Let's say I have this table:
| sellerid | buyerid | price |
|---|---|---|
| abc | xyz | 100.00 |
| abc | xyz | 500.00 |
| ikj | abc | 200.00 |
| ikj | abc | 300.00 |
And I need to get the sum of price for each id (sellerid and buyerid):
| id | total |
|---|---|
| abc | 11,000.00 |
| xyz | 600.00 |
| ikj | 500.00 |
I'm struggling with the group by part, I can get it to give me the total but then the id is null.
Any help would be appreciated, thanks.
One method would be using union all to combine sellerid and buyerid columns , then using an outer query we can do the aggregation.
With the given data example
create table tbl (
sellerid text ,
buyerid text ,
price decimal(5,2)
);
insert into tbl values ('abc','xyz',100.00),
('abc','xyz',500.00), ('ikj','abc',200.00),('ikj','abc',300.00);
Query
select sellerid,sum(price) as tot_Price
from (
select sellerid,price from tbl
union all
select buyerid,price from tbl
) x
group by sellerid
order by sellerid asc
Result
sellerid tot_price
abc 1100.00
ikj 500.00
xyz 600.00
