Display Grand Total only once in SQL Server using RowNumber

Hi ramco1917,

Yes you can use RowNumber function.

Refer below example. I have used the CASE statement and calculate the total for each group.

SQL

DECLARE @Test AS TABLE (StcCode INT, DocDate VARCHAR(20), BpGSTN NUMERIC(18,2))
INSERT INTO @Test VALUES(19137, 'CSGST-18', 13525.2)
INSERT INTO @Test VALUES(19137, 'CSGST-18', 13525.2)
INSERT INTO @Test VALUES(19137, 'CSGST-18', 384.95)
INSERT INTO @Test VALUES(19137, 'CSGST-18', 384.95)
INSERT INTO @Test VALUES(19138, 'CSGST-18', 164.52)
INSERT INTO @Test VALUES(19138, 'CSGST-18', 164.52)

;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY StcCode ORDER BY StcCode) [RowNumber],
        StcCode, DocDate, BpGSTN
    FROM @Test
)

SELECT StcCode, DocDate, BpGSTN,
    CASE WHEN [RowNumber] = 1 
             THEN CAST((SELECT SUM(BpGSTN) FROM @Test WHERE BpGSTN = CTE.BpGSTN) AS VARCHAR(20))
         ELSE '' END [Total]
FROM CTE

Screenshot