calculate sum of columns in mysql using boolean php

Question

I have a table named expenses and it includes these columns:

ID, amount_1, amount_2, amount_1_paid, amount_2_paid, total

amount_x_paid is a TINYINT so it's used for checkboxes.

What I want to do is to calculate the total based on amount_x_paid value (1,0)

So let's say:

amount_1 = 100
amount_2 = 200
amount_1_paid = 1
amount_2_paid = 0

This should calculate

 total = 100

How can I achieve this in php?

The php file will be a scheduled task job that will run one time a day to calculate the totals.

this can do the trick:

SELECT
 FORMAT(
   SUM(
     CASE
       WHEN amount_1_paid = 0 THEN (total + amount_1)
 ELSE (total - amount_1)
       END
    ),
    0
  )
AS total
  FROM
    expenses  
    WHERE ID = '1';

how i can put this in a loop and update all records?

Please explain what you tried so far, what didn't work and where your problems are in solving this problem. — Till Helge
– Till Helge, Commented Apr 17, 2013 at 11:21
if both amount_X_paid will be 1 then you want total of amount_X. Right? — Bhavik Shah
– Bhavik Shah, Commented Apr 17, 2013 at 11:24
i was wondering if amount_paid fields could be both empty (0) or both paid(1)? — Fabio
– Fabio, Commented Apr 17, 2013 at 11:28

Mahmoud Gamal · Accepted Answer · 2013-04-17 11:23:08Z

3

To select the amount_ field depending on the amount_x_paid column, you can use the CASE expression. Something like this:

SELECT
  CASE 
    WHEN amount_1_paid = 1 THEN amount_1 
    WHEN amount_2_paid = 1 THEN amount_2  
    ELSE 0
  END AS Amount,
  ...
FROM tablename;

IF you are looking for the sum of these amounts, use it in a subquery:

SELECT 
  SUM(Amount) AS Total,
  ...
FROM
(
    SELECT
      CASE 
        WHEN amount_1_paid = 1 THEN amount_1 
        WHEN amount_2_paid = 1 THEN amount_2  
        ELSE 0
      END AS Amount,
      ...
    FROM tablename
) AS sub;

answered Apr 17, 2013 at 11:23

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

1 Comment

Mahmoud Gamal Over a year ago

@StathisNtonasII - It should work fine, can you please edit your question and show me the exact query you tried, and if possible some sample data and the desired output? It will be very helpful to spot the problem thanks.

MaPePeR · Accepted Answer · 2013-04-17 11:51:45Z

2

In your php file you execute a mysql-query:

SELECT SUM(amount_1*amount_1_paid + amount_2*amount_2_paid) AS "total"
FROM expenses;

Update: When you want to have the total columnwise, you might consider not saving it, as the total totally depends on amount_1_paid, amount_2_paid, amound_1 and amound_2.

My Update-Query (No loop needed, does update the whole table)

UPDATE expenses SET total=amount_1*amount_1_paid + amount_2*amount_2_paid;

edited Apr 17, 2013 at 11:51

answered Apr 17, 2013 at 11:26

MaPePeR

98910 silver badges21 bronze badges

1 Comment

Stathis Ntonas Over a year ago

thanks for the ultra fast replies, i ve edited my question before i see your replies, is the sql query correct? how i can loop through the query in order to update all rows?

Stathis Ntonas · Accepted Answer · 2013-05-02 07:57:02Z

this code did the trick:

            <?php

            include('dbcon.php');

            $query = "SELECT recordno,poso10,poso11,poso12,poso13,poso14,poso15,poso16,poso17,poso18,poso19,poso20,poso21,poso30,poso31,poso32,poso33,poso34,poso41,poso42,poso50,poso51,poso52,poso53,poso60,poso61,poso62,poso63,poso70,poso71,poso10_check,poso11_check,poso12_check,poso13_check,poso14_check,poso15_check,poso16_check,poso17_check,poso18_check,
            poso19_check,poso20_check,poso21_check,poso30_check,poso31_check,poso32_check,poso33_check,poso34_check,poso40_check,
            poso41_check,poso42_check,poso50_check,poso51_check,poso52_check,poso53_check,poso60_check,poso61_check,poso62_check,
            poso63_check,poso70_check,poso71_check,synolo40_42_At_Pa_Bo,
            (if(isnull(poso10),0,poso10) + 
            if(isnull(poso11),0,poso11) + 
            if(isnull(poso12),0,poso12) +
            if(isnull(poso13),0,poso13) + 
            if(isnull(poso14),0,poso14) + 
            if(isnull(poso15),0,poso15) + 
            if(isnull(poso16),0,poso16) + 
            if(isnull(poso17),0,poso17) + 
            if(isnull(poso18),0,poso18) + 
            if(isnull(poso19),0,poso19) + 
            if(isnull(poso20),0,poso20) + 
            if(isnull(poso21),0,poso21) + 
            if(isnull(poso30),0,poso30) + 
            if(isnull(poso31),0,poso31) + 
            if(isnull(poso32),0,poso32) + 
            if(isnull(poso33),0,poso33) + 
            if(isnull(poso34),0,poso34) +  
            if(isnull(poso41),0,poso41) + 
            if(isnull(poso42),0,poso42) + 
            if(isnull(poso50),0,poso50) + 
            if(isnull(poso51),0,poso51) + 
            if(isnull(poso52),0,poso52) + 
            if(isnull(poso53),0,poso53) + 
            if(isnull(poso60),0,poso60) + 
            if(isnull(poso61),0,poso61) + 
            if(isnull(poso62),0,poso62) + 
            if(isnull(poso63),0,poso63) + 
            if(isnull(poso70),0,poso70) + 
            if(isnull(poso71),0,poso71) +
            if(isnull(synolo40_42_At_Pa_Bo),0,synolo40_42_At_Pa_Bo)
            )as synolo

            FROM
            (SELECT
            recordno,poso10_check,poso11_check,poso12_check,poso13_check,poso14_check,poso15_check,poso16_check,poso17_check,poso18_check,
            poso19_check,poso20_check,poso21_check,poso30_check,poso31_check,poso32_check,poso33_check,poso34_check,poso40_check,
            poso41_check,poso42_check,poso50_check,poso51_check,poso52_check,poso53_check,poso60_check,poso61_check,poso62_check,
            poso63_check,poso70_check,poso71_check,
            (CASE WHEN poso10_check = '1' THEN poso10 END) AS poso10,
            (CASE WHEN poso11_check = '1' THEN poso11 END) AS poso11,
            (CASE WHEN poso12_check = '1' THEN poso12 END) AS poso12,
            (CASE WHEN poso13_check = '1' THEN poso13 END) AS poso13,
            (CASE WHEN poso14_check = '1' THEN poso14 END) AS poso14,
            (CASE WHEN poso15_check = '1' THEN poso15 END) AS poso15,
            (CASE WHEN poso16_check = '1' THEN poso16 END) AS poso16,
            (CASE WHEN poso17_check = '1' THEN poso17 END) AS poso17,
            (CASE WHEN poso18_check = '1' THEN poso18 END) AS poso18,
            (CASE WHEN poso19_check = '1' THEN poso19 END) AS poso19,
            (CASE WHEN poso20_check = '1' THEN poso20 END) AS poso20,
            (CASE WHEN poso21_check = '1' THEN poso21 END) AS poso21,
            (CASE WHEN poso30_check = '1' THEN poso30 END) AS poso30,
            (CASE WHEN poso31_check = '1' THEN poso31 END) AS poso31,
            (CASE WHEN poso32_check = '1' THEN poso32 END) AS poso32,
            (CASE WHEN poso33_check = '1' THEN poso33 END) AS poso33,
            (CASE WHEN poso34_check = '1' THEN poso34 END) AS poso34,
            (CASE WHEN poso40_check = '1' THEN synolo40_42_At_Pa_Bo END) AS synolo40_42_At_Pa_Bo,
            (CASE WHEN poso41_check = '1' THEN poso41 END) AS poso41,
            (CASE WHEN poso42_check = '1' THEN poso42 END) AS poso42,
            (CASE WHEN poso50_check = '1' THEN poso50 END) AS poso50,
            (CASE WHEN poso51_check = '1' THEN poso51 END) AS poso51,
            (CASE WHEN poso52_check = '1' THEN poso52 END) AS poso52,
            (CASE WHEN poso53_check = '1' THEN poso53 END) AS poso53,
            (CASE WHEN poso60_check = '1' THEN poso60 END) AS poso60,
            (CASE WHEN poso61_check = '1' THEN poso61 END) AS poso61,
            (CASE WHEN poso62_check = '1' THEN poso62 END) AS poso62,
            (CASE WHEN poso63_check = '1' THEN poso63 END) AS poso63,
            (CASE WHEN poso70_check = '1' THEN poso70 END) AS poso70,
            (CASE WHEN poso71_check = '1' THEN poso71 END) AS poso71

            FROM 010_08_exoda)a;";

            $synolo = mysql_query($query);
            $count=mysql_num_rows($synolo);

            while($row = mysql_fetch_assoc($synolo))
            {
                    $recordno = $row['recordno'];
                    $synolo2 = $row['synolo'];

            mysql_query("UPDATE 010_08_exoda SET synolo_paid='$synolo2' where recordno='$recordno'");  

                }
            ?>

Collectives™ on Stack Overflow

calculate sum of columns in mysql using boolean php

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related