CODEWITHSUNDEEP
postgresql
Miguel Ângelo Belém
Miguel Ângelo Belém

Reputation: 113

PostgreSQL- Adding "TOTAL" row with more than one Group by columns

I was following this topic:

PostgreSQL - making first row show as total of other rows

..and i used this query to accomplish something similar in my code:

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

It works fine but i now need to put two more columns before the sum columns simliar to the fruit one and i'm getting an error :

"... must appear in the GROUP BY clause or be used in an aggregate function"

Any help on how to do like the example above but with to more columns like "fruit"?

(Sorry my rep didn't let me continue the previous topic)

Upvotes: 0

Views: 2869

Answers (2)

Miguel Ângelo Belém
Miguel Ângelo Belém

Reputation: 113

It was an easier fix than i thought.

with w as ( select fruits, vegetables, cereals, sum(a) a, sum(b) b, sum(c) c
            from basket
            group by fruits, vegetables, cereals )

select * from w union all select 'total', null, null, sum(a), sum(b), sum(c) from w

Two nulls in the last select solved the problem

Upvotes: 3

Hans-Jürgen Schönig
Hans-Jürgen Schönig

Reputation: 741

what you need is something called "Grouping sets". The magic word here is ROLLUP, which is currently not yet supported by PostgreSQL. I guess it will be in 9.5. For now you have to continue on the path you have chosen (= subselects, CTEs, etc.). a guy called atri shama is currently working on the stuff you really want. sorry for the bad news for now.

Upvotes: 0

Related Questions