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

Question

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;

Answer 1

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

Answer 2

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;