query help with group by - 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)

query help with group by

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2008-07-30 : 14:05:09

Hi,

when I query with different group by clause, I get differnt total sum.

For this query with timemonth, I get -6 million result

SELECT
sum(Sales.Amount),
custname,
timeMonth
FROM
Sales,
WHERE

timeMonth In ( '2008 06','2008 05','2008 04','2008 03','2008 02','2008 01','2007 12','2007 11','2007 10','2007 09','2007 08','2007 07' )
GROUP BY
custname,
timeMonth
HAVING
sum(Sales.pfBaseAmount) <= 0

When did the same query with just custname grp by, the result is -1 million.

SELECT
sum(Sales.Amount),
custname
FROM
Sales,
WHERE

timeMonth In ( '2008 06','2008 05','2008 04','2008 03','2008 02','2008 01','2007 12','2007 11','2007 10','2007 09','2007 08','2007 07' )
GROUP BY
custname

HAVING
sum(Sales.pfBaseAmount) <= 0

What can the reason. Any tip will be of great help.

thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 14:08:16

Because the WHERE clause is run before the GROUP BY. If you want it after, then put it in the HAVING.

Or am I misunderstanding the question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thanksfor help
Posting Yak Master

106 Posts

Posted - 2008-07-30 : 14:13:18

Where part is working fine.

It something to do with the data, that I am missing to understand

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 14:16:11

You missed my point.

Show us some sample data to help illustrate your issue for us. Remember we can't read minds nor can we see your environment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 14:21:15

quote:
Originally posted by thanksfor help

Where part is working fine.

It something to do with the data, that I am missing to understand

its actually because you're grouping each time on different fields. in first case it gives unique records per custname,timeMonth combination while in second case it further compacts the result to one per custname which is why you get 1 million against 6 million

consider this simple example

custname timemonth
c1 1
c1 1
c1 2
c1 2
c1 3

now taking unique records per custname,timeMonth combination you get

custname timemonth
c1 1
c1 2
c1 3

which is first case

and taking unique records per custname you get
custname
c1

same is case happening above.Having less fields on GROUP BY means taking unique record per that column value which leads to less rows returned.

thanksfor help
Posting Yak Master

106 Posts

Posted - 2008-07-30 : 14:28:14

Sorry, thanks for quick reply.

For query with timemonth
2007 07 2007 08 2007 09........................... 2008 06
Custname
ABCD -2345 -1789 -90 -140000
VXS 0 -3456 -87 -3456
.....................................................................................
Total -2345 - 5245 177 -143456 Overalltotal is( -6 million)

When timemonth is not in the group by clause,

custname salestotal
XYZ -9000
JFK - 2390
.................

Total (- 1 million)

thanks for Ur time

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 14:29:42

Please post sample data for the same customers in both result sets.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thanksfor help
Posting Yak Master

106 Posts

Posted - 2008-07-30 : 14:32:58

Visakh16, but why is the total sum is giving different result. when group by C1, it sum up all the sales total irrespective of month which should be same as
custname timemonth
c1 1
c1 2
c1 3

Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources