[ Blue82 @ 02.07.2022. 12:54 ] @
Kako da dobijem na dnu tabele sumu kolone uz pomoć GROUP BY ROLLUP?

Code (sql):

WITH Grupa1 AS (
     SELECT
          t2.Referent,
          FORMAT(SUM ((t1.Kolicina*t1.cena)-(t1.kolicina*t1.cena)*t1.rabat/100), 'N') AS VrednostPonuda
     FROM ponudaSta t1
     LEFT JOIN PonudaZag t2 ON t2.RbrPonude = t1.RbrPonude
     WHERE t2.Datum >='2022-1-1'
     GROUP BY ROLLUP (t2.Referent)    
),
Grupa2 AS (
     SELECT Referent, COUNT(RbrPonude) AS BrojSvihPonuda
     FROM PonudaZag
     WHERE Datum >='2022-01-01'
     GROUP BY Referent
)
SELECT Grupa1.Referent, Grupa1.VrednostPonuda, Grupa2.BrojSvihPonuda
FROM Grupa2
JOIN Grupa1 ON Grupa1.Referent =Grupa2.Referent
 


[ Blue82 @ 02.07.2022. 18:00 ] @
I pojednostavljena verzija istog problema:

Code (sql):

DECLARE @ponudaSta TABLE  (Referent INT, Iznos INT)
INSERT INTO @ponudaSta SELECT 1, 1000
INSERT INTO @ponudaSta SELECT 2, 2000
INSERT INTO @ponudaSta SELECT 3, 3000
INSERT INTO @ponudaSta SELECT 4, 4000
INSERT INTO @ponudaSta SELECT 1, 1000
INSERT INTO @ponudaSta SELECT 2, 2000
INSERT INTO @ponudaSta SELECT 3, 3000
INSERT INTO @ponudaSta SELECT 4, 4000

DECLARE @PonudaZag TABLE  (RbrPonude INT, Referent INT)
INSERT INTO @PonudaZag SELECT 1, 1
INSERT INTO @PonudaZag SELECT 2, 2
INSERT INTO @PonudaZag SELECT 3, 3
INSERT INTO @PonudaZag SELECT 4, 4
INSERT INTO @PonudaZag SELECT 5, 1
INSERT INTO @PonudaZag SELECT 6, 2
INSERT INTO @PonudaZag SELECT 7, 3
INSERT INTO @PonudaZag SELECT 8, 4

WITH Grupa1 AS (
     SELECT
          Referent,
          SUM (Iznos) AS VrednostPonuda
     FROM ponudaSta
     GROUP BY ROLLUP (Referent)    
),
Grupa2 AS (
     SELECT Referent, COUNT(RbrPonude) AS BrojSvihPonuda
     FROM PonudaZag
     GROUP BY Referent
)
SELECT Grupa1.Referent, Grupa1.VrednostPonuda, Grupa2.BrojSvihPonuda
FROM Grupa2
JOIN Grupa1 ON Grupa1.Referent =Grupa2.Referent