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

Postgresql – How to generate/create a same row from the same table with one column condition changed

My data looks like this

date category subcategory price
20230510 A LMN 10
20230510 A PQR 20
20230511 A LMN 50
20230511 A PQR 10

In my table I am getting subcategory LMN and PQR along with price, I need to introduce row ALL with the addition of the price for LMN and PQR subcategory using optimized query

My expected output is like this

date category subcategory price
20230510 A LMN 10
20230510 A PQR 20
20230510 A ALL 30
20230511 A LMN 50
20230511 A PQR 10
20230511 A ALL 60

I tried using UNION ALL to generate a row to ALL and the addition of the price at category level and displaying but is there anyway that I can do this same thing without using UNION ALL or SELF JOIN so reduce table scans

Query I tried –

select date,
category,
'ALL' as subcategory
sum(price)
from table
group by date,category
UNION ALL
select date,
category,
subcategory,
price
from table;
Tags:

2

Answers


  1. 0

    Use the conditional aggregation to get total price of LMN and PQR only :

    select *
from mytable
WHERE subcategory in ('LMN','PQR')
UNION ALL
select date, category, 'ALL' as subcategory, sum(case when subcategory in ('LMN','PQR') then price else 0 end)
from mytable
group by date, category
order by date

    If not interested by filtering your data by subcategory, then :

    select *
from mytable
UNION ALL
select date, category, 'ALL' as subcategory, sum(price)
from mytable
group by date, category
order by date

    Demo here

    Login or Signup to reply.
  2. 0

    You could use rollup:

    with mytable as (select * from (values
                      ('20230510', 'A', 'LMN', 10),
                      ('20230510', 'A', 'PQR', 20),
                      ('20230511', 'A', 'LMN', 50),
                      ('20230511', 'A', 'PQR', 10)) x(date, category,   subcategory,    price))

select date, category, subcategory, sum(price) as price
from mytable
group by  rollup (date, category, subcategory)
order by date,category, subcategory;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search