Multiple counts with multiple tables.

Question

question

tabularyee asked Jul 11, '11

Multiple counts with multiple tables.

I would like to count The different requests by survey Id's and grouping it by SubjectValue I have done this on just the one table with a sub query, but I'm not too sure to do it with several. Could anyone help me out? This is how the 3 tables are joined. The only values of note are

subjectValue - Table A
Request_Id - Table A
Survey_Id - Table C

SELECT     TableA.SubjectValue
FROM          TableB INNER JOIN
                       TableA ON TableB.ID = TableA.Request_ID INNER JOIN
                       Table C ON TableB.Details_ID = TableC.ID

There are 3 different survey Id's so the count will need a where clause on the survey_id. May I also add, that I would like all the separate counts of survey id returned in the same grouped by row. Hope that makes sense. Many thanks in advance.

sql-server-2005 sql count relationships

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-07-11T10:38:50Z

Oleg answered Jul 11, '11

If you have only a handful of predefined Survey\_ID values then you can get the results you need by hardcoding the actual values into the query like this: select pvt.* from ( select TableA.SubjectValue, TableC.Survey_Id, 1 PlaceHolder from TableA inner join TableB on TableA.Request_ID = TableB.ID inner join TableC on TableB.Details_ID = TableC.ID ) src pivot ( count(PlaceHolder) for Survey_Id in ([ID1], [ID2], [ID3]) ) pvt; The quoted values of Survey\_Id are hardcoded and this is a downside of the query above. However, it is easy enough to make it a dynamic pivot, so you don't have to hardcode them. Please beware though that should the total number of distinct Survey\_Id values be big enough, it might make the query results go berserk :) declare @sql varchar(max); select @sql = ' select pvt.* from ( select TableA.SubjectValue, TableC.Survey_Id, 1 PlaceHolder from TableA inner join TableB on TableA.Request_ID = TableB.ID inner join TableC on TableB.Details_ID = TableC.ID ) src pivot ( count(PlaceHolder) for Survey_Id in (' + stuff(( select distinct ', ' + quotename(Survey_Id) from TableC for xml path('')), 1, 2, '') + ')) pvt;'; exec (@sql); Hope this helps, Oleg

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question