How can I count a value in SQL-CodePudding

I have a table with the columns company_id, shipment_id and trip_id. I am asked to find out which companies have less than 4 shipments on a single trip. The query I have written in SQL looks like this:

select company_id, shipment_id, trip_id,
       count(*) as c
from shipment_trip
group by trip_id
having count(*) < 4

The problem is that I am getting incorrect results that differ from what I am seeing while looking at the table. This may have been asked before but I cannot find an answer. Thanks

CodePudding user response：

You need to include company_id in your group by and remove shipment_id from the select e.g.

select company_id, trip_id
from shipment_trip
group by company_id, trip_id
having count(*) < 4

CodePudding user response：

select company_id,trip_id,
count(distinct shipment_id) as c
from shipment_trip
group by company_id,trip_id
having count(distinct shipment_id) <4

CodePudding user response：

You're very close.

You need to drop shipment_id. You also need to add company_id to the GROUP BY clause.

    SELECT company_id
         , trip_id
         , count(*) as c
    FROM   shipment_trip
    GROUP  by copany_id, trip_id  
    HAVING count(*) < 4