Count Based on Condition in SQL Server

Count based on condition in SQL Server

Use SUM/CASE...

SELECT
    COUNT(*),  --total
    SUM(CASE WHEN name = 'system' THEN 1 ELSE 0 END) --conditional
FROM
    myTable

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

Conditional Count on a field

I think you may be after

select 
    jobID, JobName,
    sum(case when Priority = 1 then 1 else 0 end) as priority1,
    sum(case when Priority = 2 then 1 else 0 end) as priority2,
    sum(case when Priority = 3 then 1 else 0 end) as priority3,
    sum(case when Priority = 4 then 1 else 0 end) as priority4,
    sum(case when Priority = 5 then 1 else 0 end) as priority5
from
    Jobs
group by 
    jobID, JobName

However I am uncertain if you need to the jobID and JobName in your results if so remove them and remove the group by,

Counting rows based on a criteria in sql server

Is this what you want?

select count(*) as num_records,
       sum(case when onlineoffline = 'online' then 1 else 0 end) as num_online_record
from (select t.*,
             min(case when onlineoffline = 'online' then trxndate end) over () as min_online_td
      from t
     ) t
where trxndate > min_online_td;

The subquery calculates the date for the first online record. The rest of the query just does the counts that you want.

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

SQL query to count rows based on condition? Keep getting syntax error for my count logic

Here is the syntax error count(bookstatus = R)

You can convert it like below

select sum(bookID) as totalbooks, SUM(CASE WHEN bookstatus = R THEN 1 ELSE 0 END) as returnedbooks
from library with (nolock)
where librarylocation = 'Chesterfield'
--and bookstatus = R

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

How to Get Count(*) and table data based on where condition in single query

If you want to return the count in the same recordset without returning multiple recordsets you can use count() with over()

For example

select *, count(*) over() as Numrows
from tbl_test tt 
where tt.col1 = @param1;

or if the results are larger than an int as suggested by your output data type,

select *, count_big(*) over() as Numrows
from tbl_test tt 
where tt.col1 = @param1;

Also as mentioned, if you want to keep a second result set you can assign the count to your output variable using @@rowcount or rowcount_big().

---

Related Topics