tallying values in columns of each row
-
I have a table where each row has 20 columns col1, col2, col3 etc...., 16 of which contain numeric indicators ( such as 1=REd, 2=yellow, 3=green)
I need to loop through the 16 indicator columns and count how many per types ( how many 1's, how many 2's, how many 3's) for each row.
So if row 1 has 5 indicators valued "1", 6 indicators valued "yellow", and 5 indicators valued "3"
I'd like to ouput.
RedIndicatorCount GreenIndicatorCount YellowIndicatorCount
row1 5 6 5
row2 1 15 0
what and how do I code it with T-sql Sql server 2005?
thanks
-
Look up Case in books online.
Case col1 when 1 then 1 end +
Case col2 when 1 then 1 end +
Case col3 when 1 then 1 end +
Case col4 when 1 then 1 end +
Case col5 when 1 then 1 end +
Case col6 when 1 then 1 end +
Case col7 when 1 then 1 end +
Case col8 when 1 then 1 end +
Case col9 when 1 then 1 end +
Case col10 when 1 then 1 end +
Case col22 when 1 then 1 end as RedIndicatorCount,
Case col1 when 2 then 1 end +
Case col2 when 2 then 1 end +
Case col3 when 2 then 1 end +
Case col4 when 2 then 1 end +
Case col5 when 2 then 1 end +
Case col6 when 2 then 1 end +
Case col7 when 2 then 1 end +
Case col8 when 2 then 1 end +
Case col9 when 2 then 1 end +
Case col10 when 2 then 1 end +
Case col22 when 2 then 1 end as GreenIndicatorCount
From Mytable
and so on.
Can you supply sample source data?
-
This should get you going. The challenge for you now is to understand HOW this works
DECLARE @table TABLE (someID INT IDENTITY(1,1), col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6 INT)
INSERT @table
SELECT 1,1,1,1,1,1 UNION ALL
SELECT 0,1,0,0,0,0 UNION ALL
SELECT 1,2,2,2,2,2 UNION ALL
SELECT 6,6,6,4,4,4 UNION ALL
SELECT 3,3,3,3,0,1 UNION ALL
SELECT 1,1,1,1,1,1;
WITH temp AS
(SELECT
rowIdentifier, someID
FROM
(SELECT someID AS rowIdentifier, [col1], [col2], [col3], [col4], [col5], [col6]
FROM
@table) p
UNPIVOT
(someID FOR x IN
([col1], [col2], [col3], [col4], [col5], [col6])
) AS unpvt)
SELECT
a.rowIdentifier
,zeroCount = (SELECT COUNT(*) FROM temp b WHERE b.rowIdentifier = a.rowIdentifier AND someID = 0)
,oneCount = (SELECT COUNT(*) FROM temp b WHERE b.rowIdentifier = a.rowIdentifier AND someID = 1)
,twoCount = (SELECT COUNT(*) FROM temp b WHERE b.rowIdentifier = a.rowIdentifier AND someID = 2)
,threeCount = (SELECT COUNT(*) FROM temp b WHERE b.rowIdentifier = a.rowIdentifier AND someID = 3)
,fourCount = (SELECT COUNT(*) FROM temp b WHERE b.rowIdentifier = a.rowIdentifier AND someID = 4)
,fiveCount = (SELECT COUNT(*) FROM temp b WHERE b.rowIdentifier = a.rowIdentifier AND someID = 5)
,sixCount = (SELECT COUNT(*) FROM temp b WHERE b.rowIdentifier = a.rowIdentifier AND someID = 6)
FROM
temp a
GROUP BY
a.rowIdentifier
RESULTS:
rowId oneCount twoCount threeCount fourCount fiveCount sixCount
1 6 0 0 0 0 0
2 1 0 0 0 0 0
3 1 5 0 0 0 0
4 0 0 0 3 0 3
5 1 0 4 0 0 0
6 6 0 0 0 0 0
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Thanks, Jason. It works and does exactly what I want. As you said, it is a challenge for me to figure out how it works. I am taking it one step at a time so I am looking into the pivot/unpivot first.
many thanks
-
Thank you Ray for replying. I am trying to avoid to code too many column names ( a total of 16) so I am going with Jason's method.
-
It's a little confusing. I'm still not 100% on them (pivot and unpivot) but it's beginning to get clear. DONT ASK ME TO EXPLAIN.. *laughs*
But as you can see the "x" in the "someID FOR x" isn't even being used.
Anyway, good luck!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply