need total sold for indvidual items

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

General SQL Server Forums

New to SQL Server Programming

need total sold for indvidual items

Author

Topic

Dmh188
Starting Member

37 Posts

Posted - 2011-01-18 : 14:00:04

everyone has been so helpful on this site and i hope you guys can help me again.. I am trying to find the totals for items shipped. so i need item A to be grand total = X and then items B grand total = X. and so on and so on, just to add up all the sales for that items and display it. i am currently using this statement:

select distinct invhist.itemno, invhist.qty, items.category
from invhist
inner join items
on invhist.itemno=items.itemno
where category in ('AIRGUNS', 'AIRPISTOLS', 'AIRSOFT', 'Airsoftpistols', 'AirsoftRifles',
'AIRRIFLES')
and invhist.doc_type = 9
and invhist.date_fld between '2010' and '2011'
order by invhist.itemno

but it is producing results like:
item qty category
909s -3 airgun
909s -1 airgun
909s -2 airgun
bkl -3 airgun
bkl -1 airgun

now the negative one part is correct because that is it getting subtracted from inventory. But what i need it to do is group the item number, this way it will just show the item number and then grand total of shipped. so like
item qty category
909s -6 airgun
bkl -4 airgun

any help would be great guys, and thank you in advance!

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-01-18 : 16:21:38

Try something like this:

select distinct invhist.itemno, sum(invhist.qty), items.category
from invhist
inner join items
on invhist.itemno=items.itemno
where category in ('AIRGUNS', 'AIRPISTOLS', 'AIRSOFT', 'Airsoftpistols', 'AirsoftRifles',
'AIRRIFLES')
and invhist.doc_type = 9
and invhist.date_fld between '2010' and '2011'
group by invhist.itemno, items.category
order by invhist.itemno

Some days you're the dog, and some days you're the fire hydrant.

Dmh188
Starting Member

37 Posts

Posted - 2011-01-18 : 16:47:30

worked like a charm, thank you for that. i tried the sum(invhist.qty) in the select, but completely missed the order by following the group by. thank you

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-01-19 : 12:57:04

You're welcome :)

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources