I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables:
transaction:
idtransaction (pk)
user_iduser (fk)
item_iditem (fk)
total_price (bigint)
time(datetime)
transaction_type (enum: 'buy', 'sell')
profit:
idprofit (pk)
transaction_idtransaction_bought (fk)
transaction_idtransaction_sold (fk)
quantity_profit (int)
profit_unit (bigint)
calendar:
days (date)
Calendar table is a simple table created from a stored procedure. It consists on every date between 2000-2050. Profits are already calculated from the application side, the profits table simply stores the results. Note that different users MAY participate in the same 'profit', (e.x user 200 buys an item and user 201 sells it. Again this is handled by the application side). In this case you'd increment the value of sales and profit to user 201 and the value of purchases to user 200.
I'm trying to create a view that outputs this (a snapshot of sums for each day for each user)
date userid total_bought total_sold total_profit profit_margin
01-01-2016 200 150000 2000000 50000 0.20
02-01-2016 200 512 14515 456 0.05
03-01-2016 200 150000 1000000 5000 0.14
04-01-2016 200 512 12515 4556 0.07
and so on (do the same for every user and date, so in total the number of rows should be users x dates)
However my implementation is outputting wrong data.
create view v_history_details as
select calendar.days as hday,
users.iduser as user_id,
sum(t1.price_total) as total_buy
sum(profit.profit_unit*profit.quantity_profit) as total_profit,
(sum(total_profit)/sum(total_buy)) * 100 AS margin
from
calendar
JOIN transaction t1 ON date(t1.time) = calendar.days
JOIN transaction t2 ON date(t2.time) = calendar.days
JOIN users on t1.iduser = users.iduser
where t1.transaction_type = 'Buy'
and t2.transaction_type = 'Sell'
GROUP BY calendar.days
order by calendar.days ASC
The issue comes from joining with the transaction table twice. I need a way to split the purchases and sales data but at the same time I also need them in the same view. I'm kind of new to all this
I'd really appreciate some guidance on how to structure this view here.