CODEWITHSUNDEEP
sqlsql-server-2008
LewisFletch
LewisFletch

Reputation: 125

COUNT and WHERE statements query

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.

Upvotes: 1

Views: 560

Answers (3)

Robert Wagner
Robert Wagner

Reputation: 17793

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

Upvotes: 0

Kjir
Kjir

Reputation: 4547

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;

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838926

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

Upvotes: 2

Related Questions