lundi 8 avril 2019

it only returns one row. and if i use group by it gives me wrong output

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

meal table

user table

user table

expenses table

expenses table

expected output

expected output




Aucun commentaire:

Enregistrer un commentaire