Select data with a join

Question

I just started learning SQL now and trying to figure out this scenario:

We have 3 tables:

1. Clients (ClientID, Name, Surname, Age)
2. Products (ProductID, Name, Price)
3. Purchases (PurchaseID, Date, ClientID, ProductID)

What would be the best SQL query that will show the quantity of purchases (how many products they bought) made by clients?

It must only show the clients who made more than 1 purchase.

The result should contain the following fields: Full name, Quantity of purchases.

Answer 1

You could do it like this:

SELECT  CONCAT(Name,' ', Surname) as FullName,
        COUNT(purchaseId) as "Quantity of purchases"
FROM Purchases as P
INNER JOIN Clients as C 
on P.ClientID = C.ClientID
GROUP BY C.ClientID,Name, Surname
HAVING COUNT(PurchaseId) > 1;

DB-Fiddle

As a remark, if Fullname or Surname can be NULL then you would have to add a check for that, could be done with IFNULL()

SELECT  CONCAT(IFNULL(Name,''),' ', IFNULL(Surname,'')) as FullName,
        COUNT(purchaseId) as "Quantity of purchases"
FROM Purchases as P
INNER JOIN Clients as C 
on P.ClientID = C.ClientID
GROUP BY C.ClientID,Name, Surname
HAVING COUNT(PurchaseId) > 1;