Help with counts - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Help with counts

Author

Topic

MitchRicio
Starting Member

1 Post

Posted - 2013-08-09 : 12:59:47

Hi Guys,

Am somehow stumped on this one: How would I count these things per row? Like how many increase ver vendornbr column and such? Here is the table:

VENDORNBR C1 C2 C3
001U DECREASE DECREASE DECREASE
0157 DECREASE DECREASE DECREASE
0160 INCREASE INCREASE DECREASE

Mitch

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-09 : 13:38:26

What do you want for output? I can't tell if you want to unpivot the data or jsut do column-wise sums.

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 09:20:36

select Vendornbr,sum(decrease) as Decrease_cnt,sum(Increase) as Increase_cnt
from (SELECT r2.Vendornbr, [Decrease] AS [Decrease], [Increase] as [Increase]
FROM
(SELECT Vendornbr, c1 FROM #temp) r1
PIVOT (Count(c1) FOR c1 IN ([Decrease], [Increase])) AS r2

union all

SELECT r2.Vendornbr, [Decrease] AS [Decrease], [Increase] as [Increase]
FROM
(SELECT Vendornbr, c2 FROM #temp) r1
PIVOT (Count(c2) FOR c2 IN ([Decrease], [Increase])) AS r2

union all

SELECT r2.Vendornbr, [Decrease] AS [Decrease], [Increase] as [Increase]
FROM
(SELECT Vendornbr, c3 FROM #temp) r1
PIVOT (Count(c3) FOR c3 IN ([Decrease], [Increase])) AS r2

)as s

group by Vendornbr order by Vendornbr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources