Mark Ausloos Mark Ausloos - 1 year ago 61
ASP.NET (C#) Question

sql count in joined tables gives errors on GROUP BY

I am stuck on SELECT query with COUNT on joined tables. I am developping in Visual Studio 2015, using and SQL Server Express x86 (SQL Server 12.0.2269).

This are the tables:


- Id (PK, int, not null)
- Number (varchar(20), not null)
- CategoryId (int, not null)
- Name (varchar(50), not null)
- Description (text, not null)
- IsActive (bit, not null)
- Stock (int, not null)
- SetColors (bit, not null)
- SetSizes (bit, not null)
- MakeId (FK, int, null)
- StockTypeId (FK, int, not null)


- Id (PK, FK, int, not null)
- Name (varchar(50), not null)
- Description (text, null)
- Picture (image, null)


- Id (PK, int, not null)
- ProductId (FK, int, not null)
- GroupId (FK, int, not null)

Each Product can belong to one or more Groups, what is held in the ProductGroups table.

I need a query to select Groups.Id, Groups.Name, Groups.Picture and a count of the products belonging to each group for showing in a databound asp:ListView.

What ever I try, I get errors on the Name and Picture column, because they are "not contained in either an aggregate function or the GROUP BY clause".

I have been searching on the internet for solutions. This is the last query I tried:

SELECT g.Id, g.Name, g.Picture, COUNT(DISTINCT pg.ProductId) as numProd
FROM Groups g
LEFT JOIN ProductGroups pg ON g.Id = pg.GroupId
GROUP BY g.Id, g.Name, g.Picture, pg.ProductId

I don't know what aggregate function I should use on varchar or nullable image fields and grouping on image is not allowed either "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator").

Anyone who can help me to solve this?

In a second step I will also need to filter on StockTypeId, MakeId, CategoryId, isActive and Stock. But that will be easy when the first step should work.

Answer Source

I think this query will do the trick:

SELECT g.Id, g.Name, g.Picture, pg.pg_count
FROM Groups g
JOIN (Select GroupId, Count(*) as pg_count
   FROM ProductGroups Group By GroupId) pg on g.Id = pg.GroupId
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download