P
P
Pasha2017-02-04 21:58:21
SQL
Pasha, 2017-02-04 21:58:21

How to show the number of orders of each customer from a particular country?

Good evening! I have 2 tables Orders and Customers. 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 and Country column. Navidite please on the idea of ​​how you can calculate the number of purchases for each customer?
purchases are counted, the only question left is how to make a conclusion only for those who have more than one purchase?
Just in case, I am attaching the DB schema https://camo.githubusercontent.com/ad43beb5737bc55...
it turned out to be solved like this:
SELECT Customers.Contactname,
Customers.Country,
(SELECT COUNT(*) FROM Orders WHERE Customers.CustomerID = Orders.CustomerID ) AS Customer
Orders FROM Customers
ORDER BY CustomerOrders DESC
displays all customers with purchases from 0
how to display those who have them > 1 ?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
N
nozzy, 2017-02-05
@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

D
d-stream, 2017-02-04
@d-stream

select
customer, count(*)
from orders
inner join customers <...>
where country = <>
group by customers
or remove where and group by country and customer

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question