COUNT and WHERE statements query

Question

At the moment I have this

SELECT
    COUNT (enrollment_id) AS enrollments,
    COUNT (result) AS 
    MAX (result) AS highest_result,
    AVG (result) AS average_result,
    MIN (period_code) AS earliest_enrollment    
FROM 
    enrollment
WHERE
result IS NULL

What I want to have is the number of enrollments in total, which is what COUNT(enrollment_id) is for. I then want to count the number of NULL values in the results column to obtain a column for unfinalised_enrollments.

Will the WHERE clause at the bottom affect the other SELECT statements that use the results column? If so how do I make it so that I can COUNT just the number of NULL values in the results column and display it in a column called unfinalised_enrollment.

thanks in advance.

Just perform 2 queries. There is absolutely no reason to try to fit everything in one query — zerkms, Jun 02 '12 at 12:15
It's actually a question and the one query has to include both so that it displays as one table. I've pretty much got everything it's just this one little hiccup. — LewisFletch, Jun 02 '12 at 12:17
if you wish - you could use something like `SUM(result IS NULL)` - this would count amount of null values — zerkms, Jun 02 '12 at 12:18

score 2 · Accepted Answer · answered Jun 02 '12 at 12:18

2

You can use SUM instead of COUNT:

SELECT
    COUNT (enrollment_id) AS enrollments,
    SUM (CASE WHEN result IS NULL THEN 1 ELSE 0 END) AS null_results,
    MAX (result) AS highest_result,
    AVG (result) AS average_result,
    MIN (period_code) AS earliest_enrollment    
FROM enrollment

answered Jun 02 '12 at 12:18

Mark Byers

811,555
193
1,581
1,452

1. `COUNT` and `MIN (period_code)` results may be affected (very likely) 2. `AVG (result)` - would it be affected as well? (haven't tried to perform `AVG` for null-contained values) – zerkms Jun 02 '12 at 12:21
this works for oracle but how about sql server 2008 as the question? – shareef Jun 02 '12 at 12:23
@shareef: `CASE` is ANSI SQL operator – zerkms Jun 02 '12 at 12:24
@shareef: that's standard SQL and should work just fine for SQL Server (or any other SQL database) – Jun 02 '12 at 12:24
@zerkms: Yes, the results will be affected. The query that the OP wrote will likely give the wrong result because some rows won't be included in the counts and no rows will be included in the average. The query I posted should work. – Mark Byers Jun 02 '12 at 12:24
Everything works. Will test later properly, but for now that has fixed my problem, thanks a bunch! – LewisFletch Jun 02 '12 at 12:27
On SQL Server, `AVG(result)` gives a warning which can [easily be disabled](http://stackoverflow.com/questions/1280508/how-do-i-suppress-t-sql-warnings-when-running-a-script-sql-server-2005). – Mark Byers Jun 02 '12 at 12:29
@Mark Byers +1 for fast thinking – shareef Jun 02 '12 at 12:29
@shareef: it's not a fast thinking at all - it's a **very common** pattern *every* DBA/programmer knows – zerkms Jun 02 '12 at 12:42

score 0 · Answer 2 · answered Jun 02 '12 at 12:29

This one should accomplish what you are looking for:

SELECT
    COUNT (enrollment_id) AS enrollments,
    ( SELECT COUNT (result) FROM enrollment WHERE result IS NULL ) AS unfinalised_enrollment,
    MAX (result) AS highest_result,
    AVG (result) AS average_result,
    MIN (period_code) AS earliest_enrollment    
FROM 
    enrollment;

score 0 · Answer 3 · answered Jun 02 '12 at 12:37

You could use:

SELECT
    COUNT (enrollment_id) AS enrollments,
    Count(*) - Count(result) AS null_results,
    MAX (result) AS highest_result,
    AVG (result) AS average_result,
    MIN (period_code) AS earliest_enrollment    
FROM enrollment

Count(*) will count the total number of rows, Count(result)enter code here will count the total number of rows where result is not null

COUNT and WHERE statements query

3 Answers3