php calculate table row

Question

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);

What is your question? Is it "will this work?" If so, does it? — MJB
– MJB, Commented Apr 6, 2011 at 17:22

Wrikken · Accepted Answer · 2011-04-06 18:08:02Z

1

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

answered Apr 6, 2011 at 18:08

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php calculate table row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related