Select Query - 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

General SQL Server Forums

New to SQL Server Programming

Select Query

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-10-16 : 11:02:56

I have a table iminvloc that will have multiple records for one item_no.


item_no  loc   QtySold  ReOrder
ABC       1     100       40
ABC       2     200       55
ABC       3     400       2

What I want to do is sum the qtysold for all locations but only pull the ReOrder from location 1. 

select item_no, sum(qtySold) as Total sold, (Reorder from location 1??
from iminvloc_sql

I would want my results to be:

ABC    700     40

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-16 : 11:19:33

SELECT item_no, sum(QTY_sold) as QtySold,sum(case when loc = 1 then reorder else 0 end) as reOrder
FROM iminvloc
GROUP BY item_no

Jim

Everyday I learn something that somebody else already knew

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-10-16 : 11:43:03

What if I had a fourth location but I only wanted the Location 1, 2 and 3?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-16 : 12:11:33

Then your case statement wuold be something like
SUM(CASE WHEN loc in(1,2,3) THEN ReOrder ELSE 0 END) or
SUM(CASE WHEN loc <> 4 THEN ReOrder ELSE 0 END)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources