How to count and group by at the same time in SQL? (Northwind)

Question

I'm learning SQL on famous 'Northwind' database.

So here is schema: https://miro.medium.com/max/4800/1*vluK_68f8_WnlL66qmRrXQ.png

I want to return informations about customers for whom company "united package" has transported at least 7 parcels. So I want to count them.

When I'm trying this:

select c.CompanyName,
count(o.OrderID) as number_packages 

from Customers as c
join Orders as o 
on c.CustomerID = c.CustomerID

join Shippers as s
on o.ShipVia = s.ShipperID
where s.CompanyName like 'united package'
group by c.CompanyName;

It returns me a table with CompanyName correct and in number_packages in all rows 326... So I guess it is number of all of the packages transported by 'United Package'.

How to make it calculating correct?

Answer 1

Your first join condition is incorrect:

from Customers c join
     Orders o 
     on c.CustomerID = c.CustomerID
--------^ should be o