dcsimg
Results 1 to 8 of 8

Thread: [RESOLVED] SQL - Total in pivot table

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Resolved [RESOLVED] SQL - Total in pivot table

    Hi
    I have a query that gives me the sum of some stuff; and I need to have the total for each row to be presented in a new column called "Total" and also the total of each column be presented in a new row called Total.
    I have been struggling with different attempts but cannot get to do what I need.

    I am pivoting this:
    Code:
     Select Department, DocSubtype, Count(DocNr) AS ContOfUpdates
       From DMS_QDOC_AllUpdates 
       Where Department in (Select Distinct Area From PhoneBookDMS)
       Group by Department,DocSubtype
    Here is my query so far:

    Code:
    DECLARE @cols  AS NVARCHAR(MAX)='';
    DECLARE @query AS NVARCHAR(MAX)='';
    
    SELECT @cols = @cols + QUOTENAME(Department) + ',' FROM (SELECT distinct Department FROM  DMS_QDOC_AllUpdates  Where Department in (Select Distinct Area From PhoneBookDMS)) AS ThisTable
    SELECT @cols = substring(@cols, 0, len(@cols)) --trim "," at end
    
    Set @query = 
    ' SELECT * from 
    (
     Select Department, DocSubtype, Count(DocNr) AS ContOfUpdates
       From DMS_QDOC_AllUpdates 
       Where Department in (Select Distinct Area From PhoneBookDMS)
       Group by Department,DocSubtype
    ) Origin
    pivot 
    (
      Max(ContOfUpdates) for Department in (' + @cols + ')
    ) piv_Table'
    execute(@query)
    In picture:
    I have this:
    Name:  before.jpg
Views: 47
Size:  21.2 KB

    I need this:
    Name:  After.jpg
Views: 48
Size:  22.5 KB

    Thanks for any help.

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,369

    Re: SQL - Total in pivot table

    I don't have ssms to hand to test this but I have a suggestion.

    I think your best bet is to simply add a union to the result of the pivot. In the unioned query simply select the totals by column. I doubt you'll be able to union straight onto the back of a pivot, though, so you'll probably have to wrap that in an outer query, something like:-
    Code:
    Select *
    From (Your whole query goes here, including it's select etc.)
    Union
    Your Total query goes here
    Alternatively, you might be able to inject a union into the source query. With the right tuples your pivot should flip it put to the correct columns and give you an extra total row.

    I'm not sure exactly what your total query will look like given that you're using dynamic sql to get the column list. I'm also a little confused that you're taking the count of instances in the source query and then the max in the pivot - so you're actually displaying the highest count of each Doc Sub Type and Department. Summing that to get a total sounds weird to me so I guess there's something in there I don't understand.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,999

    Re: SQL - Total in pivot table

    As for the Total in the last Row: Check if SQL-Server has a Rollup-Clause in its SQL. I have something similiar here, but on a DB2, and there i use ROLLUP

    EDIT: YeP!
    http://www.sqlservertutorial.net/sql...server-rollup/
    Last edited by Zvoni; Nov 22nd, 2019 at 05:16 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Total in pivot table

    OK, I got some progress (not checked rullup thing yet).
    Now there are only one issues: Horisontally, it only sums when all columns contain values (not null)
    I tried to use IsNull(Department, 0) but no effect.

    Attachment 172487

    [CODE]
    Code:
    DECLARE @cols       NVARCHAR(MAX)=''
    DECLARE @query      NVARCHAR(MAX)=''
    DECLARE @COLS_SUM   NVARCHAR(MAX)=''
    DECLARE @COLS_TOT   NVARCHAR(MAX)=''
    
    --columns for Pivot
    SELECT @cols = @cols + QUOTENAME(Department) + ',' 
    FROM (SELECT DISTINCT Department FROM DMS_QDOC_AllUpdates 
    Where Department in (Select Distinct Area From PhoneBookDMS)) AS tmp
    SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))
    
    -- sum of columns for horizontal sum
    SELECT @COLS_SUM = @COLS_SUM + QUOTENAME(Department) + '+' 
    FROM (SELECT DISTINCT Department FROM DMS_QDOC_AllUpdates 
    Where Department in (Select Distinct Area From PhoneBookDMS)) AS tmp
    
    SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0,

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Total in pivot table

    I can't post complete reply. Will try to post segmented.

    Code:
    LEN(@COLS_SUM)) +' AS Total'
    
    --sum of single columns for vertically sum
    SELECT @COLS_TOT = @COLS_TOT +'SUM('+ QUOTENAME(Department) + '),' 
    FROM (SELECT DISTINCT Department FROM DMS_QDOC_AllUpdates 
    Where Department in (Select Distinct Area From PhoneBookDMS)) AS tmp
    
    SELECT @COLS_TOT = SUBSTRING(@COLS_TOT, 0, LEN(@COLS_TOT)) 
    
    SET @query = 
                'SELECT *'+@COLS_SUM+'  INTO #Tempy FROM 
                (
                  Select Department, DocSubtype, Count(DocNr) AS ContOfUpdates
    		 From DMS_QDOC_AllUpdates 
    		Where Department in (Select Distinct Area From PhoneBookDMS)
    		Group by Department,DocSubtype

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Total in pivot table

    Code:
    Count(DocNr) AS ContOfUpdates
    			   From DMS_QDOC_AllUpdates 
    			   Where Department in (Select Distinct Area From PhoneBookDMS)
    			   Group by Department,DocSubtype
                ) Origin
                PIVOT

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Total in pivot table

    Code:
    (
                 SUM(ContOfUpdates) FOR Department IN (' + @cols + ')
                ) piv_Table
                SELECT * FROM #Tempy
                UNION ALL
                SELECT ''Total -->'' AS TOTAL ,'+@COLS_TOT+', SUM(TOTAL) FROM #Tempy  '
    
    execute(@query)

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Total in pivot table

    OK, it won't let me to type anything more than two lines. I will open a new thread.
    trying again...
    fully functioning also handling nulls:

    [CODE]DECLARE @cols NVARCHAR(MAX)=''
    DECLARE @query NVARCHAR(MAX)=''
    DECLARE @COLS_SUM NVARCHAR(MAX)=''
    DECLARE @COLS_TOT NVARCHAR(MAX)=''

    --columns for Pivot
    SELECT @cols = @cols + QUOTENAME(Department) + ','
    FROM (SELECT DISTINCT Department FROM DMS_QDOC_AllUpdates
    Where Department in (Select Distinct Area From PhoneBookDMS)) AS tmp
    SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))

    -- sum of columns for horizontal sum
    -- COALESCE is necesary for dealling with null values
    SELECT @COLS_SUM = @COLS_SUM + 'COALESCE(' + QUOTENAME(Department) + ',0)+'
    FROM (SELECT DISTINCT Department FROM DMS_QDOC_AllUpdates
    Where Department in (Select Distinct Area From PhoneBookDMS)) AS tmp

    SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +' AS Total'
    %0
    Last edited by Grand; Nov 23rd, 2019 at 05:28 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width