Output several counts of one table records in one row

Question

Here is an example table CALLRECORD:

    +--------+------------+
    |callid  |   rating   |
    |1       |            |
    |2       |   5        |
    |3       |            |
    |4       |   1        |
    |5       |            |
    +--------+------------+

No problem to output total number of calls, number of rated calls, average rating and number of unrated calls:

select count(*) as total from callrecord;
select count(*) as rated, avg(rating) as average_rating from callrecord where rating is not null;
select count(*) as unrated from callrecord where rating is null;

    +--------+
    |total   |
    |5       |
    +--------+

    +--------+------------+
    |rated   |average     |
    |2       |3           |
    +--------+------------+

    +--------+
    |unrated |
    |3       |
    +--------+

I'm looking for how to output all above to one row with single SQL request:

    +--------+--------+------------+---------+
    |total   |rated   |average     |unrated  |
    |5       |2       |3           |3        |
    +--------+--------+------------+---------|

db<>fiddle here

Answer 1

Most aggregate functions ignore null values, so what you want is simpler that you may think:

select 
    count(*) total,                  -- total number of rows
    count(rating) as rated,          -- count of non-null ratings
    avg(rating) average,             -- avg ignore `null`
    count(*) - count(rating) unrated -- count of null ratings
from mytable

Answer 2

Try using the SUM aggregation with a CASE statement inside of it. Example below.

 Select 
        COUNT(*) AS 'Total', 
        SUM(CASE WHEN rating IS NULL THEN 0 ELSE 1 END) AS 'Rated',
        (SUM(CASE WHEN rating IS NULL THEN 0 ELSE rating END)/SUM(CASE WHEN rating IS NULL THEN 0 ELSE 1 END)) AS 'Avg',
        SUM(CASE WHEN rating IS NULL THEN 1 ELSE 0 END) AS 'Unrated'
 From callrecord