SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Group by problems
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 03/14/2013 :  15:31:34  Show Profile  Reply with Quote
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
8683 Posts

Posted - 03/14/2013 :  15:38:28  Show Profile  Visit webfred's Homepage  Reply with Quote
because you are not only grouping by account


Too old to Rock'n'Roll too young to die.
Go to Top of Page

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 03/14/2013 :  16:42:37  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 03/14/2013 :  17:01:39  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
279 Posts

Posted - 03/14/2013 :  19:08:42  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000