i have three tables meals,expenses,users! i want to calculate users meal details for a month! i have tried in many ways i stucked :(
i have tried using group by user.id but it return all user total cost same as total expense! calculation output fully unexpected.
SELECT u.name ,
((SUM(tot_expenseamount)/ SUM(tot_noofmeal))*tot_noofmeal) as total_Cost,
IFNULL(t.tot_expenseamount,0) AS tot_expense_amount ,
IFNULL(n.tot_noofmeal,0) AS tot_no_of_meal
FROM users u
LEFT JOIN (
SELECT e.user_id , SUM(e.expenseamount) AS tot_expenseamount
FROM expenses e
WHERE e.expensedate >= '2019-04-01' + INTERVAL 0 MONTH AND e.expensedate < '2019-04-01' + INTERVAL 1 MONTH
GROUP BY e.user_id ) t ON t.user_id = u.id
LEFT JOIN (
SELECT m.user_id , SUM(m.noofmeal) AS tot_noofmeal
FROM meals m
WHERE m.mealdate >= '2019-04-01' + INTERVAL 0 MONTH AND m.mealdate < '2019-04-01' + INTERVAL 1 MONTH
GROUP BY m.user_id ) n ON n.user_id = u.id
GROUPBY u.name
ORDER BY u.name
i have expected total cost for user!! total cost= meal rate* user total meal; meal rate = sum of total expense / sum of all user total meal
example : i have three user x,y,z their april month total meal is 10,20,30 and expense is 2000,1000,3000
now total expense= 6000(2000+1000+3000) total meal = 60(10+20+30) meal rate = (6000/60) = 100.00
per user total cost will be let for x, total cost= 10*100=1000/=
meal table

user table

expenses table

expected output

Aucun commentaire:
Enregistrer un commentaire