COUNT query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

COUNT query

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-05-08 : 09:59:49

I have two tables


clients
-------

clientid  clientname
--------  ----------
1         Bill Smith
2         Steve Murray
3         Candice Wells


Payments
--------

clientid  date       amount
--------  ----       ------
2         02/03/2008 120.25
1         02/05/2008 112.00
1         02/07/2008 115.00
1         02/12/2008 116.50

I want to return the clientname from the clients table for any client(s) that have made any payments to give:


clientname     payments
----------     --------
Bill Smith     3
Steve Murray   1
Candice Wells  0

How do I do this please?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 10:02:25

[code]
SELECT c.clientid, payments = isnull(COUNT(p.clientid), 0)
FROM client c
left JOIN payments p ON c.clientid = p.clientid
GROUP BY c.clientid
[/code]

KH
[spoiler]Time is always against us[/spoiler]

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-08 : 10:02:37

SELECT a.clientname, (SELECT COUNT(1) FROM [payments] b WHERE b.clientid = a.clientid) AS payments FROM [clients] a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 10:49:22

SELECT c.clientname,p.payments
FROM Clients c
CROSS APPLY (SELECT COUNT(*) AS payments
             FROM Payments
             WHERE clientid=c.clientid)p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources