Good evening! I have 2 Orders and Customers tables. The first, Orders has a CustomerID column (which is the same as the CustomerID in the Customers table). The second, Customers table, has a Contactname column and a Country . Please tell me how to calculate the number of purchases for each customer?

just in case I attach the schema database schema database

    2 answers 2

    It's late, something the head does not cook:

     SELECT Customers.Contactname, Customers.Country, (SELECT COUNT(*) FROM Orders WHERE Customers.CustomerID = Orders.CustomerID) AS CustomerOrders FROM Customers WHERE CustomerOrders > 1 ORDER BY CustomerOrders DESC
    • one
      the only thing that added is where country = 'france' thanks! - DoneBass
    • a tiny question to you, now the bill comes from 0, and how to make those who have output> 1? - DoneBass
    • Updated for you - Daniel Protopopov
    • so I tried, says invalid column name - DoneBass
    • select t1.ContactName, t1.Country, count(*) from Customers t1 inner join Orders t2 on t2.CustomerID = t1.CustomerID group by t1.ContactName, t1.Country having count(*) > 1 this is an option but how make your version work where customerorders> 1 and country = 'france' - DoneBass
     select c.ContactName, c.Country, count(o.Id) from Customers c left join Orders o on c.Id=o.CustomerId where c.Country='France' group by c.Id, c.ContactName, c.Country having count(o.Id)>=1

    If you need to filter by the presence of a certain number of orders - add having count(o.Id)>=N , where N is the minimum number of orders.

    Fiddle