Is it possible to specify condition in Count()?

agnieszka_40872 0
Viewed 697015

Is it possible to specify a condition in Count()? I would like to count only the rows that have, for example, "Manager" in the Position column.

I want to do it in the count statement, not using WHERE; I'm asking about it because I need to count both Managers and Other in the same SELECT (something like Count(Position = Manager), Count(Position = Other)) so WHERE is no use for me in this example.

tsql sql-server sql
12 Answers

If using Postgres or SQLite, you can use the Filter clause to improve readability:

SELECT
  COUNT(1) FILTER (WHERE POSITION = 'Manager') AS ManagerCount,
  COUNT(1) FILTER (WHERE POSITION = 'Other') AS OtherCount
FROM ...

BigQuery also has Countif - see the support across different SQL dialects for these features here: https://modern-sql.com/feature/filter

user_13581870

Here is what I did to get a data set that included both the total and the number that met the criteria, within each shipping container. That let me answer the question "How many shipping containers have more than X% items over size 51"

select
   Schedule,
   PackageNum,
   COUNT (UniqueID) as Total,
   SUM (
   case
      when
         Size > 51 
      then
         1 
      else
         0 
   end
) as NumOverSize 
from
   Inventory 
where
   customer like '%PEPSI%' 
group by
   Schedule, PackageNum
user_30294780
Related