Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# how to sum up data base on location

Posted on 2011-05-09
Medium Priority
197 Views
I have a table that has fields (Sku, Location_id, Quantity). I would like sum up the Quantity values for two unique locations_id and leave it alone if it equals anything else.

Table:
sku                Location_id          Qty
23452           1001                     10
23452           1002                     20
23452           1003                     70

Sum up the values in locations 1001 & 1002
current code:
select sku, case when location_id=1001 or location_id=1002 then 'East' else 'West' end as LOC
case when location_id=1001 or location_id=1002  sum(quantity) else quantity end as QTY_1
from tablex  group by sku, location_id

I'm still getting values for each location for some reason, I think the group by is getting me.
0
Question by:tips54
• 2
• 2
• 2
• +2

LVL 19

Expert Comment

ID: 35719997
Hi,

TRY THIS

``````select sku,LOC,sum(QTY_1)Quantity
FROM
(
select sku, case when location_id in(1001,1002) then 'East' else 'West' end as LOC,
case when location_id in(1001,1002) then sum(quantity) else quantity end as QTY_1
from tablex  group by sku, location_id
)A
GROUP BY SKU,LOC
``````
0

LVL 22

Expert Comment

ID: 35720196
Hi,

If I understand you correct, try this:

select sku,
case when location_id=1001 or location_id=1002 then 'East' else 'West' end as LOC,
case when location_id=1001 or location_id=1002 then (select sum(qty) from tablex t2 where t2.sku=t1.sku and t2.location_id in (1001,1002)) else sum(qty) end as QTY_1
from tablex  t1
group by sku, location_id

this should give you
sku                Location_id          Qty
23452           1001                     30
23452           1002                     30
23452           1003                     70

if there is only one row per sku,location_id this would do

select sku,
case when location_id=1001 or location_id=1002 then 'East' else 'West' end as LOC,
case when location_id=1001 or location_id=1002 then (select sum(qty) from tablex t2 where t2.sku=t1.sku and t2.location_id in (1001,1002)) else qty end as QTY_1
from tablex  t1

/peter
0

Author Comment

ID: 35720591
Brichsoft,
your suggestion is asking me to put the Qty in the group by, which puts me back to the duplicates I was getting
0

LVL 32

Expert Comment

ID: 35721189
What is it you want to see as output? Could there be another row like?
23452           1004                     50
If so, what would be the output then?
0

LVL 53

Expert Comment

ID: 35721999
(no points ..)

I think the group by is getting me.

Yep.  What are the results if you group by SKU (only)? If that's not allowed, group by "Loc"

ie
group by sku, case when location_id IN (1001,1002) then 'East' else 'West' end
0

LVL 53

Expert Comment

ID: 35722026
awking00
>> What is it you want to see as output? Could there be another row like? <<

Hm... the group by is probably interfering, but a better question is what @awking00 asked.
0

LVL 19

Accepted Solution

Bhavesh Shah earned 900 total points
ID: 35726421
Hi,

i changed query bit.

- Bhavesh
``````select sku,LOC,sum(QTY_1)Quantity
FROM
(
select sku, case when location_id in(1001,1002) then 'East' else 'West' end as LOC, quantity as QTY_1
from tablex  group by sku, location_id
)A
GROUP BY SKU,LOC
``````
0

Author Closing Comment

ID: 35820411
thank you
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month13 days, 15 hours left to enroll