SQL Server Equivalent of a Countif Aggregate Function

Sql Server equivalent of a COUNTIF aggregate function

You could use a SUM (not COUNT!) combined with a CASE statement, like this:

SELECT SUM(CASE WHEN myColumn=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView

Note: in my own test NULLs were not an issue, though this can be environment dependent. You could handle nulls such as:

SELECT SUM(CASE WHEN ISNULL(myColumn,0)=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView

SQL equivalent of countif() across 2 columns

try this :

SELECT id
      ,current
      ,nxt
      ,nvl2(nxt,decode(next,lead(current) over (partition by id order by id),1,0),0) occured
  FROM yourtable

How to count two different criteria from the same column in a table?

In MS Access, conditional aggregation uses IIF() instead of CASE:

SELECT TheDate As Day,
       SUM(IIF(Running = "Runtime", 1, 0)) AS RunNum,
       COUNT(*) AS TotalNum
FROM Source_Table
WHERE ValidPoint = "yes"
GROUP BY TheDate;

Is there a way to check that none of the rows in a grouping contain a value?

I think you are looking for something like this:

select distinct
   o.OrderId
from
   OrderDetails as o
where not exists
(
   select 1
   from
      OrderDetails as o2
   where
      o2.OrderId = o.OrderId and
      o2.Class = '0'
)

---

Related Topics