CODEWITHSUNDEEP
sqlsql-server
Mohan Wijesena
Mohan Wijesena

Reputation: 235

Counting columns that have data

I have a query running to fetch month on month data from SQL Server. I need to add an additional column "Count" which captures the count of all the columns that has a value greater than '0'

Jan Feb Mar Count
13 0 25 2
11 10 4 3
0 0 7 1

Here's the SQL query I tried, however soon realized this case expression may not be the optimal way to capture all possible combination.

Can someone suggest a better solution?

SELECT 
, P.January as 'Jan'
, P.February as 'Feb'
, P.March as 'Mar',
CASE 
  WHEN P.January > 0 AND P.February > 0 AND P.March > 0 THEN '3'
  WHEN P.January < 0 AND P.February > 0 AND P.March > 0 THEN '2'
  ....
ELSE ''
END as 'Count'

Upvotes: 0

Views: 98

Answers (4)

Jonas Metzler
Jonas Metzler

Reputation: 5975

I think this is a good use case for IIF, see the documentation.

The query will be this one:

SELECT 
Jan, Feb, Mar,
IIF(Jan > 0, 1, 0) + IIF(Feb > 0, 1, 0) + IIF(Mar > 0, 1, 0) AS [Count]
FROM p;

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22275

Here is a flexible solution that is working regardless of the number of columns.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (Jan int, Feb int, Mar INT, Apr INT, May INT);
INSERT @tbl (Jan,Feb,Mar, Apr, May) VALUES
(13, 0,  25, 0, 2),
(11, 10, 4,  1, 18),
(0,  0,  7,  0, 7);
-- DDL and sample data population, end

SELECT t.*
   , [Count] = x.value('count(/root/*[not(text()="0")]/text())', 'INT')
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x);

Output

Jan Feb Mar Apr May Count
13 0 25 0 2 3
11 10 4 1 18 5
0 0 7 0 7 2

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

If no negative values, we can take advantage of the fact that CONVERT(bit, 13) yields 1, and take SUM from a CROSS APPLY:

SELECT s.Jan, s.Feb, s.Mar, 
  [Count] = SUM(CONVERT(int, CONVERT(bit, x.v)))
FROM dbo.YourTableName AS s
CROSS APPLY (VALUES(s.Jan),(s.Feb),(s.Mar)) AS x(v)
GROUP BY s.Jan, s.Feb, s.Mar;

If you have negative values that should not count, you can just add col + ABS(col) to change negative values to 0, double positive values, and leave 0 untouched.

SELECT s.Jan, s.Feb, s.Mar, 
  [Count] = SUM(CONVERT(int, CONVERT(bit, x.v + ABS(x.v))))
  -------------------------------------------^^^^^^^^^^^
FROM dbo.YourTableName AS s
CROSS APPLY (VALUES(s.Jan),(s.Feb),(s.Mar)) AS x(v)
GROUP BY s.Jan, s.Feb, s.Mar;

Upvotes: 1

sa-es-ir
sa-es-ir

Reputation: 5052

As @JohnCappelletti said in the comments you can use beautiful Sign function like this (assuming the values are >=0):

SELECT 
, P.January as 'Jan'
, P.February as 'Feb'
, P.March as 'Mar'
, Sign(P.January) + Sign(P.February) + Sign(P.March) /*other months*/ as Count
FROM P

Upvotes: 1

Related Questions