[es] - Kako da dobijem sumu druge i trece kolone na dnu?

Blue82
dipl. ecc.

Član broj: 165981
Poruke: 838
*.static.isp.telekom.rs.

+322 Profil

Kako da dobijem sumu druge i trece kolone na dnu?

^{02.07.2022. u 12:54 - pre 26 meseci}

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

Prikačeni fajlovi

Tabela1.jpg - 29.31k

Odgovor na temu

Blue82
dipl. ecc.

Član broj: 165981
Poruke: 838
*.dynamic.sbb.rs.

+322 Profil

Re: Kako da dobijem sumu druge i trece kolone na dnu?

^{02.07.2022. u 18:00 - pre 26 meseci}

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

Odgovor na temu