Query to return sum for multiple columns as separate rows

Question

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?

Answer 1

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;

Answer 2

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

Answer 3

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:

• Transpose single row with multiple columns into multiple rows of two columns

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

• Are PostgreSQL column names case-sensitive?