'How can I count a value in SQL

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

mysqlsql


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 CHill60
Solution 2 mikasa
Solution 3 Dennis

Related Questions