Join results of two select statements

Question

Hey all-

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

Answer 1

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

Answer 2

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.)