SQLite COUNT and LEFT JOIN - how to combine?

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?

Answers


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

Need Your Help

Using ORM for a multi-lingual site

php orm localization

I would like to ask for recommendations using ORM (over MySQL) and PHP. Until today I was a CodeIgniter user but now I want to code with FUEL (http://fuelphp.com) and they have a ORM.

How do I make a TreeParser in ANTLR3?

antlr antlr3

I'm attemping to learn language parsing for fun...