| Author |
 Topic  |
|
|
sherrireid
Yak Posting Veteran
USA
50 Posts |
 Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 03/14/2013 : 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
USA
50 Posts |
Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 03/14/2013 : 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
Yak Posting Veteran
USA
79 Posts |
Posted - 03/14/2013 : 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 |
|
|
| |
Topic |
|
Group by problems
