Way of adding a total row to the bottom of a SQL query

Question

I've got a web based report pulling out a load of information from our SQL server and wanted to know the best way of adding a "totals" row to the bottom. The query is full of subqueries, so I'm not sure of the best way of adding a summary row on the bottom.

I know the "best" way would be to do this in the presentation layer, but I've got sweet FA knowledge of the presentation layer (as the chap that made it has since left and no-one has the same skill set...!)

            SELECT 
            dbo.Groups.GroupName, dbo.UserGroups.GroupId, dbo.Users.UserName + ' ' + dbo.Users.Surname AS Consultant,
                dbo.UserGroups.UserId,
               (SELECT  COUNT(*) AS ManagerCount
                FROM     dbo.ClientContacts
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (CreatedOn BETWEEN @startDate AND @endDate)) AS ManagersAdded,
               (SELECT  COUNT(*) AS InterviewCount
                FROM     dbo.Interviews
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (InterviewTypeId = 1) AND (Createdon BETWEEN @startDate AND @endDate)) AS FirstInterviewCount,
               (SELECT  COUNT(*) AS InterviewCount
                FROM     dbo.Interviews AS Interviews_1
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (InterviewTypeId in (1,3,4)) AND (Createdon BETWEEN @startDate AND @endDate)) AS InterviewCount,
                (SELECT  COUNT(*) AS InterviewCount
                FROM     dbo.Interviews AS Interviews_1
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (InterviewTypeId in (2)) AND (Createdon BETWEEN @startDate AND @endDate)) AS TelephoneCount,
               (SELECT  COUNT(*) AS DMSpokenTo
                FROM     dbo.NotebookItems
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (NotebookTypeId = 56) AND (CreatedOn BETWEEN @startDate AND @endDate)) AS DMSpokenTo,
               (SELECT  COUNT(*) AS Appspokento
                FROM     dbo.NotebookItems
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (NotebookTypeId = 85) AND (CreatedOn BETWEEN @startDate AND @endDate)) AS AppSpokenTo,
               (SELECT  COUNT(*) AS Marketed
                FROM     dbo.NotebookItems AS NotebookItems_1
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (NotebookTypeId = 124) AND (CreatedOn BETWEEN @startDate AND @endDate)) AS Marketed,
               (SELECT  COUNT(*) AS CVSent
                FROM     dbo.ApplicantActions
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (StatusId <> 28) AND (JobId IS NOT NULL) AND (CreatedOn BETWEEN @startDate AND @endDate)) AS CVSent, 
               (SELECT  COUNT(*) AS CVSent
                FROM     dbo.ApplicantActions
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (CreatedOn BETWEEN @startDate AND @endDate)) AS Speccv,
               (SELECT  COUNT(*) AS Meetings
                FROM     dbo.DiaryEvents
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (EventTypeID = 29) AND (CreatedOn BETWEEN @startDate AND @endDate)) AS Meetings,
               (SELECT  COUNT(*) AS VacanciesAdded
                FROM     dbo.Jobs
                WHERE  (CreatedUserId = dbo.Users.UserId) AND (CreatedOn BETWEEN @startDate AND @endDate)) AS VacanciesAdded,
               (SELECT  SUM(pc.CommissionPerc / 100) AS PermPlacements
                FROM     dbo.Placements
                INNER JOIN PlacementConsultants pc on placements.PlacementID = pc.PlacementId
                INNER JOIN PlacementSectorDefinedColumns psdc on psdc.PlacementId = placements.PlacementID
                WHERE  (isnull(psdc.notnewbusiness,'N') = 'N') AND (pc.UserId = dbo.Users.UserId) AND (placements.CreatedOn BETWEEN @startDate AND @endDate) AND (PlacementTypeId <> 6)) AS [Perm Placements],
               (SELECT  SUM(PlacementFee / 100 * pc.CommissionPerc) AS PermFee
                FROM     dbo.Placements
                INNER JOIN PlacementConsultants pc on placements.PlacementID = pc.PlacementId
                INNER JOIN PlacementSectorDefinedColumns psdc on psdc.PlacementId = placements.PlacementID
                WHERE  (isnull(psdc.notnewbusiness,'N') = 'N') AND (pc.UserId = dbo.Users.UserId) AND (placements.CreatedOn BETWEEN @startDate AND @endDate) AND (PlacementTypeId <> 6)) AS [Perm Fee],
               (SELECT  SUM(pc.CommissionPerc / 100) AS ContractPlacements
                FROM     dbo.Placements
                INNER JOIN PlacementConsultants pc on placements.PlacementID = pc.PlacementId
                INNER JOIN PlacementSectorDefinedColumns psdc on psdc.PlacementId = placements.PlacementID
                WHERE  (isnull(psdc.notnewbusiness,'N') = 'N') AND (pc.UserId = dbo.Users.UserId) AND (placements.CreatedOn BETWEEN @startDate AND @endDate) AND (PlacementTypeId = 6)) AS [Contract Placements],
               (SELECT  SUM(dbo.CONTRACT_NETT_VALUE_FOR_INITIAL_PLACEMENT(Placements_1.PlacementID) / 100 * pc.CommissionPerc) AS ContractFee
                FROM     dbo.Placements AS Placements_1
                INNER JOIN PlacementConsultants pc on Placements_1.PlacementID = pc.PlacementId
                INNER JOIN PlacementSectorDefinedColumns psdc on psdc.PlacementId = Placements_1.PlacementID
                WHERE  (isnull(psdc.notnewbusiness,'N') = 'N') AND (pc.UserId = dbo.Users.UserId) AND (Placements_1.CreatedOn BETWEEN @startDate AND @endDate) AND (PlacementTypeId = 6)) AS [Contract Value]


            FROM    dbo.Groups INNER JOIN
                    dbo.UserGroups ON dbo.Groups.GroupId = dbo.UserGroups.GroupId INNER JOIN
                    dbo.Users ON dbo.UserGroups.UserId = dbo.Users.UserId 
            WHERE (     
                    (dbo.Users.Inactive = 'N') AND (dbo.UserGroups.GroupId = @GroupId) ) 
                    and users.userid not in (select userid from UserGroups where GroupId = 57)  

