Counting specific values across columns within SQL Server 2008 view

Question

I have the following data:

   ID Column1 Column2 Column3 Column4 Column5
   001   A        C       D        A      B
   002   A        D       A        B      A
   003   B        K       Q        C      Q
   004   A        K       E        E      B

I want to create a new column in a view which gives me the count of "A"s across the 5 source columns for each row. The result should look like this:

    ID Column1 Column2 Column3 Column4 Column5  SumOfA
    001   A        C       D        A      B       2
    002   A        D       A        B      A       3
    003   B        K       Q        C      Q       0
    004   A        K       E        E      B       1

I've seen several examples here but they return instances of "A" across records - I want the count of "A"s across the columns, not aggregating across rows. Any thoughts?

Taryn · Accepted Answer · 2013-03-20 19:08:46Z

5

You can use multiple CASE expressions to count the A values:

select id,
  column1, 
  column2,
  column3,
  column4, 
  column5,
  case when column1 = 'A' then 1 else 0 end +
  case when column2 = 'A' then 1 else 0 end +
  case when column3 = 'A' then 1 else 0 end +
  case when column4 = 'A' then 1 else 0 end +
  case when column5 = 'A' then 1 else 0 end TotalA
from yourtable

See SQL Fiddle with Demo

answered Mar 20, 2013 at 19:08

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DevelopmentIsMyPassion Over a year ago

@bluefeet strikes :) What can i say.

ErikE · Accepted Answer · 2013-03-21 18:59:53Z

4

For a situation like this, I prefer using CROSS APPLY to do an intermediate logical UNPIVOT.

SELECT
   *
FROM
   dbo.YourTable T
   CROSS APPLY (
      SELECT Count(*)
      FROM (VALUES (Column1), (Column2), (Column3), (Column4), (Column5)) C (Val)
      WHERE Val = 'A'
   ) A (Cnt)

This also has the advantage of being able to change which value you're counting in just one place instead of in 5 places, and it is extremely easy to add more columns if necessary.

See this working in a Sql Fiddle

However, that you are struggling to build this query shows that the table design is probably not best. If the column names themselves contain data (such as time periods, regions, or other kinds of similar values) then it is almost certain that the design is suboptimal. I strongly recommend that you store the data unpivoted instead--use one row and column per value, with a new column denoting which original column it came from.

If you can't change the schema for whatever reason, you might consider creating a view:

CREATE VIEW dbo.YourTableUnpivoted
SELECT
   T.ID,
   C.*
FROM
   dbo.YourTable T
   CROSS APPLY (VALUES
      ('Column1', Column1),
      ('Column2', Column2),
      ('Column3', Column3),
      ('Column4', Column4),
      ('Column5', Column5)
   ) C (Col, Val);

(You can also use the PIVOT operator, too.) Then you can use this as if it were the redesigned table:

SELECT
   ID,
   Count(*)
FROM
   dbo.YourTableUnpivoted
WHERE
   Val = 'A'
GROUP BY
   ID;

edited Mar 21, 2013 at 18:59

answered Mar 21, 2013 at 8:19

ErikE

50.7k23 gold badges157 silver badges201 bronze badges

3 Comments

Clint Finch Over a year ago

Thanks! I agree that the table design is suboptimal and what you described is the way it should be. Problem there is that the source system (REDCap) only one-toone table structures so these repeating values are stored in separate columns, so I'm working with it as-is and not transforming it at present.

Clint Finch Over a year ago

I tried this out btw, works very well, thanks. I'd have used this as a solution but the other one was posted first. I'll save it for another time, I'll be running across this for later.

ErikE Over a year ago

Please see my update for one more idea. Thanks for checking out the code.

shahkalpesh · Accepted Answer · 2013-03-20 19:07:26Z

1

SELECT 
(CASE WHEN Column1 = 'A' THEN 1 ELSE 0 END 
+ CASE WHEN Column2 = 'A' THEN 1 ELSE 0 END 
+ CASE WHEN Column3 = 'A' THEN 1 ELSE 0 END 
+ CASE WHEN Column4 = 'A' THEN 1 ELSE 0 END 
+ CASE WHEN Column5 = 'A' THEN 1 ELSE 0 END) AS SumOfA
FROM myTable

answered Mar 20, 2013 at 19:07

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Comments

Praveen Nambiar · Accepted Answer · 2013-03-20 19:12:21Z

0

You can use CASE statement to achieve this:

SELECT 
    column1, column2, column3, column4, column5,
    (CASE WHEN Column1 = 'A' THEN 1 ELSE 0 END   
     + CASE WHEN Column2 = 'A' THEN 1 ELSE 0 END   
     + CASE WHEN Column3 = 'A' THEN 1 ELSE 0 END   
     + CASE WHEN Column4 = 'A' THEN 1 ELSE 0 END   
     + CASE WHEN Column5 = 'A' THEN 1 ELSE 0 END) AS TotalSum  
FROM yourTable

answered Mar 20, 2013 at 19:12

Praveen Nambiar

4,9022 gold badges24 silver badges32 bronze badges

Collectives™ on Stack Overflow

Counting specific values across columns within SQL Server 2008 view

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related