We help IT Professionals succeed at work.

sql 2008  Aggregations

What would be the best syntax for this request?      For each city, how many customers does the database have?

or this one?

2. How many orders has each employee handled?  Please show the employeeID and the number of orders.
Comment
Watch Question

Senior Developer Analyst
Commented:
Use the count function and a group by like this:

select city, count(*) as CustomerCount
from yourTable
group by City

select employeeId, count(*) sa EmployeeCount
from yourTable
group by employeeId

Christopher GordonSenior Developer Analyst
Commented:
typo corrected:

select employeeId, count(*) as EmployeeCount
from yourTable
group by employeeId

Note: assumes employeeId field exists in yourTable table.
1)
SELECT DISTINCT
CITY
,(SELECT COUNT(1) FROM CUSTOMERS C WHERE C.CITY_ID = CC.CITY_ID)
FROM CITIES CC

2)
The same but changing the table names.

Hope this helps.

Regards,
Leandro Nuñez.
Well, I don't know why I wrote that...

Follow gohord's example.
Top Expert 2011

Commented:
select l.city,count|(distinct customerid) as customers
from location as l
left outer join customerlocations as cl
pn l.city=cl.city
group by l.city
order by 1

select e.employeeid,count(distinct orderno) as orders
from employee as e
left outer join orderaction as oa
on e.employeeid = oa.employeeid
group by e.employeeid
order by 1
Steve WalesSenior Database Administrator

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.