Get how many customer for a city-CodePudding

i'm currently strugling with an sql query i have to make, the aim is simple i have a table of customers and a table of purchases, it is linked via a foreign key in the purchase table.

the model is like that : the model in image

i guess i have to use the HAVING and COUNT functions in sql but i can't really figure out how to write the query correctly to have a result like that :

CITY	Number_of_customer
Paris	7
London	3

CodePudding user response：

SELECT C.CITY,COUNT(C.CUSTOMER_ID)CUSTOMER_COUNT
FROM CUSTOMER C
WHERE EXISTS
(
    SELECT 1 FROM PURCHASE_ORDER AS RD 
      WHERE C.CUSTOMER_ID=RD.CUSTOMER_ID
)
AND C.CITY IN('Paris','London')-- not sure if it is necessary
 GROUP BY C.CITY

CodePudding user response：

If all you want is # customers per city:

SELECT city, COUNT(*) n_customers
FROM "CUSTOMER"
GROUP BY city
ORDER BY n_customers DESC

If you only want to count customers that have made at least one purchase:

SELECT city, COUNT(*) n_customers
FROM (
    SELECT t1.id, t1.city FROM "CUSTOMER" t1
    INNER JOIN "PURCHASE_ORDER" t2 ON t1.id = t2.customer_id
    GROUP BY t1.id
)
GROUP BY city
ORDER BY n_customers DESC;

CodePudding user response：

Thank you all for your answers !

In the mean time i've figured myself a way to do it.

Here's my query :

SELECT city, count(purchase_order.id) FROM `customer` left join purchase on customer.id = purchase.customer_id GROUP BY city;

do you have any suggestion on how i could have done this better/more efficiently ?

Again thank you all for those quick answers, i trully appreciate !