x

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.

more ▼

asked Jul 11, 2011 at 07:53 AM in Default

avatar image

tabularyee
160 11 12 17

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jul 11, 2011 at 10:38 AM

avatar image

Oleg
17.5k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2020
x1080
x47
x13

asked: Jul 11, 2011 at 07:53 AM

Seen: 1376 times

Last Updated: Jul 11, 2011 at 08:12 AM

Copyright 2016 Redgate Software. Privacy Policy