Is it possible to specify condition in Count

Question

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

User · Answer

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  gt  51        then          1        else          0     end   as NumOverSize  from    Inventory  where    customer like   PEPSI    group by    Schedule  PackageNum

User · Answer

If you can t just limit the query itself with a where clause  you can use the fact that the count aggregate only counts the non-null values   select count case Position when  Manager  then 1 else null end  from       You can also use the sum aggregate in a similar way   select sum case Position when  Manager  then 1 else 0 end  from

User · Answer

Assuming you do not want to restrict the rows that are returned because you are aggregating other values as well  you can do it like this   select count case when Position    Manager  then 1 else null end  as ManagerCount from       Let s say within the same column you had values of Manager  Supervisor  and Team Lead  you could get the counts of each like this   select count case when Position    Manager  then 1 else null end  as ManagerCount      count case when Position    Supervisor  then 1 else null end  as SupervisorCount      count case when Position    Team Lead  then 1 else null end  as TeamLeadCount  from

User · Answer

Note with PrestoDB SQL  from Facebook   there is a shortcut   https   prestodb io docs current functions aggregate html     count if x    bigint       Returns the number of TRUE input values  This   function is equivalent to count CASE WHEN x THEN 1 END

User · Answer

I think you can use a simple WHERE clause to select only the count some record

User · Answer

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 · Answer

I know this is really old  but I like the NULLIF trick for such scenarios  and I found no downsides so far  Just see my copy amp pasteable example  which is not very practical though  but demonstrates how to use it   NULLIF might give you a small negative impact on performance  but I guess it should still be faster than subqueries   DECLARE  tbl TABLE   id  int  NOT NULL  field  varchar  50  NOT NULL   INSERT INTO  tbl  id  field  SELECT 1   Manager  UNION SELECT 2   Manager  UNION SELECT 3   Customer  UNION SELECT 4   Boss  UNION SELECT 5   Intern  UNION SELECT 6   Customer  UNION SELECT 7   Customer  UNION SELECT 8   Wife  UNION SELECT 9   Son   SELECT   FROM  tbl  SELECT      COUNT 1  AS  total       COUNT 1  - COUNT NULLIF  field    Manager    AS  Managers       COUNT NULLIF  field    Manager    AS  NotManagers        COUNT 1  - COUNT NULLIF  field    Wife        COUNT 1  - COUNT NULLIF  field    Son     AS  Family  FROM  tbl   Comments appreciated  -

User · Answer

You can also use the Pivot Keyword if you are using SQL 2005 or above  more info and from Technet  SELECT   FROM  Users PIVOT       COUNT Position      FOR Position     IN  Manager  CEO  Employee    as p   Test Data Set      DECLARE  Users TABLE  Position VARCHAR 10   INSERT INTO  Users  Position  VALUES  Manager   INSERT INTO  Users  Position  VALUES  Manager   INSERT INTO  Users  Position  VALUES  Manager   INSERT INTO  Users  Position  VALUES  CEO   INSERT INTO  Users  Position  VALUES  Employee   INSERT INTO  Users  Position  VALUES  Employee   INSERT INTO  Users  Position  VALUES  Employee   INSERT INTO  Users  Position  VALUES  Employee   INSERT INTO  Users  Position  VALUES  Employee   INSERT INTO  Users  Position  VALUES  Employee

User · Answer

Depends what you mean  but the other interpretation of the meaning is where you want to count rows with a certain value  but don t want to restrict the SELECT to JUST those rows     You d do it using SUM   with a clause in  like this instead of using COUNT    e g   SELECT SUM CASE WHEN Position    Manager  THEN 1 ELSE 0 END  AS ManagerCount      SUM CASE WHEN Position    CEO  THEN 1 ELSE 0 END  AS CEOCount FROM SomeTable

User · Answer

Do you mean just this   SELECT Count    FROM YourTable WHERE Position    Manager    If so  then yup that works

User · Answer

Guffa  s answer is excellent  just point out that maybe is cleaner with an IF statement  select count IF Position    Manager   1  NULL   as ManagerCount from

User · Answer

SELECT COUNT    FROM bla WHERE Position    Manager

[sql] Is it possible to specify condition in Count()?

Examples related to sql

Examples related to sql-server

Examples related to tsql