T SQL Count question

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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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