Sum of group records SQL Server 2008

Question

I am creating an SSRS report in SQL Server 2008. The resultant table is grouped on basis of Business Unit. My requirement is to show total sum after each group set.

Here is the query:

    SELECT 
DATENAME(MONTH, A.scheduledstart) Months
,A.scheduledstart ScheduledStart
,B.name BusinessUnit
,CASE WHEN FC.renre_topbrokername='Yes' THEN 'Broker'
      WHEN FC.renre_typevendorpartner<>0 THEN 'Vendor'
      WHEN FC.renre_typemediacontact<>0 THEN 'Media'
      WHEN FC.renre_typeinvestmentmanager<>0 THEN 'Investment Manager'
      WHEN FC.renre_typeclient<>0 THEN 'Client'
      WHEN FC.renre_typeinvestor<>0 THEN 'Investor'
      WHEN FC.renre_typemitigationresources<>0 THEN 'Mitigation'
      WHEN FC.renre_typeriskaccountingsp<>0 THEN 'Risk/Accounting Services Provider'
      WHEN FC.renre_typeindustryanalyst<>0 THEN 'Industry Analyst'
      WHEN FC.renre_typeratingagencies<>0 THEN 'Rating Agency'
      WHEN FC.renre_typecommunity<>0 THEN 'Community'
      WHEN FC.renre_typeresearchprovider<>0 THEN 'Research Provider'
      WHEN FC.renre_typefederal<>0 THEN 'Federal, State or Local Regulator'
      WHEN FC.renre_typeindustryassociation<>0 THEN 'Industry Association'
      WHEN FC.renre_typeboardmember<>0 THEN 'Board Member'
      WHEN FC.renre_typeinvestmentbank<>0 THEN 'Investment Bank'
 END AttendeeType
,AP.partyidname Name
,CASE WHEN FC.renre_keycontact=0 THEN 'No' ELSE 'Yes' END isKeyContact
,A.owneridname Owner
,COUNT(AP.partyidname) AttendeeCount

from 
FilteredAppointment A JOIN FilteredBusinessUnit B
ON A.owningbusinessunit=B.businessunitid
JOIN FilteredActivityParty AP ON A.activityid = AP.activityid
JOIN FilteredContact FC ON FC.contactid = AP.partyid

WHERE 
A.scheduledstart='2014-05-31 15:00:00.000'
and
AP.participationtypemask=6

group by B.name,A.scheduledstart,AP.partyidname,
CASE WHEN FC.renre_topbrokername='Yes' THEN 'Broker'
      WHEN FC.renre_typevendorpartner<>0 THEN 'Vendor'
      WHEN FC.renre_typemediacontact<>0 THEN 'Media'
      WHEN FC.renre_typeinvestmentmanager<>0 THEN 'Investment Manager'
      WHEN FC.renre_typeclient<>0 THEN 'Client'
      WHEN FC.renre_typeinvestor<>0 THEN 'Investor'
      WHEN FC.renre_typemitigationresources<>0 THEN 'Mitigation'
      WHEN FC.renre_typeriskaccountingsp<>0 THEN 'Risk/Accounting Services Provider'
      WHEN FC.renre_typeindustryanalyst<>0 THEN 'Industry Analyst'
      WHEN FC.renre_typeratingagencies<>0 THEN 'Rating Agency'
      WHEN FC.renre_typecommunity<>0 THEN 'Community'
      WHEN FC.renre_typeresearchprovider<>0 THEN 'Research Provider'
      WHEN FC.renre_typefederal<>0 THEN 'Federal, State or Local Regulator'
      WHEN FC.renre_typeindustryassociation<>0 THEN 'Industry Association'
      WHEN FC.renre_typeboardmember<>0 THEN 'Board Member'
      WHEN FC.renre_typeinvestmentbank<>0 THEN 'Investment Bank'
 END
 ,FC.renre_keycontact
 ,CASE WHEN FC.renre_keycontact=0 THEN 'No' ELSE 'Yes' END 
 ,A.owneridname

Here is the output:

At 8th row of output I need to print total count of records in this group (i.e 7) under column AttendeeCount. There can be multiple group sets so in that case total count would be after each group.

Please help.

Answer 1

You should keep the query data set as it is at the moment and build the grouping and aggregation into the SSRS report itself.

