sql - How to combine two results from same table -

sql - How to combine two results from same table -

i'm looking way of pulling totals transactions table. sales , homecoming transactions differentiated column, value stored positive.

i've managed pull different transaction type totals, grouped product, separate rows:

select `type`, `product`, sum(`final_price`) `value`, count(`final_price`) `count` grouping `product`, `type`

the result is:

type | product | value | count s | 1 | 1000 | 2 s | 4 | 750 | 3 s | 2 | 300 | 2 s | 3 | 10 | 1 r | 1 | 500 | 1

ideally, i'd have totals displayed on single row in additional columns different columns ordering purposes. ideal result be:

type | product | s_value | s_count | r_value | r_count s | 1 | 1000 | 2 | 500 | 1 s | 4 | 750 | 3 | 0 | 0 s | 2 | 300 | 2 | 0 | 0 s | 3 | 10 | 1 | 0 | 0

i've tried union all , left joins no joys far. help appreciated.

you can utilize case expressions differentiate type of transaction:

select `product`, sum(case `type` when 's' `final_price` end) `s_value`, count(case `type` when 's' `final_price` end) `s_count`, sum(case `type` when 'r' `final_price` end) `r_value`, count(case `type` when 'r' `final_price` end) `r_count` grouping `product`, `type`

edit: forward-quotes around column names i'm assuming mysql questions though it's not explicitly tagged such. if case, can simplify count statements utilizing mysql's automatic conversion boolean int takes true 1 , false 0:

select `product`, sum(case `type` when 's' `final_price` end) `s_value`, sum(`type` = 's') `s_count`, sum(case `type` when 'r' `final_price` end) `r_value`, sum(`type` = 'r') `r_count` grouping `product`, `type`

sql select group