7
I have two similar tables tracking buy/sell transactions. I had to use two tables due to a restriction of the top level table structure and specific information on buyers or sellers.
Essential information that I would like to combine from the two tables
Table A (buyer)
purchase_date
purchase_price
transaction_type
Table B (seller)
sale_date
sale_price
transaction_type
The query used to get the info out:
select year(purchase_date) as year, purchase_price as price, transactiuon_type as type from tableA
Once I have added query from tableB either by a union or inserting into a temp table, I would like to do a final query - by year by type
select year, type, count(*), sum(price), avg(price), max(price), min(price) from (the union or the temp table) group by year,type
Right now, I do not even do a temp table - I have created a perm table holding information and do queries manually, as I am still trying to figure out how to output the group-by results in a compact table using smarty.