Count Question - 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 Question

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-13 : 09:23:49

Hi Guys

My mind has gone blank on this!

Basically I have two tables:

(Sample Data)

Customer:
CustomerID, Name, DOB, Email
1,Jimmy B, 1980-01-03, jimbo@yahoo.com
2,Henry V, 1982-03-23, henryv@yahoo.co.uk

Receipt:
ReceiptID, CustomerID, Date, Status
22,1,2008-01-13,V
23,1,2008-01-13,V
24,1,2008-01-13,V
25,1,2008-01-13,V

These are just very basic tables just for explanation purposes.

I want to do a count of individual customers who have Receipt.Status of V.

So if I use the following query:
SELECT
COUNT(c.CustomerID)
FROM Customer AS c
LEFT JOIN Receipt AS r ON c.CustomerID = r.CustomerID
WHERE r.Status = V

I want the result to show 1, but instead get 4.

Any help???

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 09:27:36

[code]
SELECT COUNT(CustomerID)
FROM
(
SELECT c.CustomerID
FROM Customer c
JOIN Receipt r
ON r.CustomerID=c.CustomerID
GROUP BY CustomerID
HAVING SUM(CASE WHEN r.Status='V' THEN 1 ELSE 0 END)>0
)t
[/code]