One approach could be to do a UNION and then add in another set of subqueries, but that's going to get really messy and ridiculously long winded surely?

Is there a better way of doing it than that?

Answer 1

1. Try ROLLUP.
2. For performance reasons, I would consider using table variables or temp tables to avoid multiple queries of the same tables. Also, table variables and temp tables can have at least a primary key, whereas common table expressions are not indexed.

For example:

DECLARE @GroupName VARCHAR(100)
SELECT @GroupName = g.GroupName
FROM   dbo.Groups g
WHERE  g.GroupId = @GroupId

DECLARE @Users TABLE
(
    UserId INT NOT NULL PRIMARY KEY,
    Consultant VARCHAR(100) NOT NULL
)

INSERT @Users (UserId, Consultant)
SELECT u.UserId, u.UserName + ' ' + u.Surname
FROM   dbo.Users u
INNER JOIN dbo.UserGroups ug ON u.UserId = ug.UserId AND ug.GroupId = @GroupId
WHERE  u.Inactive = 'N'
AND    NOT EXISTS (SELECT 1 FROM dbo.UserGroups ne
                   WHERE  u.UserId = ne.UserId
                   AND    ne.GroupId = 57)

DECLARE @ClientContacts TABLE
(
    UserId INT NOT NULL PRIMARY KEY,
    ManagerCount INT NOT NULL
)

INSERT @ClientContacts (UserId, ManagerCount)
SELECT u.UserId, COUNT(*)
FROM   @Users u
INNER JOIN dbo.ClientContacts cc ON cc.CreatedUserId = u.UserId AND cc.CreatedOn BETWEEN @startDate AND @endDate
GROUP BY u.UserId

DECLARE @Interviews TABLE
(
    UserId INT NOT NULL,
    InterviewTypeId INT NOT NULL,
    InterviewCount INT NOT NULL,
    PRIMARY KEY (UserId, InterviewTypeId)
)

INSERT @Interviews (UserId, InterviewTypeId, InterviewCount)
SELECT u.UserId, i.InterviewTypeId, COUNT(*)
FROM   @Users u
INNER JOIN dbo.Interviews i ON u.UserId = i.CreatedUserId AND i.InterviewTypeId BETWEEN 1 AND 4 AND i.CreatedOn BETWEEN @StartDate AND @EndDate
GROUP BY u.UserId, i.InterviewTypeId

-- Rest is an exercise for the reader, but:

SELECT @GroupName AS GroupName,
       @GroupId AS GroupId,
       CASE GROUPING(u.UserId) WHEN 1 THEN NULL ELSE MIN(u.Consultant) END AS Consultant,
       u.UserId,
       SUM(COALESCE(cc.ManagerCount, 0)) AS ManagersAdded,
       SUM(COALESCE(ip.[1], 0)) AS FirstInterviewCount,
       SUM(COALESCE(ip.[1], 0) + COALESCE(ip.[3], 0) + COALESCE(ip.[4], 0)) AS InterviewCount,
       SUM(COALESCE(ip.[2], 0)) AS TelephoneCount
FROM   @Users u
LEFT JOIN @ClientContacts cc ON u.UserId = cc.UserId
LEFT JOIN (SELECT i.UserId, i.InterviewTypeId, i.InterviewCount
           FROM   @Interviews i) AS ii
          PIVOT (SUM(InterviewCount) FOR InterviewTypeId IN ([1], [2], [3], [4])) AS ip ON u.UserId = ip.UserId
GROUP BY ROLLUP(u.UserId)
ORDER BY GROUPING(u.UserId), u.UserId