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.
SELECT *
, but SELECT COUNT(*)
, which is a totally different beast.
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 ...
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 ...
else
part, just end
it right after the 1
.
else
in as it better documents the results of the case statement, especially for novie SQL developers. For brevity, it can be removed in this case.
@Guffa 's answer is excellent, just point out that maybe is cleaner with an IF statement
select count(IIF(Position = 'Manager', 1, NULL)) as ManagerCount
from ...
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
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
You can also use the Pivot Keyword if you are using SQL 2005 or above
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')
Do you mean just this:
SELECT Count(*) FROM YourTable WHERE Position = 'Manager'
If so, then yup that works!
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&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 :-)
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)
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
SELECT COUNT(*) FROM bla WHERE Position = 'Manager'
I think you can use a simple WHERE clause to select only the count some record.
Success story sharing
null
is special. Usecase when IntegerField is null then ...
SUM(CONVERT(int, IsManager))
else null
forcase
statements, so thecount()
example can be 10 characters shorter (if you count the space).