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
8
Medium Priority
?
197 Views
Last Modified: 2012-05-11
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
Comment
Question by:tips54
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
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

Open in new window

0
 
LVL 22

Expert Comment

by:pivar
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

by:tips54
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 32

Expert Comment

by:awking00
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

by:_agx_
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

by:_agx_
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

by:
Bhavesh Shah earned 900 total points
ID: 35726421
Hi,

i changed query bit.
Please look at it.


- 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

Open in new window

0
 

Author Closing Comment

by:tips54
ID: 35820411
thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question