how to display Grand Totals.

Ramco Ramco

In below query how to display Grand Totals.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(T2."U_A_M") 
            FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> 'NA'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
    
set @query = 'SELECT itemcode, ' + @cols + ' from 
            (
                SELECT T1."ItemCode",T1."LineTotal", T2."U_A_M"
    FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> ''NA''
           ) x
            pivot 
            (
                 sum(linetotal)
                for U_A_M in (' + @cols + ')
            ) p '


execute(@query)

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramco Ramco1y ago
Hi

I want to display total of all items.

itemcode   Admin   Purchase   Sales   GrandTotal
3000740003   NULL   61628.000000   NULL   61628.000000
3000820001   NULL   NULL   141251.000000   141251.000000
3000740004   1000625.000000   NULL   NULL   1000625.000000
3000810002   1306931.800000   NULL   NULL   1306931.800000



2307557 61628 141251 2510436

Thanks
0
Sophia Carter1y ago
To display Grand Totals in your SQL query, you can achieve this by modifying the existing query which already uses the PIVOT function to display subtotals for each distinct value of "U_A_M."

To add Grand Totals, you can follow these steps:

1. Add an additional column in the inner query that calculates the total for each row, summing up all the corresponding "LineTotal" values.

2. Include the newly added total column in the dynamic pivot query defined by @query.

3. Modify the pivot clause within @query to include an additional item that sums up the Grand Total.

Here's an example of how you can adjust your query to display Grand Totals:
```
DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(T2."U_A_M") 
            FROM Opch T0 
            INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry"
            INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode"
            WHERE T2.U_A_M <> 'NA'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = '
SELECT itemcode, ' + @cols + ', GrandTotal
FROM 
    (
        SELECT T1."ItemCode", T1."LineTotal", T2."U_A_M",
               SUM(T1."LineTotal") OVER (PARTITION BY T1."ItemCode") AS GrandTotal
        FROM Opch T0
        INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry"
        INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode"
        WHERE T2.U_A_M <> ''NA''
    ) x
    PIVOT 
    (
        SUM(linetotal)
        FOR U_A_M IN (' + @cols + ')
    ) p'

EXECUTE (@query)
```
In this revised query:

- A new column "GrandTotal" is calculated using the `SUM` window function to get the total for each specific "ItemCode."

- The "GrandTotal" column is added to the SELECT list in the dynamic pivot query.

- The pivot function remains the same to display subtotals based on "U_A_M" values, while the "GrandTotal" now appears in the final result set.

By following these adjustments, you should be able to enhance your existing query to include the Grand Totals alongside the existing subtotals based on "U_A_M."

I hope this explanation helps you in achieving the desired output with Grand Totals. Let me know if you need further clarification or assistance!
0