CODEWITHSUNDEEP
phpmysql
tonoslfx
tonoslfx

Reputation: 3442

php calculate table row

hey guys, ive got one big database table report.
The way i want, split the calculation from the amount to 40% and 60%, except for the (pro_tan) i want them to be 100% No split.

SHORT REPORT table:
--------------
id  t_deep  t_tanning   amount   cashier
----------------------------------------- 
1   deep    pro tan     30        1
2   facial  tanning     25        2
3   deep    pro tan     30        1
4   deep    tanning     25        1 

report html result:

table id   cashier(40%)  owns(60%)   total
1          12            18          30   
2          10            15          25
3          12            18          30
4          10            15          25

EXPECTING RESULTS:

table id   cashier       owns        total
1           0            30          30   
2          10            15          25
3           0            30          30
4          10            15          25

php calculation to split 40% and 60%:

$q = $db->query("SELECT cashier, SUM(r_amount) AS amount, SUM(r_amount*0.60) AS totalSixty, SUM(r_amount*0.40) AS totalFourty FROM report 
        LEFT JOIN cashier ON cashier.id_cashier = report.id_cashier 
        WHERE date='$today' GROUP BY id_cashier");
// query to search pro tan
$q1 = $db->query("SELECT cashier, SUM(r_amount) AS amount, SUM(r_amount*0.60) AS totalSixty, SUM(r_amount*0.40) AS totalFourty FROM report 
        LEFT JOIN cashier ON cashier.id_cashier = report.id_cashier 
        WHERE date='$today' AND t_tanning LIKE 'Pro Tan%' GROUP BY id_cashier");

$sixtyPercent = number_format($r['totalSixty'],2);
$fourtyPercent = number_format($r['totalFourty'],2);

Upvotes: 0

Views: 732

Answers (1)

Wrikken
Wrikken

Reputation: 70490

You can do a conditional sum like SUM(IF(t_tanning = 'pro tan',amount*0.6, amount)) to do it in 1 query.

Upvotes: 1

Related Questions