Conditional COUNT in SQL

Posted: 10th October 2010 by Tim in SQL
Tags: , , , , , , , , , , ,

Sometimes you want to count the number of results which meet a certain condition. Excel has a COUNTIF(...) function but there is nothing like that in the (current) SQL standard. The solution is to mix the SUM function with a CASE statement. Confused? Let’s see an example.

Imagine you have two tables – student (student_id, name) and result (student_id, course, passed). We want to list all student names, the number of courses they have taken and the number they passed. This could be done in one query:

SELECT  s.student_id AS id,
        s.name AS student_name,
        COUNT(r.course) AS total_courses,
        SUM(CASE r.passed WHEN TRUE THEN 1 ELSE 0 END) AS passed_courses
FROM student s
        LEFT JOIN result r ON s.student_id = r.student_id
GROUP BY s.student_id, s.name

This would give you a result like:

 id | student_name | total_courses | passed_courses 
----+--------------+---------------+----------------
  4 | Mark         |             2 |              0
  3 | Bob          |             4 |              3
  6 | Sam          |             4 |              4
  8 | Kate         |             0 |              0
  7 | Jim          |            15 |              6
  5 | Sarah        |             7 |              6

This syntax conforms to the SQL-99 standard, so feel free to use it on your favourite SQL server.

  1. Phil says:

    Thanks, This could help solve my problem.

    i am not very good at SQL and need to count 2 rows, like below.Datein will have the date something arrives and date out could have the date it leaves and I need to count instances where dateout is empty while datein is populated.

    Datein Dateout
    25/10/10 26/10/10
    26/10/10

    I hopefully will be able to adapt this to fit.

  2. Tim says:

    Hi Phil,

    You can use this technique to solve your problem. To count the number of times Dateout is not set, you can use SELECT SUM (CASE Dateout WHEN NULL THEN 1 ELSE 0 END) FROM table_name. You can also nest the CASE statements. So to count the number of times Dateout is not populated and Datein is, the query will look something like this:

    SELECT SUM (CASE Datein WHEN NULL THEN 0 ELSE (CASE Dateout WHEN NULL THEN 1 ELSE 0 END) END) AS date_in_no_date_out FROM table

    Hope that helps, or at least makes sense.

  3. alvin567 says:

    Hi there,

    is it possible to do this?

    Select count(enabled = 1) from user

    Is using of case statement a standard sql statement?

  4. Martin says:

    Thanks a lot!
    You saved me a lot of time.
    Write to me, if you are in Vienna and want to drink a coffee or something (:

  5. anam says:

    Hi,…

    what about using distinct like this?

    SELECT COUNT(CASE expression WHEN expression THEN DISTNCT column_name ELSE 0 END) FROM table_name.

    is that posible

  6. subbarao says:

    i want to use the countif function for 7 columns in a table. i want the find howmany values are not null in 7 columns and get the result
    Eg. col1,col2,col3,col4,col5,col6,col7
    1 0 0 0 0 1 0
    i want to write the query for getting the count value in the above table. The output for the above is 2. Please advise how to write the query for the same.

  7. Rodney says:

    Here are some ideas to address your interest:

    Method 1: set a product of desired conditions to equal 1 and sum them to give the count.

    SELECT Sum(if(column1=’expectation1′,1,0)*if(column2=’expectation2′,1,0)*if(column3=’expectation3′,1,0)*if(column4=’expectation4′,1,0)*if(column5=’expectation5′,1,0)*if(column6=’expectation6′,1,0)*if(column7=’expectation7′,1,0))

    FROM Table_name

    Explanation: In the above syntax the contents within the sum function will equal 1 whenever all conditions are met. Hence summing the results will actually give the count of the conditions defined.

    The good thing about Method1 syntax is you can also use it with distinct count function, in cases where you want to know how many different things were present while the condition occurred.

    Method2: Count the distinct conditions.

    SELECT Count(Distinct Subject*if(column1=’expectation1′,1,0)*if(column2=’expectation2′,1,0)*if(column3=’expectation3′,1,0)*if(column4=’expectation4′,1,0)*if(column5=’expectation5′,1,0)*if(column6=’expectation6′,1,0)*if(column7=’expectation7′,1,0)) – Count(Distinct Subject*0)

    FROM Table_name

    you could simply put Count(Distinct 0) instead of Count(Distinct Subject*0), but just in case Subject*0 is a value 0 you can catch it and eliminate counting where the column were not met with the subtraction.

    Hope this helps.

  8. Aviator says:

    hi my requirement is like to get the “run id” from table which meets the following criteria the table has following fields also,
    insert_timestamp, status, suspend, decision.

    I want to get the run id which satisfies the following criteria.
    Count of Max (Insert Timestamp) Status is NULL = 0 and
    (Count of Max (Insert Timestamp) Status = Reject + Count of Max (Insert Timestamp) suspend= Y + Count of Max (Insert Timestamp) Decision is no processing) = 0
    kindly help me

  9. Aviator says:

    Hi Any update?

  10. Kazz says:

    I am completely new to the coding world but am in it none the less with a question.
    I have for example 10 questions with choices A, B and C as answers (each with its own value and the A, B and C values differ on each question) I need to add all the A answers together, all the B answers together etc… The platform I am using does not recognize COUNT or IF.
    I would sincerely appreciate any direction at all. TIA

  11. christopher says:

    SELECT IF(COUNT(*) != ‘0’ OR COUNT(*) IS NULL,'(COUNT(*))’,’0′) FROM

    is there a way to return count(*) value from if statements?

  12. Fraggle says:

    Cheers Tim, what a lovely little code excerpt. Can certainly see me using this extensively, makes extracting certain types of figures for analytics much easier. For example I don’t think I can use over clauses/partitioning functions for summing bit fields. Much easier to use this in aggregation than having to cast/convert the bit data type to integer. Added bonus: it will also work on fields with varchar data types!

  13. Sheik Abdul says:

    I want to check if the number is repeated for the next consecutive 2 days using SQL. Please assist.