February 9, 2010 at 4:31 am
Could someone please help me with this?
I have data like this:
create table A
(
s char(1),
sku char(1),
q int
)
create table B
(
sku char(1),
kbn int
)
insert into A
select '1','a',10 union all
select '1','b',20 union all
select '1','c',30 union all
select '1','e',40 union all
select '2','f',50 union all
select '2','g',50 union all
select '2','h',10 union all
select '3','a',15 union all
select '4','k',20
insert into B
select 'a',4 union all
select 'b',2 union all
select 'c',4 union all
select 'd',2 union all
select 'e',2 union all
select 'f',3 union all
select 'g',4 union all
select 'h',2 union all
select 'k',2
I need to create a result like this:
s qty
1 40
2 50
3 15
1 60
2 60
4 20
I have to create a temp table from A and B.
The process is as description below:
s qty
1 sum(A.q) where B.kbn=4
1 sum(A.q) where B.kbn<>4
2 sum(A.q) where B.kbn=4
( does not include sum(A.q) where B.kbn<>4 in case it is zero )
( does not include sum(A.q) where B.kbn=4 in case it is zero )
3 sum(A.q) where B.kbn<>4
Current i'm using this solution:
select
A.s,
SUM(A.q) as qty
from A inner join B on A.sku=B.sku and B.kbn=4
group by A.s having SUM(A.q)>0
union all
select
A.s,
SUM(A.q) as qty
from A inner join B on A.sku=B.sku and B.kbn<>4
group by A.s having SUM(A.q)>0
But I think there will be better solutions.
Could someone please suggest another?
Thanks in advance.
February 9, 2010 at 5:09 am
Here's another way to write the query, but compared to what you have it is only marginally better on the sample set you provided (49% to 51% 🙂
SELECT [t1].
,SUM([t1].
) AS
FROM [dbo].[A] AS [t1]
JOIN [dbo]. AS [t2]
ON [t1].[sku] = [t2].[sku]
GROUP BY [t1].
,CASE WHEN [t2].[kbn] = 4 THEN 0
ELSE 1
END
HAVING SUM([t1].
) > 0
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply