CODEWITHSUNDEEP
sql
leon Hill
leon Hill

Reputation: 93

How to count only 1s in SQL?

I am trying to run a report to see how many vehicles need a repair over the last month and how many need a service. As shown below, if a vehicle has had a repair, it has a 1 assigned to it, otherwise, it's a 0. This is my attempt currently and it's returning both 1 and 0s which I don't want. Any help is much appreciated.

Table

 select 
   vehicles_inspections.Vehicle_ID, 
   make, 
   model, 
   count(Repair), 
   count(Service) 
 from vehicles_inspections, vehicles 
 where vehicles.Vehicle_ID = vehicles_inspections.Vehicle_ID  
 group by Vehicle_ID

Upvotes: 0

Views: 276

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

This is your existing where clause:

where vehicles.Vehicle_ID = vehicles_inspections.Vehicle_ID

To that clause, add this:

and repair_service_complete = 0
and repair = 1

or whatever similar filter achieves your business requirement.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

With the data you have shown, you can simply use sum() rather than count():

select vi.Vehicle_ID, v.make, v.model, sum(vi.Repair), sum(vi.Service)
from vehicles v join
     vehicles_inspections vi
     on v.Vehicle_ID = vi.Vehicle_ID 
group by vi.Vehicle_ID, v.make, v.model;

This works because the values appears to be either 0 or 1. In other words, these columns are designed as flags that can be summed up for this purpose.

If they could take on other values, you would use something like:

sum(case when repair = 1 then 1 else 0 end) as repairs

Notes:

  • You should qualify all column names. I'm guessing where the columns are coming from.
  • Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

Upvotes: 3

Related Questions