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.
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:
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 :)
Hope this helps,
answered Jul 11, 2011 at 10:38 AM