PHP & MySQL : Show SUM of Something, Classified By Distinct Category

Question

Could you help me out ? If you can provide answer with an explanation i will be more thankful because i want to learn more, i already thankful for this community actually. Helps me a lot with learning. But only answer is good enough for me since i really need the code for this.

Ok, now I will define the problem.

Now, lets say i have table with these column. tbl_transaction -> id | ref | description | value | category | sub-category

value is an integer column type, with some amount of value. like 100000 or 200000 for example.

category and subcategory is VARCHAR type column which contain category and subcategory.

my desired output is it will show SUM of value based on its category. for that logic i tried this query (its not a valid mySQL code standard, just want to show a logic).

X = select DISTINCT category from tbl_transaction;
select SUM(value) from tbl_transaction WHERE category='X';

its actually work well on MySQL. but i want to print this result to PHP too. the problem is i cant print this value along with SUM and DISTINCT category. i want something like (in PHP output / echo) :

Category | SUM of Category

A | 100000

B | 400000

C | 500000

i tried these code on php, and its only print the category well, but not the SUM of category.

$query1=mysql_query("select DISTINCT category from tbl_transaction");
echo "<table><tr><td>Category</td>";
while($query2=mysql_fetch_array($query1))
{
echo "<tr><td>".$query2['category']."</td></tr></table>";

$query3=mysql_query("select SUM(value) from tbl_transaction WHERE category ="(select DISTINCT category from tbl_transaction)"");
echo "<table><tr><td>SUM of Category</td>";
while($query4=mysql_fetch_array($query3))
{
echo "<tr><td>".$query4['value']."</td></tr></table>";
}

}

i tried several time, it results is null / resource id #7 / error. far more from this, i actually want something like this.

Category A

Sub Category A - Total Amount of Sub Category A

Sub Category B - Total Amount of Sub Category B

Sub Category C - Total Amount of Sub Category C

Total - Total Amount of Category A

Category B

Sub Category A - Total Amount of Sub Category A

Sub Category B - Total Amount of Sub Category B

Sub Category C - Total Amount of Sub Category C

Total - Total Amount of Category B

Thank you in advance, Stackoverflow community.

Answer 1

You can try a sql statement like this to SUM the values GROUPing by a column:

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

And you can use this in PHP like this: (I'll type with PDO because mysql_ commands are getting old right now and not recommended)

//Creating connection
$connection=new PDO("mysql:host=127.0.0.1;dbname=database_name","root","");

//Creating main query, fetching main categories
$query=$connection->prepare("SELECT category, SUM(value) as MAIN_CAT_TOTAL_VAL FROM tbl_transaction GROUP BY category");
$query->execute(); //executing query

foreach($query->fetchAll() as $q) //fetching results
{
    echo "<b>".$q["category"].":<br/></b>"; //printing main category name

    //creating second query which sums value column and groups by sub category
    $query2=$connection->prepare("SELECT SUM(value) as TOTAL_VALUE, sub-category FROM tbl_transaction WHERE category=:p1 GROUP BY sub-category");   
    $query2->bindParam(":p1",$q["category"],PDO::PARAM_STR); //binding parameter to second query
    $query2->execute(); //executing second query
    foreach($query2->fetchAll() as $q2) //fetching the results
    {
        echo $q2["sub-category"]." -> ".$q2["TOTAL_VALUE"]."<br/>"; //printing the results
    }

    echo "total amount of category ".$q["category"]." = ".$q["MAIN_CAT_TOTAL_VAL"];
    echo "<hr/>"; //cosmetics
}

This should work if you edit it properly. It can look a little complicated if you're not sure how to use PDO, I suggest you to take a look at it.