14
Quote:
from what i'm understanding, tableA and tableB handle totally different transactions, right?
No, they are not that different from each other. Theoretically, they should be in the same table (would save me a lots of trouble). They are stored in tableA or tableB according to top-level attributes. Essential information in tableA and tableB are the same, but each table has its own unique fields. I am trying to combine the common fields and do an arithmetic calculation afterwards.
What I really want to do is a simple union query like following, which I can't do with MySQL 4.0 (I would have to wait for MySQL 4.2)
select year, type, count(*), sum(price), avg(price), max(price), min(price)
FROM
((select year(purchase_date) as year, purchase_price as price, transaction_type as type from tableA)
union
(select year(sale_date), sale_price, transaction_type from tableB))
GROUP by year, type