CODEWITHSUNDEEP
sqlpostgresql
Andrius
Andrius

Reputation: 21118

PostgreSQL - making first row show as total of other rows

Is there any way to make first row be different then the rest, so it would show total sum of the appropriate columns?

For example:

      fruits|a|b|c
       total|3|4|6
      apples|1|2|3
     bananas|1|1|2
     oranges|1|1|1

Is it possible to make query like that or it is against the logic of sql? It would be like this (ignoring the first row for now):

SELECT fruits, sum(a), sum(b), sum(c)
FROM basket

So the first row would be different. It would show word 'total' instead of fruit name, and would show total sum of a (1+1+1=3), b (2+1+1=4) and c (3+2+1=6). Is it possible to do like that? Thanks

Upvotes: 11

Views: 11819

Answers (3)

Scott Staniewicz
Scott Staniewicz

Reputation: 742

This is now possible in version 9.5 of Postgres:

PostgreSQL 9.5 Schema

CREATE TABLE basket(fruits text, a integer, b integer, c integer);
CREATE TABLE
INSERT INTO basket(fruits, a, b, c) values('apples', 1, 1, 1),
                                      ('apples', 0, 1, 2),
                                      ('bananas', 1, 1, 2),
                                      ('oranges', 1, 1, 1);

Query

SELECT coalesce(fruits,'total'), sum(a) a, sum(b) b, sum(c) c
FROM basket
GROUP BY ROLLUP((fruits))

Results

 fruits  | a | b | c
---------+---+---+---
 apples  | 1 | 2 | 3
 bananas | 1 | 1 | 2
 oranges | 1 | 1 | 1
 total   | 3 | 4 | 6

This ROLLUP is equivalent to using an expressions with GROUPING SETS:

SELECT fruits, sum(a) a, sum(b) b, sum(c) c
FROM basket
GROUP BY GROUPING SETS (fruits, ())

Each sublist in the GROUPING SETS is interpreted the same way as though it were directly in the GROUP BY clause.

Upvotes: 14

user533832
user533832

Reputation:

You can avoid a second full scan of the table with a CTE:

PostgreSQL 9.2 Schema:

create table basket(fruits text, a integer, b integer, c integer);
insert into basket(fruits, a, b, c) values('apples', 1, 1, 1),
                                          ('apples', 0, 1, 2),
                                          ('bananas', 1, 1, 2),
                                          ('oranges', 1, 1, 1);

Query:

with w as ( select fruits, sum(a) a, sum(b) b, sum(c) c
            from basket
            group by fruits )
select * from w union all select 'total', sum(a), sum(b), sum(c) from w

Results:

|  FRUITS | A | B | C |
-----------------------
| bananas | 1 | 1 | 2 |
| oranges | 1 | 1 | 1 |
|  apples | 1 | 2 | 3 |
|   total | 3 | 4 | 6 |

SQL Fiddle here

Upvotes: 27

edze
edze

Reputation: 3005

SELECT 'total' AS fruits, sum(a), sum(b), sum(c) FROM basket
UNION ALL
SELECT fruits, sum(a), sum(b), sum(c) FROM basket GROUP BY fruits

Upvotes: 6

Related Questions