Sum of a specific column in SQL Select Statement

Question

I am creating following SQL Table

SELECT Nr, nu_betrag_wert AS Amount
FROM 
    (SELECT DISTINCT 
         processid, nu_betrag_wert, Nr
     FROM NU_WERTE 
     WHERE Nr <> '(Nr)' AND nu_betrag_wert is NOT NULL) AS nu_betrag

to get the a table of two columns with the lines and the amount.

Now all I want is the sum of the column Amount in an extra row directly under the last row.

I tried something like this, which didn't work:

SELECT  
    Nr, SUM(nu_betrag_wert) AS Zahlbetrag
FROM 
    (SELECT DISTINCT 
         processid, nu_betrag_wert, Nr
     FROM NU_WERTE 
     WHERE Nr <> '(Nr)' AND nu_betrag_wert is NOT NULL) AS nu_betrag
GROUP BY 
    Nr WITH ROLLUP

Can anyone help me please?

Can you please create sample database with few entries on SQLfiddle ? — Jigar, Jun 23 '16 at 13:53

Paurian · Answer 1 · 2016-06-23T13:55:40.943

I've successfully used UNION across two SELECTs to produce what I think you're requesting - where it shows both the list of values and the final summation in a separate row at the bottom.

Maybe something like this:

CREATE TABLE #nu_werte ( processid INT IDENTITY, nu_betrag_wert int, nr VARCHAR(10) )

INSERT INTO #nu_werte ( nr, nu_betrag_wert )
SELECT 'Gut', 1 UNION
SELECT '(Nr)', 3 UNION
SELECT 'Gut', 5 UNION
SELECT 'Tag', 6 UNION
SELECT 'Tag', 8

WITH nu_betrag ( processid, nu_betrag_wert, nr )
AS
(
  SELECT
    processid,
    nu_betrag_wert,
    nr
  FROM
    #nu_werte
  WHERE
    nr != '(Nr)' AND
    nu_betrag_wert IS NOT NULL
)

SELECT nr, nu_betrag_wert AS amount
FROM
  nu_betrag
UNION
SELECT
  NULL, SUM(nu_betrag_wert) AS amount
FROM
  nu_betrag

gofr1 · Answer 2 · 2016-06-23T13:56:45.707

Just checked:

;WITH NU_WERTE AS (
SELECT DISTINCT *
FROM (VALUES
(1,10,'(N0)'),(2,20,'(N0)'),(3,30,'(N0)'),(4,40,'(N0)'),(5,60,'(N0)'),
(6,11,'(N1)'),(7,22,'(N1)'),(8,33,'(N1)'),(9,44,'(N1)'),(10,NULL,'(N1)'),
(11,NULL,'(N1)'),(12,20,'(Nr)'),(13,10,'(Nr)')
) as t (processid, nu_betrag_wert, Nr)
)

SELECT Nr, nu_betrag_wert AS Amount
FROM 
    (SELECT DISTINCT 
         processid, nu_betrag_wert, Nr
     FROM NU_WERTE 
     WHERE Nr <> '(Nr)' AND nu_betrag_wert is NOT NULL) AS nu_betrag
UNION ALL
SELECT  
    Nr, SUM(nu_betrag_wert) AS Zahlbetrag
FROM 
    (SELECT DISTINCT 
         processid, nu_betrag_wert, Nr
     FROM NU_WERTE 
     WHERE Nr <> '(Nr)' AND nu_betrag_wert is NOT NULL) AS nu_betrag
GROUP BY 
    Nr WITH ROLLUP

Output:

Nr      Amount
(N0)    10
(N0)    20
(N0)    30
(N0)    40
(N0)    60
(N1)    11
(N1)    22
(N1)    33
(N1)    44
(N0)    160
(N1)    110
NULL    270

Sum of a specific column in SQL Select Statement

2 Answers2