santa santa - 5 months ago 12
SQL Question

Count rows in second table with LEFT JOIN

I have a query where I output some results

SELECT
t1.busName,
t1.busCity,
COUNT(t2.ofr_id) AS cntOffers
FROM t1
LEFT JOIN t2 ON (t2.ofr_busID = t1.busID)


The query above returns only one row, however, if I remove COUNT and leave only below query I get multiple results. What am I missing? And how can I fetch results from the first table while getting associated results count from t2?

SELECT
t1.busName,
t1.busCity
FROM t1
LEFT JOIN t2 ON (t2.ofr_busID = t1.busID)

Answer

You need group by:

SELECT t1.busName, t1.busCity,
       COUNT(t2.ofr_id) AS cntOffers
FROM t1 LEFT JOIN
     t2
     ON t2.ofr_busID = t1.busID
GROUP BY t1.busName, t1.busCity;

Most databases would return an error on your version of the query, because you have unaggregated and aggregated columns in the SELECT.

Comments