CODEWITHSUNDEEP
sql-servert-sqlviewcountsql-server-2008-r2
Clint Finch
Clint Finch

Reputation: 101

Counting specific values across columns within SQL Server 2008 view

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?

Upvotes: 1

Views: 6831

Answers (4)

ErikE
ErikE

Reputation: 50251

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;

Upvotes: 4

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

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

Upvotes: 0

Taryn
Taryn

Reputation: 247800

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

Upvotes: 5

shahkalpesh
shahkalpesh

Reputation: 33474

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

Upvotes: 1

Related Questions