RESOLVED: COUNT problem - 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 2008 Forums

Transact-SQL (2008)

RESOLVED: COUNT problem

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2012-06-15 : 11:00:11

I want to add a count field to an existing query. Basically I want to return the number of child records associated with a parent record, like how many invoices does a customer have.

I will show my actual query later, but here is a simple query showing what I think needs to be done.

Sample query without COUNT:

SELECT * FROM Customers c WHERE c.ID=@custId

Sample query with COUNT added:

SELECT c.*, COUNT(a.Id) AS InvoiceCount
FROM Customers c, INvoice a
WHERE c.ID=@custId AND a.CustId=c.Id

Question 1: Will this work?

If not, what is the correct way to do this?

If it is correct, then why does the same technique not work on my more complex query below?

Actual query without COUNT: (this works)

SELECT TOP 50
p.PolicyNumber,
FormLOB.LOB AS xLOB,
comp.Name AS xCompanyName,
prod.Name AS xCSR,
c.Name AS xClientName
FROM Policies p
LEFT JOIN FormLOB ON p.LOB = FormLOB.ID
LEFT JOIN Clients comp ON comp.ID=p.CompanyID
LEFT JOIN Clients prod ON prod.ID=p.CSR
LEFT JOIN Clients c ON c.ID=p.ClientID
WHERE p.SN = @SN ORDER BY p.ModifiedOn DESC

Actual query with COUNT added: (this fails)

SELECT TOP 50
p.PolicyNumber,
FormLOB.LOB AS xLOB,
comp.Name AS xCompanyName,
prod.Name AS xCSR,
c.Name AS xClientName,
COUNT(act.ID) ax xActivityCount
FROM Policies p
LEFT JOIN FormLOB ON p.LOB = FormLOB.ID
LEFT JOIN Clients comp ON comp.ID=p.CompanyID
LEFT JOIN Clients prod ON prod.ID=p.CSR
LEFT JOIN Clients c ON c.ID=p.ClientID
LEFT JOIN PolicyActivity act ON act.PolicyID=p.ID
WHERE p.SN = @SN ORDER BY p.ModifiedOn DESC

The error returned is:

Exception:Column 'Policies.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

which seems pretty specific but I don't know what it's trying to tell me.

kpg

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-15 : 11:11:33

quote:
SELECT c.*, COUNT(a.Id) AS InvoiceCount
FROM Customers c, INvoice a
WHERE c.ID=@custId AND a.CustId=c.Id

Question 1: Will this work?

That would not work - it will complain about needing a GROUP BY clause. To get what you are looking for, you can do this:

SELECT c.*, COUNT(a.Id) OVER(PARTITION BY c.ID) AS InvoiceCount
FROM Customers c, INvoice a
WHERE c.ID=@custId AND a.CustId=c.Id

If you are limiting the query to one custId, you can remve the "PARTITION BY c.ID" and just have "OVER ()".

For your more complex query, the approach would be exactly the same.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-15 : 11:19:26

Using an aggregate function with an OVER clause works great. However, sometimes, it's harder for people to wrap their mind around the concept. Here is another way using a derived (or inline) table:

SELECT c.*, a.InvoiceCount
FROM 
	Customers AS c 
INNER JOIN
	(
		SELECT CustID, COUNT(*) AS InvoiceCount
		FROM Invoice 
		WHERE CustID = @custId
		GROUP BY CustID
	) AS a
	ON c.Id = a.CustId

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2012-06-15 : 11:37:17

Brilliant. Thanks both!

I like the OVER clause becauce it is short and sweet, but true, I don't understand what it's doing at this point time, I'll look it up.

Both were very helpful, thanks.

kpg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources