I need to count the number of incidents per car to groups (100,200,300,400 ), and count the number of cars for each group e.g:

i try to run the code below but the result is only 400 and the total number of cars and i cant see all of the groups in the result.

SELECT
case
when count(incidents) > 400 then '400 ' else count(incidents)::Text END Total_number_of_incidents,
count(distinct car) as number_of_cars
from car_incidents

CodePudding user response：

I would aggregate twice, first by car and then everything. Also, if you get the result in different columns rather than in different rows, you only need to scan the table once.

SELECT count(*) FILTER (WHERE incidents >= 400) AS "400 ",
SELECT count(*) FILTER (WHERE incidents >= 300 AND incidents < 400) AS "300",
SELECT count(*) FILTER (WHERE incidents >= 200 AND incidents < 300) AS "200",
SELECT count(*) FILTER (WHERE incidents >= 100 AND incidents < 200) AS "100"
FROM (SELECT car, count(*) AS incidents
      FROM car_incidents
      GROUP BY car) AS subq;

CodePudding user response：

In your query you count incidents. There is no GROUP BY clause, so you get one result row with the count. You take this count and in case it is greater than 400 you show 400 instead of the actual count.

You want several result rows instead, so you want to count incidents per __________? Or do you want to count at all or only group? Isn't incidents already the number of incidents the car had?

Maybe, just maybe, you want this:

select
  case when incidents > 400 then '400 ' else incidents::Text as total_number_of_incidents,
  count(*) number_of_cars
from car_incidents
group by case when incidents > 400 then '400 ' else incidents::Text;

While this may not what you are after, maybe this answer leads you to where you actaully want to go.