Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lakers34kb
Starting Member
15 Posts |
 Posted - 2010-01-29 : 23:36:38
|
| I have 2 different tables: Customers and OrdersI need to:List all Customers (include CustomerId and CompanyName) that have placed less than 3 orders. Include those who have zero orders placed. Name the count field "OrderCount". Sort by least number of orders placed.This is what I have so far, but all I have showing is the CustomerID and the count for it. I'm assuming I need to use inner join and that's where I need help, I included a print screen of both the tables.SELECT CustomerID, count(*)as OrderCount FROM OrdersGROUP BY CustomerID ORDER BY OrderCounthttp://i45.tinypic.com/2lnjzn8.jpghttp://i48.tinypic.com/m936yx.jpghttp://i47.tinypic.com/2lucaq0.jpg |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-01-29 : 23:46:33
|
[code]select c.CustomerId, count(o.CustomerId)from Customers c left join Orders o on c.CustomerId = o.CustomerIdgroup by c.CustomerIdhaving count(c.CustomerId) < 3[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
lakers34kb
Starting Member
15 Posts |
Posted - 2010-01-30 : 00:00:24
|
| thank you khtan for the help |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 03:17:14
|
| I think you just want a JOIN (i.e. an inner join) and not a LEFT JOIN.You will get customers that have no orders, also you will get SQL warning that COUNT() includes NULLs (that's not a big issue, but it can derail!! some applications) |
|
|
|
|
|
|
INNER JOIN