Join results of two select statements
I have a table serving as a transaction log:
Date Action Qty
11-23 ADD 1
11-23 REMOVE 2
11-23 ADD 3
I would like a query to aggregate all of ADDs and all of the REMOVEs separately for a given date.
Each of these select statements work fine, but they cannot be joined:
select date, sum(qty) as Added from table where action='Add' and date='11-23'
natural join
select date, sum(qty) as Removed from table where action='Remove' and date='11-23'
Can I store the 开发者_运维百科results of each select statement to a table and then join those? Is there a way to avoid this all together?
Thanks- Jonathan
Take a look at UNION syntax, if you really want to add the result sets of multiple queries into one:
http://dev.mysql.com/doc/refman/5.0/en/union.html
However, in this case, take a look at GROUP BY
You could group on Date, Action
therefore resulting in one record per day per action, with SUM()
being able to give you the quantity.
For example:
select
date,
action,
sum(qty) AS Quantity
from
table
group by
date, action
Will result in:
11-23 | ADD | 10
11-23 | REMOVE | 5
11-24 | ADD | 4
11-24 | REMOVE | 3
It would help if you showed what output you actually want. From your "as Added" and "as Removed" I'm guessing that you don't want a union, but maybe something like this:
select
date,
sum(if(action='ADD',qty,0)) as Added,
sum(if(action='REMOVE',qty,0)) as Removed
from `table`
where date='11-23';
(with a group by date
if you are selecting multiple dates.)
精彩评论