CODEWITHSUNDEEP
sqlpostgresqlcountsumpivot
user6688
user6688

Reputation: 29

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 :)

Upvotes: 0

Views: 267

Answers (2)

GMB
GMB

Reputation: 222482

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use filter for conditional aggregation:

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 ;

You don't need the outer aggregation, although you could return the results in rows rather than columns more simply:

select "exerciseType", count(*)
from exer
group by "exerciseType";

Note: I strongly discourage the use of escaped column names. Do not use double quotes when defining tables and don't use them in your queries.

Upvotes: 1

Related Questions