CODEWITHSUNDEEP
sql-server-2005t-sql
user296146
user296146

Reputation: 21

T SQL Count question

I'm making a report at work which looks at 5 columns which all contain v1aa, v1ab and v1ac.

What I want to do is count up all the v1aa in the 5 columns and show the results (and the same for v1ab and v1ac)

An example of how I like it to be displayed as is as follows :-

Amber = 3 (v1aa code)
Blue =  2 (v1ab code)
Red  = 1 (v1ac code)

Could anyone give me any tips how to get me started?

Upvotes: 2

Views: 193

Answers (4)

Charles Bretana
Charles Bretana

Reputation: 146469

If you want these counts for each record, then try:

Select
    Case When Col1 Like '%v1aa%' Then 1 Else 0 End +
    Case When Col2 Like '%v1aa%' Then 1 Else 0 End +
    Case When Col3 Like '%v1aa%' Then 1 Else 0 End +
    Case When Col4 Like '%v1aa%' Then 1 Else 0 End +
    Case When Col5 Like '%v1aa%' Then 1 Else 0 End As AmberCount,
    Case When Col1 Like '%v1ab%' Then 1 Else 0 End +
    Case When Col2 Like '%v1ab%' Then 1 Else 0 End +
    Case When Col3 Like '%v1ab%' Then 1 Else 0 End +
    Case When Col4 Like '%v1ab%' Then 1 Else 0 End +
    Case When Col5 Like '%v1ab%' Then 1 Else 0 End As BlueCount,
    Case When Col1 Like '%v1ac%' Then 1 Else 0 End +
    Case When Col2 Like '%v1ac%' Then 1 Else 0 End +
    Case When Col3 Like '%v1ac%' Then 1 Else 0 End +
    Case When Col4 Like '%v1ac%' Then 1 Else 0 End +
    Case When Col5 Like '%v1ac%' Then 1 Else 0 End As RedCount
From MyTable

If you want the counts for the entire table then add a Sum() aggregation function, as Timothy Khouri suggested in his answer

Upvotes: 0

Jason Kresowaty
Jason Kresowaty

Reputation: 16500

I think this will get you started. You might need to add a WHERE clause if NULLs are a possibility or additional codes are possible in the data that you do not want to show in your report.

SELECT CASE i.code
    WHEN 'v1aa' THEN 'Amber'
    WHEN 'v1ab' THEN 'Blue'
    WHEN 'v1ac' THEN 'Red' END,
    COUNT(*)
FROM
(
    SELECT Col1 AS Code FROM Test
    UNION ALL SELECT Col2 FROM Test
    UNION ALL SELECT Col3 FROM Test
    UNION ALL SELECT Col4 FROM Test
    UNION ALL SELECT Col5 FROM Test
) i
GROUP BY i.Code

Upvotes: 0

Timothy Khouri
Timothy Khouri

Reputation: 31845

Is this what you're looking for?

SELECT
    SUM((CASE WHEN Column1 = 'v1aa' THEN 1 ELSE 0 END)
        + (CASE WHEN Column2 = 'v1aa' THEN 1 ELSE 0 END)
        + (CASE WHEN Column3 = 'v1aa' THEN 1 ELSE 0 END)
        + (CASE WHEN Column4 = 'v1aa' THEN 1 ELSE 0 END)
        + (CASE WHEN Column5 = 'v1aa' THEN 1 ELSE 0 END))
    AS Amber,
    SUM((CASE WHEN Column1 = 'v1ab' THEN 1 ELSE 0 END)
        + (CASE WHEN Column2 = 'v1ab' THEN 1 ELSE 0 END)
        + (CASE WHEN Column3 = 'v1ab' THEN 1 ELSE 0 END)
        + (CASE WHEN Column4 = 'v1ab' THEN 1 ELSE 0 END)
        + (CASE WHEN Column5 = 'v1ab' THEN 1 ELSE 0 END))
    AS Blue,
    SUM((CASE WHEN Column1 = 'v1ac' THEN 1 ELSE 0 END)
        + (CASE WHEN Column2 = 'v1ac' THEN 1 ELSE 0 END)
        + (CASE WHEN Column3 = 'v1ac' THEN 1 ELSE 0 END)
        + (CASE WHEN Column4 = 'v1ac' THEN 1 ELSE 0 END)
        + (CASE WHEN Column5 = 'v1ac' THEN 1 ELSE 0 END))
    AS Red
FROM
    dbo.MyTable

Upvotes: 0

amelvin
amelvin

Reputation: 9051

I'd need to see the data to be sure, but is sounds like a cross tab query.

Upvotes: 1

Related Questions