SQL query using Sum() and count() functions

I’m trying to query in PostgresQL using the SUM function to get the total of 3 different row types (Root, Dynamic, Test). I used the Sum() function for the first attempt and the Count() function for the second attempt; both didn’t work sadly. I expect a syntax error (since I’m a beginner at using SQL), but I’m not sure what it is and how to fix it!

1st attempt with the Sum() function:

SELECT 
sum(case when "exerciseType" = 'ROOT') as total_root_exercises, 
sum(case when "exerciseType" = 'DYNAMIC') as total_dynamic_exercises,
sum(case when "exerciseType" = 'TEST') as total_test_exercises
FROM exer 
GROUP BY "exerciseType"

2nd attempt with the Count() function:

select 
count(*) as total_root_exercises
where "exerciseType" = 'ROOT',
count(*) as total_Dynamic_exercises
where "exerciseType" in('DYNAMIC'),
count(*) as total_test_exercises
where "exerciseType" in('TEST')
FROM exer

Can I please get help with this? Thank you 🙂

Answer

Consider using the filter syntax to aggreagate functions, which is standard SQL and that Postgres supports:

select 
    count(*) filter(where "exerciseType" = 'ROOT'   ) as total_root_exercises,
    count(*) filter(where "exerciseType" = 'DYNAMIC') as total_Dynamic_exercises,
    count(*) filter(where "exerciseType" = 'TEST'   ) as total_test_exercises 
FROM exer

If you were to write this without the filter syntax (as in your first attempt), a portable syntax is:

select 
    sum(case when "exerciseType" = 'ROOT'    then 1 else 0 end) as total_root_exercises,
    sum(case when "exerciseType" = 'DYNAMIC' then 1 else 0 end) as total_Dynamic_exercises,
    sum(case when "exerciseType" = 'TEST'    then 1 else 0 end) as total_test_exercises 
FROM exer