sum of particular columns in SQL-CodePudding

I have bicycle rental data and I have grouped it by number of uses per day using the following code in postgresql:

    SELECT date_trunc('day', rental_date) FROM rentalinfo;

SELECT COUNT(date_trunc('day', rental_date)) as counted_leads,
     date_trunc('day', rental_date) as count_date
FROM rentalinfo
GROUP BY date_trunc('day', rental_date)
ORDER BY date_trunc('day', rental_date) ASC;

the result gives a column called counted_leads which contains number of rentals per day. I want to make a query where I can extract and sum the number of rentals on weekends and weekdays separately. I tried for weekdays:

SELECT SUM(counted_leads) AS sum_date WHERE count_date NOT IN ('2021-12-04',..)

but it I get an error saying "ERROR: syntax error at or near "SELECT".

How can I fix it, please?

CodePudding user response：

Use extract(dow ...) in the where clause to filter all weekday (or weekend) rows and count them:

select count(*) as weekdays
from rentalinfo
where extract(dow from rental_date) in (1, 2, 3, 4, 5)

Or use conditional aggregation:

select count(case when extract(dow from rental_date) in (1, 2, 3, 4, 5) then 1 end) as weekdays
     , count(case when extract(dow from rental_date) in (0, 6) then 1 end) as weekends
from rentalinfo