sql count case – sql count(case when multiple criteria)

sql count case – The SQL CASE statement; Adding multiple conditions to a CASE statement; best article fir SQL queries skills by practising CASE WHEN with COUNT DISTINCT in Example.

sql count case

SQL CASE can be used in conjunction with SUM to return a count of only those items matching a pre-defined condition.

SELECT 
    COUNT(CASE WHEN is_active = 0 then 1 ELSE NULL END) as "OLD",
    COUNT(CASE WHEN is_active = 1 then 1 ELSE NULL END) as "NEW_PRODUCT"
from tb_a

Example of sql count case

Use CASE to COUNT the number of rows in a column match a condition.

SELECT 
    COUNT(Id) AS ProductsCount,
    SUM ( CASE 
            WHEN AmountRate = 'Cheap' THEN 1
            ELSE 0
          END
        ) AS CheapItemsCount
FROM ProductSales 

Alternative:

SELECT 
    COUNT(Id) as ProductsCount,
    SUM (
        CASE AmountRate 
            WHEN 'Cheap' THEN 1
            ELSE 0
        END
       ) AS CheapItemsCount
FROM ProductSales 

Countif in SQL

  SQL: COUNT(CASE WHEN  THEN 1 END)
  SQL: COUNT(CASE WHEN A = 38 THEN 1 END)
  SQL: COUNT(CASE WHEN A > 38 THEN 1 END)
  SQL: COUNT(CASE WHEN A = 'Krishna' THEN 1 END)
  SQL: COUNT(CASE WHEN A LIKE 'Krishna%' THEN 1 END)

 SQL: COUNT(CASE WHEN A = 38 THEN 1 END) +
       COUNT(CASE WHEN B = 38 THEN 1 END) +
       COUNT(CASE WHEN C = 38 THEN 1 END)

  SQL: COUNT(CASE WHEN A = 38 AND B = 43 THEN 1 END)

  SQL: COUNT(CASE WHEN A IS NOT NULL AND A != '' THEN 1 END)

don’t Miss : Sql Unique Count

CASE in a clause ORDER BY

SELECT * FROM TM
ORDER BY
CASE TEAM
      WHEN 'DEVLOPER' THEN  1
      WHEN 'PLAYER' THEN 2
      WHEN 'BOLWER' THEN 3
      WHEN 'SOFTWARE' THEN 4
      ELSE        5
      END,
      CITY

I hope you get an idea about sql count case.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.