CODEWITHSUNDEEP
mysql
Shiro Pie
Shiro Pie

Reputation: 223

mySQL two counts in one query

I want to count the total number of accidents per year and the amount of car accidents happening per year separately. My code however, it seems to only count the total number of accidents.

So far my code is:

Select Year, Count(*) AS 'Total', Count(*) AS 'Car' 
FROM ACCIDENTS 
WHERE Car = 1 
GROUP BY Year, Car 
Order BY Year ASC;

Table should be something like this:

Year |  Accidents | Car
1990      500        25
1991      521        18

Structure of table:

enter image description here

I would count the years for the total amount of accidents and then the types of accidents would have boolean values to represent whether it was that type of accident.

Upvotes: 0

Views: 87

Answers (1)

Gaurav
Gaurav

Reputation: 1109

Use below query 

Select Year, Count(*) AS 'Total', count(case when car =1 then 1 else null end) FROM ACCIDENTS GROUP BY Year Order BY Year ASC;

Upvotes: 1

Related Questions