Question 89167 in PostgreSQL Question posted in
The official documentation can be found here.

Query to return sum for multiple columns as separate rows – Postgresql

I’m trying to generate the following output:

type totals
CostA 1500
CostB 200
CostC 300

From a table with this structure:

CostA
CostB
CostC
name
date
-- more columns ...

The table has more columns but I want the sum for only 3 of them.

I need this structure specifically because superset expects a structure like this to generate some charts.

Any idea?

Tags:

3

Answers


  1. 0

    as you can see, the table has more columns but I want the sum of only
    3 columns.

    You can do it using union all :

    SELECT 'CostA' AS type, SUM(CostA) AS totals FROM table_name
UNION ALL
SELECT 'CostB' AS type, SUM(CostB) AS totals FROM table_name
UNION ALL
SELECT 'CostC' AS type, SUM(CostC) AS totals FROM table_name;
    Login or Signup to reply.
  2. 0

    You can do it using unnest() as follows :

    with cte as (
  select sum(CostA) as CostA, sum(CostB) as CostB, sum(CostC) as CostC
  from mytable
)
SELECT
   unnest(array['CostA', 'CostB', 'CostC']) AS type,
   unnest(array[CostA, CostB, CostC]) AS total
FROM cte
ORDER BY total
    Login or Signup to reply.
  3. 0

    If the source table is big, it pays to compute all sums in a single SELECT (close to 1/3 the cost), and then pivot results – with a VALUES expression in a LATERAL join:

    SELECT pivot.*
FROM  (
   SELECT sum(CostA) AS a
        , sum(CostB) AS b
        , sum(CostC) AS c
   FROM   tbl
   ) sub
CROSS JOIN LATERAL (
   VALUES
     ('CostA', a)
   , ('CostB', b)
   , ('CostC', c)
   ) pivot(type, total);

    See:

    Aside: avoid CaMeL-case identifiers in Postgres if at all possible. See:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search