How to get sum of particular column in last row with union

Question

I want to get sum of average

SELECT
        Employees.EmployeeID AS EMPLOYEEID,
        Employees.EMPLOYEENAMESURNAMEFORMAT AS LastFirstName,

        SUM(MAILCOUNT) MAILCOUNT,
        COUNT(*) DAYCOUNT,
        AVG(MAILCOUNT) AVERAGE 
    FROM Employees
    LEFT JOIN ST_LAWERP_DAILYMAIL_PERFORMANCE ON Employees.EmployeeID = ST_LAWERP_DAILYMAIL_PERFORMANCE.EMPLOYEEID
    WHERE Employees.ACTIVESTATUS = 1 
        AND Employees.Loginname<>''
        AND Employees.Title NOT IN ('Chauffeur','Intern','Summer Intern')
        AND ST_LAWERP_DAILYMAIL_PERFORMANCE.DATE BETWEEN '2011-01-01' AND '2015-01-01'

    GROUP BY Employees.EmployeeID,Employees.EMPLOYEENAMESURNAMEFORMAT

Maybe you could use the keyword COMPUTE? http://technet.microsoft.com/en-us/library/ms181708%28v=sql.105%29.aspx — Krisztián Balla, Aug 14 '13 at 11:42
@cutefather, take a look at [this question](http://stackoverflow.com/questions/17934318/add-a-summary-row-with-totals) — i-one, Aug 15 '13 at 09:17

score 1 · Accepted Answer · answered Aug 14 '13 at 11:56

You can use a window function to get the sum of the average:

SELECT
        Employees.EmployeeID AS EMPLOYEEID,
        Employees.EMPLOYEENAMESURNAMEFORMAT AS LastFirstName,

        SUM(MAILCOUNT) MAILCOUNT,
        COUNT(*) DAYCOUNT,
        AVG(MAILCOUNT) AVERAGE ,
        sum(avg(MAILCOUNT)) over () as SUMAVERAGE
    FROM Employees
    LEFT JOIN ST_LAWERP_DAILYMAIL_PERFORMANCE ON Employees.EmployeeID = ST_LAWERP_DAILYMAIL_PERFORMANCE.EMPLOYEEID
    WHERE Employees.ACTIVESTATUS = 1 
        AND Employees.Loginname<>''
        AND Employees.Title NOT IN ('Chauffeur','Intern','Summer Intern')
        AND ST_LAWERP_DAILYMAIL_PERFORMANCE.DATE BETWEEN '2011-01-01' AND '2015-01-01'

    GROUP BY Employees.EmployeeID,Employees.EMPLOYEENAMESURNAMEFORMAT

How to get sum of particular column in last row with union

1 Answers1