You've queried the data from the database, you just need to use that data in the report now...

Create a report and add a table (or matrix if required but doesn't look like it). Create a dataset using your query above. Link the table to the Dataset and then add a group by right clicking on the table and adding grouping based on your grouping requirements. SSRS can then automatically add sub totals and the like for you.

Answer 2

In SQLServer2008 for manipulating sets of results, you can use the GROUP BY clause with the GROUPING SETS

SELECT DATENAME(MONTH, A.scheduledstart) Months
       ,A.scheduledstart ScheduledStart
       ,B.name BusinessUnit
       ,CASE WHEN FC.renre_topbrokername='Yes' THEN 'Broker'
             WHEN FC.renre_typevendorpartner<>0 THEN 'Vendor'
             WHEN FC.renre_typemediacontact<>0 THEN 'Media'
             WHEN FC.renre_typeinvestmentmanager<>0 THEN 'Investment Manager'
             WHEN FC.renre_typeclient<>0 THEN 'Client'
             WHEN FC.renre_typeinvestor<>0 THEN 'Investor'
             WHEN FC.renre_typemitigationresources<>0 THEN 'Mitigation'
             WHEN FC.renre_typeriskaccountingsp<>0 THEN 'Risk/Accounting Services Provider'
             WHEN FC.renre_typeindustryanalyst<>0 THEN 'Industry Analyst'
             WHEN FC.renre_typeratingagencies<>0 THEN 'Rating Agency'
             WHEN FC.renre_typecommunity<>0 THEN 'Community'
             WHEN FC.renre_typeresearchprovider<>0 THEN 'Research Provider'
             WHEN FC.renre_typefederal<>0 THEN 'Federal, State or Local Regulator'
             WHEN FC.renre_typeindustryassociation<>0 THEN 'Industry Association'
             WHEN FC.renre_typeboardmember<>0 THEN 'Board Member'
             WHEN FC.renre_typeinvestmentbank<>0 THEN 'Investment Bank' END AttendeeType
       ,AP.partyidname Name
       ,CASE WHEN FC.renre_keycontact=0 THEN 'No' ELSE 'Yes' END isKeyContact
       ,A.owneridname Owner
       ,COUNT(AP.partyidname) AttendeeCount
FROM FilteredAppointment A JOIN FilteredBusinessUnit B
  ON A.owningbusinessunit=B.businessunitid
    JOIN FilteredActivityParty AP ON A.activityid = AP.activityid
    JOIN FilteredContact FC ON FC.contactid = AP.partyid
WHERE A.scheduledstart='2014-05-31 15:00:00.000'
  AND AP.participationtypemask=6
GROUP BY GROUPING SETS(
  (B.name,A.scheduledstart,AP.partyidname,
   CASE WHEN FC.renre_topbrokername='Yes' THEN 'Broker'
        WHEN FC.renre_typevendorpartner<>0 THEN 'Vendor'
        WHEN FC.renre_typemediacontact<>0 THEN 'Media'
        WHEN FC.renre_typeinvestmentmanager<>0 THEN 'Investment Manager'
        WHEN FC.renre_typeclient<>0 THEN 'Client'
        WHEN FC.renre_typeinvestor<>0 THEN 'Investor'
        WHEN FC.renre_typemitigationresources<>0 THEN 'Mitigation'
        WHEN FC.renre_typeriskaccountingsp<>0 THEN 'Risk/Accounting Services Provider'
        WHEN FC.renre_typeindustryanalyst<>0 THEN 'Industry Analyst'
        WHEN FC.renre_typeratingagencies<>0 THEN 'Rating Agency'
        WHEN FC.renre_typecommunity<>0 THEN 'Community'
        WHEN FC.renre_typeresearchprovider<>0 THEN 'Research Provider'
        WHEN FC.renre_typefederal<>0 THEN 'Federal, State or Local Regulator'
        WHEN FC.renre_typeindustryassociation<>0 THEN 'Industry Association'
        WHEN FC.renre_typeboardmember<>0 THEN 'Board Member'
        WHEN FC.renre_typeinvestmentbank<>0 THEN 'Investment Bank'
   END
   ,FC.renre_keycontact
   ,CASE WHEN FC.renre_keycontact=0 THEN 'No' ELSE 'Yes' END 
   ,A.owneridname), (B.name))