Query Count Value In a Row

Question

Post reply

Query Count Value In a Row

gtan018

SSChasing Mays

Points: 624
More actions
April 24, 2008 at 8:17 am

#206423

I have a query that compares two tables which returns a certain value.
I would like to count a certain values in a row. Is this possible?
Example Query:
Name / Column1 / Column2 / Column3
John / 1 / 1 / 2
Tom / 0 / 1 / 2
Mary / 1 / 1 / 1
I need to count the value of "1"
John = 2,
Tom = 1,
Mary = 3.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI,

Will you always only have three cols to check and count?

Also how do you want your results returned?

thanks

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

SELECTName,

CASE WHEN Column1 = 1 THEN 1 ELSE 0 END

+ CASE WHEN Column2 = 1 THEN 1 ELSE 0 END

+ CASE WHEN Column3 = 1 THEN 1 ELSE 0 END AS CountOfOne

FROMTable1

N 56°04'39.16"
E 12°55'05.25"

gtan018 SSChasing Mays Points: 624 More actions · Answer 3

18 columns to be exact. I need a total for each row with a vale of "1" from there I can get the value of "2" or "3" by changing the query.

I need to count the value of "1"

John = 2

Tom = 1

Mary = 3

Tim = 4

and so on.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

I think you should have told us from the beginning.

DECLARE@Sample TABLE (Name VARCHAR(4), Column1 INT, Column2 INT, Column3 INT)

INSERT@Sample

SELECT'John', 1, 1, 2 UNION ALL

SELECT'Tom', 0, 1, 2 UNION ALL

SELECT'Mary', 1, 1, 1

SELECTp.Name,

p.theValue,

COUNT(*) AS Items

FROM@Sample AS s

UNPIVOT(

theValue

FOR theColumn IN (s.[Column1], s.[Column2], s.[Column3])

) AS p

GROUP BY p.Name,

p.theValue

ORDER BYp.Name,

p.theValue

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

What you need is this

SELECTp.Name,

p.theValue,

COUNT(*) AS Items

FROMYourTableNameHere AS s

UNPIVOT(

theValue

FOR theColumn IN (s.[Column1], s.[Column2], ... , s.[Column18])

) AS p

GROUP BY p.Name,

p.theValue

ORDER BYp.Name,

p.theValue

N 56°04'39.16"
E 12°55'05.25"

gtan018 SSChasing Mays Points: 624 More actions · Answer 6

Peso perfect. It worked great. Next time I'll provide more detail. Thanks.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Thanks for the feedback.

N 56°04'39.16"
E 12°55'05.25"