count from 2 tables - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

count from 2 tables

Author

Topic

sbayeta
Starting Member

3 Posts

Posted - 2010-06-08 : 07:58:23

Hi,

I'm trying to count the records on one table based on an id in a different table. Something like this:

Table customer

id customer
--------------
1 John
2 Mike
3 Carl

Table order

id custid amount
----------------------
1 2 100
2 2 40
3 1 75
4 1 35
5 1 100
6 1 40

I'd like to create a query that will return the orders count for each customer, but without omiting the customers that don't have orders:

custid orders
----------------
1 4
2 2
3 0

How can I do this?

Thanks in advance.

Best regards,
Santiago

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-08 : 08:11:12

Select c.id,count(o.id) as orders from customer c left join
order o on o.custid =c.id group by c.id

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sbayeta
Starting Member

3 Posts

Posted - 2010-06-08 : 09:00:06

I omited a small detail. I need a where clause to limit to orders above 50. If I add this clause the query returns no records for customers with no orders matching the criteria.

Any ideas?

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-06-08 : 09:03:59

having count(o.id) >50

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 09:08:48

In that case, use INNER JOIN instead of LEFT JOIN, if you always is having the "number of orders" criteria.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources