mysql group sum_MySQL sum（）在不同的分组依据上

Ok, I have a query over two tables. I need to get two sums. I do a group by so the sum() works correctly.

SELECT sum(a.x), sum(b.y) FROM a,b GROUP BY a.n where a.n=b.m

So far this works well, but the problem is i need to group them differently for the second sum (sum(b.y)), than for the first sum (sum(a.x)).

The real query is somewhat more complex but this is my main problem.

This is what i actually try to select sum(stock.amount) - if( sold.amount IS NULL , 0, sum( sold.amount ) )

How can I solve that in one query?

解决方案

since you are not writing down the tables I am gonna make a wild guess and assume the tables are like :

stock : id, item_id, amount

sold : id, item_id, amount

then again I assume that you need the stock_in_total, sold_total, left_total counts

SELECT

stock_sums.item_id,

stock_sums.st_sum as stock_in_total,

COALESCE(sold_sums.so_sum,0) as sold_total,

(stock_sums.st_sum - COALESCE(sold_sums.so_sum,0)) as left_total

FROM (

SELECT stock.item_id as item_id, SUM(stock.amount) as st_sum

FROM stock

GROUP BY item_id

) as stock_sums

LEFT JOIN (

SELECT sold.item_id as item_id, SUM(sold.amount) as so_sum

FROM sold

GROUP by item_id

) as sold_sums ON stock_sums.item_id = sold_sums.item_id

I hope this would help.