1

I want to write a query that can show the amount of purchases made in the month of June, grouped by city. So I wrote this query:

SELECT state, city, COUNT(*) 
FROM address 
JOIN person 
JOIN purchase
WHERE purchase.person_FK = person.id 
AND address.person_FK = person.id
AND MONTH(purchase.purchase_date) = 5
GROUP BY state, city
ORDER BY state, city;

But this query doesn't return the cities that have no purchases in that month, and I want to show them. Can you help me?

flpn
  • 1,868
  • 2
  • 19
  • 31

3 Answers3

1

You will need to have a table that provides a listing of all cities you want to show (if you don't already have that). Then you join to the city table as well. Otherwise, your query has no idea which cities to show with a zero count. In addition, you will need to change your JOIN's to LEFT JOIN's

SELECT city.state, city.city, COUNT(*) 
FROM address 
LEFT JOIN person ON person.id = address.person_FK
LEFT JOIN purchase ON purchase.person_FK = person.id
LEFT JOIN city ON purchase.city = city.city
WHERE MONTH(purchase.purchase_date) = 5
GROUP BY address.state, address.city
ORDER BY address.state, address.city;
Matt Spinks
  • 6,380
  • 3
  • 28
  • 47
  • There's any way to do this without creating this City table? – flpn Jun 05 '17 at 18:24
  • 1
    This doesnt work, because A) you need `LEFT JOIN`. B) if you put the join condition on the `WHERE` you end with an `INNER JOIN` again. – Juan Carlos Oropeza Jun 05 '17 at 18:27
  • 1
    Correct. As it stands, your query is only looking up `city` from your purchase table. So the only output you'll get is from the purchase table. If there are states/cities that aren't in your purchase table, then they won't show up at all. You'll need a reference table for your cities/states to see all possible cities/states, even if they are not in the purchase table. – Matt Spinks Jun 05 '17 at 18:27
  • @JuanCarlosOropeza, if you join to a cities table, and all your cities are in there, then an inner join will work just fine. – Matt Spinks Jun 05 '17 at 18:29
  • If you read OP problem, he doesn't have all cities on purchase. Specially because he is filter by month, so less chance to have all the cities. – Juan Carlos Oropeza Jun 05 '17 at 18:30
  • Yes, my mistake. You do need a left join as well. – Matt Spinks Jun 05 '17 at 18:33
1

You need a city table with all the cities, then do a LEFT JOIN.

And put the JOIN condition on the ON section not the WHERE

SELECT Cities.state, Cities.city, COUNT(*) 
FROM  Cities
LEFT JOIN Purchase
  ON Cities.city = Purchase.city
 AND Cities.state = Cities.state
JOIN person 
  ON purchase.person_FK = person.id 
 AND MONTH(purchase.purchase_date) = 5
JOIN address 
  ON address.person_FK = person.id
GROUP BY Cities.state, Cities.city
ORDER BY Citiesstate, Cities.city;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
1

Look at your joins, 'JOIN' is the same as 'INNER JOIN' which only shows results which is in both tables, you'll need to use a LEFT or FULL join to get what you need.

Theres a diagram here which explains them well

dbajtr
  • 2,024
  • 2
  • 14
  • 22