mysql - How to get single result after grouping? -

i have table this

type      amount    purc    100 sale    120 sale    80 purc    150 sale    50 sale    70 sale    120 purc    60

when run query "select type, sum(amount) amount mytable group type" can 2 result set like

type     amount purc    310 sale    440

but want single result in 1 query sale , purc like

sale   purc   profit 440    310    130

what single query?

try this:

select   sum(if(`type` = 'purc', `amount`, 0)) `purc`,   sum(if(`type` = 'sale', `amount`, 0)) `sale` `mytable`

this should perform so-called pivoting on table.

using case instead of if:

select   sum(case `type` when 'purc' `amount` else 0 end) `purc`,   sum(case `type` when 'sale' `amount` else 0 end) `sale` `mytable`

EXIT