SQLite COUNT and LEFT JOIN - how to combine?

Question

There are two tables: one (P) that contains list of products, the other one (H) contains history of products consumed. Every product can be consumed 0 or more times. I need to build a query that will return all products from P along with number of times every product has been consumed, sorted by the times it's been consumed. Here is what I did:

SELECT P.ID, P.Name, H.Date, COUNT(H.P_ID) as Count 
FROM P 
LEFT JOIN H 
  ON P.ID=H.P_ID  
ORDER BY Count DESC

This seems to work only if history table contains data, but if it does not - the result is incorrect. What am I doing wrong?

Answer 1

You need a group by to get the counts that you need. You also need to apply an aggregate function to H.Date, otherwise it is not clear which date to pick:

SELECT P.ID, P.Name, COUNT(H.P_ID) as Count, MAX(H.Date) as LastDate
FROM P 
LEFT JOIN H ON P.ID=H.P_ID
GROUP BY P.ID, P.Name
ORDER BY Count DESC

I picked MAX(H.Date) to produce the date of last consumption; if you need a different date from H, change the aggregating function.

I am not sure if sqlite lets you sort by alias; if it does not, replace

ORDER BY Count DESC

with

ORDER BY COUNT(H.P_ID) DESC