2

I have a sql query that I am trying to aggregate counts into, and I'm running into an issue. I've got a table that connects a user to a couple of categories they have saved. UsersToCategories. So the three tables combined would be Users, Categories and UsersToCategories. The following query will give me a list of all categories belonging to a user:

select c.CategoryID, c.CategoryName, utc.SortOrder
from Categories c
inner join UsersToCategories utc on utc.CategoryID = c.CategoryID
where utc.UserID = 1234

I have a further level of detail which has Services. There can be multiple services within a category, in a one-to-many relationship. So you've got Categories from above, Services, and ServicesToCategories

What I need is a way to join that second piece of pseudocode into the top query I wrote so that I get COUNTS of how many services in each category. At this level I don't need to know what the services are, just a raw count of each.

I know this is sort of basic stuff, but I'm not that great at sql, and I can't seem to find an exact answer to my question. Any help is appreciated.

Robby Cornelissen
  • 91,784
  • 22
  • 134
  • 156
optionsix
  • 956
  • 1
  • 8
  • 27
  • 1
    Sample data and desired results would really help elucidate what you are trying to do. – Gordon Linoff Sep 05 '14 at 03:34
  • The above would return something like: 1 | Category A | 1, 2 | Category B | 2 - in the order of CategoryID, CategoryName, SortOrder. If in Category A there are 3 services, Service A, Service B and Service C, I would want the FIRST ROW to return: 1 | Category A | 1 | 3, with the 3 being the TOTAL COUNT of services in that category. Hope that helps explain – optionsix Sep 05 '14 at 03:38

1 Answers1

1

I think you need something close to the following:

select c.CategoryID, c.CategoryName, utc.SortOrder, count(stc.ServiceID)
from Categories c
inner join UsersToCategories utc on utc.CategoryID = c.CategoryID
inner join ServicesToCategories stc on stc.CategoryID = c.CategoryID
where utc.UserID = 1234
group by c.CategoryID, c.CategoryName, utc.SortOrder, utc.UserID
Robby Cornelissen
  • 91,784
  • 22
  • 134
  • 156
  • You had your group by and where clauses in the wrong order, but this was what I was looking for. Thanks! – optionsix Sep 05 '14 at 03:48