Help with Grouping - 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)

Help with Grouping

Author

Topic

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-02-04 : 08:07:17

How in the following can I group by slsman, so it only lists 1 slsman, there is many of the same slsman in the query, but I only want to display the total by each slsman. Does that make sens?

select co.slsman,
sum (coitem.price*coitem.qty_invoiced) - ((coitem.price*coitem.qty_invoiced) * coitem.disc / 100) as Total
from co
inner join coitem on coitem.co_num = co.co_num
inner join inv_hdr on inv_hdr.co_num = co.co_num
where inv_hdr.inv_date between '12/01/09' and '12/31/09'
GROUP BY co.slsman,coitem.price,coitem.qty_invoiced,coitem.disc

Thanks
Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 08:11:12

quote:
Originally posted by baze7

How in the following can I group by slsman, so it only lists 1 slsman, there is many of the same slsman in the query, but I only want to display the total by each slsman. Does that make sens?

select co.slsman,
sum (coitem.price*coitem.qty_invoiced) - ((coitem.price*coitem.qty_invoiced) * coitem.disc / 100) as Total
from co
inner join coitem on coitem.co_num = co.co_num
inner join inv_hdr on inv_hdr.co_num = co.co_num
where inv_hdr.inv_date between '12/01/09' and '12/31/09'
GROUP BY co.slsman~~,coitem.price,coitem.qty_invoiced,coitem.disc~~

Thanks
Chad

then group by slsman alone

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-02-04 : 08:27:15

Please excuse my stupidity, I am learning this, coming from another dataabse language, I tried that and I get this:
Msg 8120, Level 16, State 1, Line 2
Column 'coitem.price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'coitem.qty_invoiced' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'coitem.disc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 08:29:36

aha...just noticed you've a part outside sum() . what does below mean. what are you calculating by it?

sum (coitem.price*coitem.qty_invoiced) - ((coitem.price*coitem.qty_invoiced) * coitem.disc / 100)

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-02-04 : 08:35:07

Well, you answered my question, I just put the entire sum in sum ((coitem.price*coitem.qty_invoiced) - ((coitem.price*coitem.qty_invoiced) * coitem.disc / 100)). Thanks for the help!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 08:37:40

great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources