How to Specify Condition in Count()

Is it possible to specify condition in Count()?

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 ...

How to have condition inside count SQL?

count explain :

• COUNT(*) counts all rows
• COUNT(column) counts non-null value
• COUNT(distinct column) counts distinct non-null value
• COUNT(1) is the same as COUNT(*)

Use case/when + sum :

SELECT
    sum(case when Gender = 'M' then 1 else 0 end ) As MaleCount,
    sum(case when Gender = 'F' then 1 else 0 end ) As FemaleCount
FROM [Session4].[dbo].[Survey]

will produce somethings like this :

MaleCount | FemaleCount
1000      | 1255

Another way is using simple goup by

SELECT
    Gender,
    Count(*)
FROM [Session4].[dbo].[Survey]
GROUP BY
    Gender

will produce :

Gender | Count
M      | 1000
F      | 1255

SQL - Using COUNT() as a WHERE condition

You can't use an aggregate (COUNT((NumKids>4)>2)) directly in a WHERE clause, that's what HAVING clauses are for.

Try the following query

select 
  Animal, COUNT(*) AS Count
from Table
where NumKids > 4
group by Animal
having COUNT(*) >= 2

BigQuery: Specify a condition in count()

add SUM(page LIKE '%astring%') AS pageCounter to your query

SELECT UserId, COUNT(UserId) AS visitCounter, 
  SUM(page LIKE '%astring%') AS pageCounter
FROM [oxidation.TestVisits]
WHERE SessionEndTime >= TIMESTAMP_TO_MSEC(DATE_ADD(CURRENT_TIMESTAMP(),-6,"MONTH"))
      AND LastUserId != 'none'
GROUP BY 1
HAVING visitCounter>=6
AND pageCounter >= 2
ORDER BY 1 ASC

Query with condition on Count

Is this what you want?

SELECT h.Id, h.Date, t.Description,
       ( SUM(CASE WHEN h.TypeId <> 288 THEN 1 ELSE 0 END) +
         MAX(CASE WHEN h.TypeId = 288 THEN 1 ELSE 0 END)            
       ) as Counter

Is it possible to use Count( ) function with WHERE Condition?

First, I would recommend ROW_NUMBER() over COUNT(*). Although they are subtly different (different results when there are duplicates in the ORDER BY), ROW_NUMBER() is more colloquial for what you want to do.

Second, you simply want a CASE expression:

SELECT c.[Team 1], c.[Team 2], c.[Winner], c.[Date],
       (CASE WHEN c.Winner = 'New Zealand'
             THEN ROW_NUMBER() OVER (Partition by c.Winner order by c.[Date])
        END) AS NewZealandWon
FROM Cricket c
ORDER BY c.[Date];

I don't think the GROUP BY is necessary, so I removed it.

Select values with condition count() Python

For count number of E values create helper column tmp and caout values by sum:

df = (df[df.assign(tmp = df['Type']=='E')
           .groupby(['date','Id'])['tmp'].transform('sum').gt(1)])

Conditional COUNT within CASE statement

You were nearly there! I've made a few changes:

SELECT 
  DATEFROMPARTS(YEAR(t1.Date), MONTH(t1.Date),1) AS Draft_Date, 
  Membership,
  COUNT(CASE WHEN t1.Membership = 5 AND t1.Service_Type = 'A' THEN 1 END) as m5stA,
  COUNT(CASE WHEN t1.Membership = 2 AND t1.Service_Type IN ('J','C') THEN 1 END) as m2stJC
FROM Table1 t1 
GROUP BY YEAR(t1.Date), MONTH(t1.Date), Membership

Changes:

• Avoid using apostrophes to alias column names, use ascii standard " double quotes if you must
• When doing a conditional count, put the count outside the CASE WHEN, and have the case when return something (any non null thing will be fine - i used 1, but it could also have been 'x' etc) when the condition is met. Don't put an ELSE - CASE WHEN will return null if there is no ELSE and the condition is not met, and nulls don't COUNT (you could also write ELSE NULL, though it's redundant)
• Qualify all your column names, always - this helps keep the query working when more tables are added in future, or even if new columns with the same names are added to existing tables
• You forgot a THEN in the second WHEN
• You don't necessarily need to GROUP BY the output of DATEFROMPARTS. When a deterministic function is used (always produces the same output from the same inputs) the db is smart enough to know that grouping on the inputs is also fine
• Your example data didn't contain any data that would make the COUNT count 1+ by the way, but I'm sure you will have other conditional counts that work out (it just made it harder to test)

SQL query with having count with condition

In MySql the condition can be written as:

HAVING SUM(logs.action in ('foo', 'bar', 'moo')) < 5

In case there are no matches from the the table logs the above sum will return null, do if you want these rows returned use COALESCE():

HAVING COALESCE(SUM(logs.action in ('foo', 'bar', 'moo')), 0) < 5

---

Related Topics