a SUM problem in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

a SUM problem in sql

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-10-31 : 06:30:13

Hello All

I have a query with the following syntax :

SELECT dbo.SITES.SITENAME, dbo.MEMBERS.UDF2 AS AirMiles, dbo.MEMBERS.UDF3 AS Charity,dbo.MEMBERS.UDF5 AS Christmas, SUM(dbo.SALEPOINTS.POINTS) AS SumPts
FROM dbo.SALEPOINTS INNER JOIN
dbo.TRANS ON dbo.SALEPOINTS.TRANKEY = dbo.TRANS.TRANKEY INNER JOIN
dbo.SITES ON dbo.TRANS.SITEKEY = dbo.SITES.SITEKEY INNER JOIN
dbo.MEMBERS ON dbo.SALEPOINTS.MEMBERKEY = dbo.MEMBERS.MEMBERKEY
WHERE (DATEADD(day, dbo.TRANS.TRANDATE - '38024', CONVERT(DATETIME, '2004-02-05 00:00:00', 102)) BETWEEN CONVERT(DATETIME,
'2006-07-30 00:00:00', 102) AND CONVERT(DATETIME, '2006-08-26 00:00:00', 102)) AND (NOT (dbo.MEMBERS.SURNAME LIKE '% test %')) AND
(NOT (dbo.MEMBERS.SURNAME = 'New Member'))
GROUP BY dbo.SITES.SITENAME, dbo.MEMBERS.UDF2, dbo.MEMBERS.UDF3, dbo.MEMBERS.UDF5

with the following subset of results returned for one of the 40 sitenames :

Total
OHouses No No 585745
OHouses No No No 8775
OHouses No No Yes 2005
OHouses no yes 40
OHouses Yes No 78850
OHouses Yes No No 1480

What I would like to see is the first three lines returned being added together and the total accumulatted and the last two lines returned being added and the total accumulatted.

Can somone please give me a suggestion to change the above script to cater for my requirements please ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-31 : 06:42:44

[code]
select SITENAME, AirMiles, Charity, sum(SumPts)
from
(
< your existing query here>
) r
group by SITENAME, AirMiles, Charity
[/code]

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-10-31 : 06:45:59

cheers KH will give that a try,

I love this forum !

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-10-31 : 07:44:47

That works brilliantly KH thank-you,

but how does SQL know to add the christmas points into the no airmiles and no charity sum points and not the charity or airmiles sum points?

It works gr8 but I am confused?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-31 : 07:54:14

it is the same GROUP BY principal as your query

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-10-31 : 08:01:27

i understand now after taking a second glance, it is because you are only grouping by 3 columns and not four as in the subquery,

Thanks again.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-10-31 : 09:17:22

Sorry to bug you again KH but I am getting null entries that I have tried to limit in the where clause with no success, how could I change the above query to leave out the top line of the following resultset ?

Chif_ _10
Chif No No 50910
Chif No Yes 20
Chif yes no 10395

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-10-31 : 09:38:35

Hopefully this is the solutioon I am looking for.

select SITENAME, AirMiles, Charity, sum(SumPts)
from
(
< your existing query here>

) r
where Airmiles in(upper('YES'),upper('NO')) and Charity in (upper('YES'),upper('NO'))
group by SITENAME, AirMiles, Charity

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-31 : 09:50:36

>>where Airmiles in(upper('YES'),upper('NO')) and Charity in (upper('YES'),upper('NO'))

Your where should be

where Upper(Airmiles) in('YES','NO') and upper(Charity) in ('YES','NO')

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 09:58:18

upper is not needed for this. assuming your collation is case insensitive (it is usualy)

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-31 : 10:07:42

quote:
Originally posted by spirit1

upper is not needed for this. assuming your collation is case insensitive (it is usualy)

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

Yes. I just showed him that he was using it wrongly