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'

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'

CodePudding user response：

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

CodePudding user response：

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;

• Example db<>fiddle

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;

CodePudding user response：

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