COUNTIF 1

[WurzelGummidge]

Is there a way of doing a 'countif' (Excel) in SQL ?
IE: Excel Formula is-
=12/COUNTIF(O5:Z5,&quot;<>0&quot

 

[sunila7]

Hi,

Try this SQL

SELECT 12/SUM(CASE When FLD<> 0 Then 1 Else 0 End) FROM TBL

Hope it helps

Sunil

 

[WurzelGummidge]

The problem is, how do I do this for multiple colums.
I have 13 columns - M1 -> M13 (these represent 13 months).
How would I count an occurence of non null data across these colums (for each row).
ie:
M1 NULL
M2 NULL
M3 12
M4 NULL
M5 6
M7 NULL

ETC...
I would like the value 2 returned (2 occurences of non-null data)
Hope you can understand what i am attempting

 

[JamesLean]

SELECT
  CASE WHEN m1 IS NULL THEN 0 ELSE 1 END +
  CASE WHEN m2 IS NULL THEN 0 ELSE 1 END +
  CASE WHEN m3 IS NULL THEN 0 ELSE 1 END +
  ...
  CASE WHEN m13 IS NULL THEN 0 ELSE 1 END
FROM tbl

--James

 

[WurzelGummidge]

You got it man!!!
Excellent - Thanks James

 

[WurzelGummidge]

In a similar vein, i would also like to find the LAST month ordered:
Example Data:
M1 NULL
M2 NULL
M3 NULL
M4 NULL
M5 6
M7 3
M8 NULL
M9 10
M10 NULL
M11 NULL
M12 NULL

These are column headings so in the above example, i would like to have '4' returned - ie LAST ORDERED 4 MONTHS AGO (M9 has last order qty in it)