CODEWITHSUNDEEP
sqlpostgresqlsumrow
Radovan Kočík
Radovan Kočík

Reputation: 71

PostgreSQL Need to make TOTAL ROW

enter image description hereHello I'm Begginer and I need some help, I have this code in my SQL database 

SELECT round(SUM(Cena)::NUMERIC,2) AS CelkovaCena, a.Meno, a.Priezvisko,Nazov
FROM Predstavenie
JOIN Autor a ON Predstavenie.ID_Autor = a.ID_Autor
WHERE a.Meno = 'Juraj' AND a.Priezvisko = 'Klein'
GROUP BY a.Meno,a.Priezvisko,Nazov;

This is what i get after i run this SELECT.

2.2 |    Juraj   |  Klein  |    Po východe slnka
5.4 |    Juraj   |  Klein  |    Ako išiel Ferenc po hlavnej
0.5 |    Juraj   |  Klein  |    Protialkoholické hnutie Klein Ferenc PHKF

I need to make total row below, from col "cena". I need to have something like this.

2.2 |    Juraj   |  Klein  |    Po východe slnka
5.4 |    Juraj   |  Klein  |    Ako išiel Ferenc po hlavnej
0.5 |    Juraj   |  Klein  |    Protialkoholické hnutie Klein Ferenc PHKF
8.1 |                      |                          |

Rollup doesnt seem to work because of groups. I looked up on internet for various solutions, but none of them works... Thank you.

Upvotes: 2

Views: 3293

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Use GROUPING SETS:

SELECT round(SUM(Cena)::NUMERIC,2) AS CelkovaCena, a.Meno,
       a.Priezvisko, Nazov
FROM Predstavenie p JOIN
     Autor a
     ON p.ID_Autor = a.ID_Autor
WHERE a.Meno = 'Juraj' AND a.Priezvisko = 'Klein'
GROUP BY GROUPING SETS ((a.Meno, a.Priezvisko, Nazov), ());

This is like ROLLUP but it allows you to explicitly list the summary rows that you want.

Upvotes: 5

coladict
coladict

Reputation: 5095

Combining WITH and UNION can get you what you need if you put this in a subquery.

WITH subq AS (SELECT round(SUM(Cena)::NUMERIC,2) AS CelkovaCena, a.Meno, a.Priezvisko,Nazov
FROM Predstavenie
JOIN Autor a ON Predstavenie.ID_Autor = a.ID_Autor
WHERE a.Meno = 'Juraj' AND a.Priezvisko = 'Klein'
GROUP BY a.Meno,a.Priezvisko,Nazov)
SELECT * FROM subq
UNION
SELECT SUM(CelkovaCena), '', '', '' FROM subq;

This saves you having to get the results twice and slowing things down unnecessarily. Just make sure every SELECT has the same number and type of columns as the first one.

Upvotes: 1

Piotr Rogowski
Piotr Rogowski

Reputation: 3890

on the fast way

SELECT round(SUM(Cena)::NUMERIC,2) AS CelkovaCena, a.Meno, a.Priezvisko,Nazov
FROM Predstavenie
JOIN Autor a ON Predstavenie.ID_Autor = a.ID_Autor
WHERE a.Meno = 'Juraj' AND a.Priezvisko = 'Klein'
GROUP BY a.Meno,a.Priezvisko,Nazov
union all
SELECT round(SUM(Cena)::NUMERIC,2) AS CelkovaCena,null as Meno,null as Nazov
FROM Predstavenie
JOIN Autor a ON Predstavenie.ID_Autor = a.ID_Autor
WHERE a.Meno = 'Juraj' AND a.Priezvisko = 'Klein'

but better approach is create new query to calculate total

Upvotes: 0

Related Questions