PHP & MySQL : SUM Value BY DISTINCT Category

Question

thank you before for take an interest into my case. In short i want to tell that i wonder if Value in MySQL table can be counted using by just MySQL query or need PHP code to count. And i have no idea how to code it.

Case :

I have tbl_transaction which has format like this :

id | ref | description | value | date | category | sub_category

Value is an INT type column, for example it contains 10000, 20000, etc..

Let's say that i want to SUM value column, but by DISTINCT Category

So for example, it's going to work like this :

category A SUM minus(-) category B SUM plus(+) category C SUM = Result of Counted SUM

Experiment :

I tried something like this, obviously wont work but i try to show my logic for solve a case here.

SELECT SUM(value) as MAIN_CAT_TOTAL_VAL 
  FROM tbl_transaction 
 WHERE monthTransaction LIKE '$newmonthminusone%' ONE
     , ONE+3

Desired Learning :

I want to know how mySQL query can be printed into PHP. And code behind that if possible, i actually need the code. Some say that mysql_ function isnt really up-to-date anymore and shouldn't be used. But i have no bright knowledge on how to use new function that replace mysql_ function, like maybe PDO and some kind like that. I really need explanation. I learn from code and case, that's how i adapt at least. So code would be help me much.

Desired Output :

Number / value, result of SUMMED UP table calculation (math). 

I have no idea at all, how to code and start. Been stuck for hours. I sincerely need help for this. Ty so much.

Answer 1

To sum the values from one category

SELECT category, SUM(value) FROM tbl_transaction GROUP BY category

I hope you have enough knowledge to expand this query to include more fields or filter out, what you don't want included in the sum.

Answer 2

mysqli_* is pretty much a drop-in replacement for mysql_*, and is a good transition to learning PDO, as you can use your (mostly) unmodified existing code, and transition toward the object-oriented approach and prepared statements as you learn about them.

That said, what you are probably looking for in a query is something like:

SELECT
    category,
    SUM(value) AS MAIN_CAT_TOTAL_VAL
FROM tbl_transaction
WHERE monthTransaction LIKE '$newmonthminusone%' AS ONE
GROUP BY category;

Then you'll be able to work with it like so:

$link = mysqli_connect("localhost", "dbuser", "dbpassword", "dbname");
$query = mysqli_query($link, $queryStringFromAbove);
$total = 0;
while($row = mysqli_fetch_assoc($query)) {
    switch($row['category']) {
        case "A":
        case "C":
            $total += $row['MAIN_CAT_TOTAL_VAL'];
            break;
        case "B":
            $total -= $row['MAIN_CAT_TOTAL_VAL'];
            break;
    }
}
echo $total;

Edit: It's difficult to tell precisely your conditions of adding or subtracting, but the switch() block should be flexible enough to customize to your needs.

Answer 3

@SuperJer has the solution for mysqli. If you want the mysql solution:

// Create connection to your server
$handle=mysql_connect('your_server_name','your_server_password');

// Check that connection was successful
if($handle == FALSE)
   die("No connection available: ".mysql_error());

// Select your database
$db=mysql_select_db('your_database_name');
if($db == FALSE)
   die("Unable to select DB: ".mysql_error());

// Formulate query
$query = SELECT category, SUM(value) AS categoryTotal FROM tbl_transaction GROUP BY category;

// Run query
$result = mysql_query($query, $handle);

// Store result
$total = 0;    
while($row=mysql_fetch_assoc($result)){
    switch($row['category']) {
    case "A":
    case "C":
        $total += $row['MAIN_CAT_TOTAL_VAL'];
        break;
    case "B":
        $total -= $row['MAIN_CAT_TOTAL_VAL'];
        break;
    }
}

I hope this helps