Group by problems - 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 2008 Forums

Transact-SQL (2008)

Group by problems

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2013-03-14 : 15:31:34

I am writing a report that is supposed to sum up the dollars associated to GL Account numbers. I am doing a group by, but it is not grouping the GL Account numbers. There are the same GL account numbers listed multiple times. Here is my code:

select
JHHD.JNL_CODE,
JHDT.JNL_ACCT,
sum(JHDT.JNL_AMT)

from JHHD
join JHDT
on JHDT.BATCH_NO = JHHD.BATCH_NO

where
JHHD.JNL_CODE in ('PORCLS','PORVRS') and
JHHD.FLX_UPDATE_DATE >= '02/01/2013' and
JHHD.FLX_UPDATE_DATE <= '02/28/2013' and
JHDT.JNL_AMT > 0.00

group by
JHDT.JNL_ACCT,
JHHD.JNL_CODE,
JHDT.JNL_AMT

Here is the results I get:

JNL_CODE JNL_ACCT JNL_AMT
-------------------------------
PORVRS 101000134000 2266.00
PORCLS 101000150000 12306.25
PORCLS 101000150000 12440.00
PORCLS 101000150000 12823.55
PORCLS 101000150000 27541.44
PORCLS 101000150000 274826.00
PORCLS 101000151000 672157.00
PORVRS 102000200000 226474.16
PORVRS 106014740480 2921.46
PORVRS 106015740480 11823.37
PORVRS 106071740480 12808.65
PORVRS 106071740480 23012.52
PORVRS 106072740480 27326.82

Why does it not group by JNL_ACCT when I am doing a group by?

SLReid
Forum Newbie
Renton, WA USA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-14 : 15:38:28

because you are not only grouping by account

Too old to Rock'n'Roll too young to die.

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2013-03-14 : 16:42:37

But when you do a group by SQL requires you to put in the group-by all of the fields that are in the select. If I want to display those fields, what am I supposed to do?

SLReid
Forum Newbie
Renton, WA USA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-14 : 17:01:39

First: there is no need to put the amt in the group by because amt is used with the sum() function.
So take the amt out of the group by and you will get less rows.

Second: If there are more than one code for an account then you will get a row for each account/code combination and this is a correct behavior.
If you want only one row for each account then it makes no sense to have the code in the group by and in the select list.

Too old to Rock'n'Roll too young to die.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-03-14 : 19:08:42

select
JHHD.JNL_CODE,
JHDT.JNL_ACCT,
sum(JHDT.JNL_AMT)

from JHHD
join JHDT
on JHDT.BATCH_NO = JHHD.BATCH_NO

where
JHHD.JNL_CODE in ('PORCLS','PORVRS') and
JHHD.FLX_UPDATE_DATE >= '02/01/2013' and
JHHD.FLX_UPDATE_DATE < '03/01/2013' and
JHDT.JNL_AMT > 0.00

group by
JHDT.JNL_ACCT,
JHHD.JNL_CODE
--removed amt from the group by

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources