Average Column-wise by using T-sql and Pivot

Question

I have developed Financial Year Data Fiscal year wise Till now i have achieved! But i couldn't get column-wise average

My table Definition

CREATE TABLE [dbo].[tblact] (
    [Id]                 INT             NOT NULL,
    [years]              NVARCHAR (MAX)  NULL,
    [months]             NVARCHAR (MAX)  NULL,
    [expenses]           DECIMAL (18, 2) NULL,
    [closingbal]         DECIMAL (18, 2) NULL,
    [monthorder]         INT             NULL

My Query

    CREATE PROCEDURE fiscalyear 
AS
DECLARE @qstr AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
declare @sql nvarchar(max)
SELECT  @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(years)FROM (SELECT DISTINCT years FROM tblact) AS years;
    SET @qstr ='SELECT months, ' + @ColumnName + ',total,average  FROM 
    (SELECT months, years, expenses,avg(expenses) over(partition by months) average,sum(expenses) over (partition by months) total ,monthorder FROM tblact ) AS p 
    PIVOT(SUM(expenses) FOR  years  IN (' + @ColumnName + '))  AS PVTTable order by monthorder  ';
    EXEC sp_executesql  @qstr

Kindly please do help to acheive the answer

My output Now :

Months | 2009-2010 | 2010 - 2011 | 2012-2013 | Total | Average
--------------------------------------------------------------
April  | 2000      | 3000        | 4000      | 9000  |  3000
MAY    | 2000      | 3000        | 4000      | 9000  |  3000
--------------------------------------------------------------

Expected Output

Months | 2009-2010 | 2010 - 2011 | 2012-2013 | Total | Average
--------------------------------------------------------------
April  | 2000      | 3000        | 4000      | 9000  |  3000
MAY    | 2000      | 3000        | 4000      | 9000  |  3000
--------------------------------------------------------------
Average| 2000      | 3000        | 4000      | 9000  |  3000

Kindly Help !

http://stackoverflow.com/questions/14478361/sum-with-sql-server-rollup-but-only-last-summary — mohan111, Apr 27 '15 at 11:33
I couldn't figure our where i can add the query i understand i need to use ROLLUP by where should i add , Kindly Help! — Prhem, Apr 27 '15 at 11:48
Store the results of your current query in a table variable, and then the averages will simply be the SUM divided by the COUNT of each column. — Tab Alleman, Apr 27 '15 at 13:32

Stephan · Accepted Answer · 2015-04-28T12:08:34.170

I cleaned up your code a bit. I simply use a UNION ALL and AVG your columns. Note: I use 13 as the month order for the 'Average' row so when it's ordered, it goes at the end. If you need anything else let me know.

CREATE PROCEDURE fiscalyear 
AS
DECLARE @qstr           NVARCHAR(MAX),
        @ColumnName     NVARCHAR(MAX),
        @AvgColumnName  NVARCHAR(MAX)

SELECT  @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(years),
        @AvgColumnName = COALESCE(@AvgColumnName + ',','') + 'AVG(' + QUOTENAME(years) + ')'
FROM tblact 
GROUP BY years;

SET @qstr ='
WITH CTE
AS
(
    SELECT months, ' + @ColumnName + ',total,average,monthorder  FROM 
    (SELECT months, years, expenses,avg(expenses) over(partition by months) average,sum(expenses) over (partition by months) total ,monthorder FROM tblact ) AS p 
    PIVOT(SUM(expenses) FOR  years  IN (' + @ColumnName + '))  AS PVTTable
),
CTE_Average
AS
(
SELECT months, ' + @ColumnName +',total,average,monthorder
FROM CTE
UNION ALL
SELECT ''Average'',' + @AvgColumnName + ',AVG(total),AVG(average),13
FROM CTE
)

SELECT months,' + @ColumnName + ',total,average
FROM CTE_Average
ORDER BY monthOrder'

EXEC sp_executesql  @qstr

Stephan : Thank you soo much ! Was sitting on it for last three days! The code is absolute! — Prhem, Apr 28 '15 at 02:48
Mr. Stephan., Is there anyway i could hide monthorder column in my output? — Prhem, Apr 28 '15 at 03:03
You need it to sort the data so I just added CTE_Average and select from that instead. Check it out — Stephan, Apr 28 '15 at 12:09

Average Column-wise by using T-sql and Pivot

1 Answers1

Linked