CODEWITHSUNDEEP
mysql
dorogz
dorogz

Reputation: 161

mysql - query database for expenses and debts

With respect to the sample table below, and keeping in mind the following definitions,

I would like to get the following mysql query:

  1. Group by student and calculate all money spent by each student - that is, (duration x cost)

This is what I got and it works, but is incomplete!

SELECT student, SUM(ceil(cost*(end-start)/3600)) AS expenses 
FROM schedules GROUP BY student;

AND (this one does not work, but the idea is actually what I want to attain)

SELECT student, SUM(SELECT ceil(cost*(end-start)/3600) FROM schedules WHERE paid = 1) AS expenses,  SUM(SELECT ceil(cost*(end-start)/3600) FROM schedules WHERE paid = 0) AS debts FROM schedules GROUP BY student;

My BIGGEST problem is with calculating expenses from today into the past as well as debts if the date of today is greater than start and paid is still set to 0


Thank you all for your ideas!


Sample Table 

id  meta_id start       end     admin   student tutor   course  cost    paid    paydate      timestamp
18  4   1359867600  1359867690  jnc banjune cameron 2   90  1   1361521193  1359881165
19  4   1360472400  1360472490  jnc banjune cameron 2   90  1   1361521195  1359881165
20  4   1359867600  1359867690  jnc saadcore    cameron 2   90  1   1361547064  1359881165
25  6   1359914400  1359919800  jnc johndoe cameron 3   35  1   1361547080  1359893058
26  6   1360000800  1360006200  jnc johndoe cameron 3   35  0   0       1359893058
27  6   1360087200  1360092600  jnc johndoe cameron 3   35  0   0       1359893058

Upvotes: 0

Views: 461

Answers (1)

dorogz
dorogz

Reputation: 161

I got the desired solution

SELECT 
student, 
SUM(CASE WHEN paid = 1 AND FROM_UNIXTIME(start) <= now() THEN ceil(cost*(end-start)/3600) 
         ELSE 0 END) as expenses, 
SUM(CASE WHEN paid = 0 AND FROM_UNIXTIME(start) <= now() THEN ceil(cost*(end-start)/3600) 
         ELSE 0 END) as debts 
FROM schedules 
GROUP BY student;

Upvotes: 0

Related Questions