Sum per Item in different condition query

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

SQL Server 2000 Forums

Transact-SQL (2000)

Sum per Item in different condition query

Author

Topic

MIB426
Starting Member

3 Posts

Posted - 2006-05-19 : 11:12:12

TableA
ID Status Number
3 A 1
1 A 1
2 B 2
3 B 1
2 C 1
1 C 3
1 A 1
2 C 2
3 B 3
2 C 1
1 C 3
1 A 1
2 C 2
3 B 3

TableB
ID Name
1 Dog
2 Cat
3 Pig

Output Query
ID Name Status=A Status=B Status=C
1 Dog 3 2 6
2 Cat 0 2 4
3 Pig 1 7 0

I want to have query which can give me result as Output Query

Condition
Status= A „³ Sum(Number) when Status = A
Status= B „³ Sum(Number) when Status = B
Status= C „³ Sum(Number) when Status = C

Please let me know in both Query and SP.
thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-19 : 11:19:56

select t1.ID, t2.name, sum(case when t1.status = 'A' then 1 else 0 end, sum(.....
from table1 t1
join table2 t2
on t1.ID = t2.ID
group by t1.id, t2.name

will be the same statement in a query or sp.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-19 : 11:22:50

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

MIB426
Starting Member

3 Posts

Posted - 2006-05-19 : 11:32:24

thank you all
Problem is solved.

again, thank you very much

MIB426

MIB426
Starting Member

3 Posts

Posted - 2006-05-19 : 13:35:07

Now i am getting another issue.
as result
ID Name Status=A Status=B Status=C
1 Dog 3 2 6
2 Cat 0 2 4
3 Pig 1 7 0

now i need extra field which is Status=B minus Status=C
ID Name Status=A Status=B Status=C Difference(B-C)
1 Dog 3 2 6 -4
2 Cat 0 2 4 -2
3 Pig 1 7 0 7

condition
Difference(B-C) --> Status=B - Status=C

please let me know
thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-19 : 13:48:33

Something like this?

--data
declare @TableA table (ID int, Status char(1), Number int)
insert @TableA
          select 3, 'A', 1
union all select 1, 'A', 1
union all select 2, 'B', 2
union all select 3, 'B', 1
union all select 2, 'C', 1
union all select 1, 'C', 3
union all select 1, 'A', 1
union all select 2, 'C', 2
union all select 3, 'B', 3
union all select 2, 'C', 1
union all select 1, 'C', 3
union all select 1, 'A', 1
union all select 2, 'C', 2
union all select 3, 'B', 3

declare @TableB table (ID int, Name varchar(10))
insert @TableB
          select 1, 'Dog'
union all select 2, 'Cat'
union all select 3, 'Pig'

--calculation
select a.ID, b.name,
    sum(case a.status when 'A' then 1 else 0 end) as 'Status=A',
    sum(case a.status when 'B' then 1 else 0 end) as 'Status=B',
    sum(case a.status when 'C' then 1 else 0 end) as 'Status=C',
    sum(case a.status when 'B' then 1 when 'C' then -1 else 0 end) as 'Difference(B-C)'
from @tableA a inner join @tableB b on a.ID = b.ID
group by a.id, b.name
order by a.id

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-19 : 15:18:15

or if you don't want to repeat the code
select ID, name, [Status=A],[Status=B],[Status=C],[Difference(B-C)] = [Status=B]-[Status=C]
from
(
select a.ID, b.name,
sum(case a.status when 'A' then 1 else 0 end) as [Status=A],
sum(case a.status when 'B' then 1 else 0 end) as [Status=B],
sum(case a.status when 'C' then 1 else 0 end) as [Status=C]
from @tableA a inner join @tableB b on a.ID = b.ID
group by a.id, b.name
) a
order by id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources