CODEWITHSUNDEEP
phpmysql
user1879786
user1879786

Reputation: 31

Display result in group of rows and sum each groups value

I do not have much experience with PHP/MySQL. I have a table with 3 fields: id, particular, and amount as shown below.

id   particular   amount
1    Fees         5000
2    Fees         3000
3    Bill         9000
4    Fees         4000
5    Bill         3000
6    Expense      2000
7    Fees         1000

I want to show the data in groups as well as the sum of each individual group, like so:

particular   amount
Fees         5000
Fees         3000
Fees         4000
Fees         1000
**Total        13000**
Bill         9000
Bill         3000
**Total        12000**
Expense      2000
**Total        2000**

i try this query SELECT SUM(amount), particular FROM sale_expense GROUP BY particular and it added all the values, but it is not showing in the group as I need to display the data

Upvotes: 3

Views: 2461

Answers (4)

Barmar
Barmar

Reputation: 780869

SELECT particular, id, sum(amount) amount
FROM TableName
GROUP BY particular, id WITH ROLLUP

fiddle

Upvotes: 3

TCFDS
TCFDS

Reputation: 622

Try this:

( SELECT 
    particular, 
    amount  
  FROM sale_expense )
UNION
( SELECT 
    CONCAT(particular," - total") AS particular, 
    SUM(amount) AS amount 
  FROM sale_expense
  GROUP BY particular )
ORDER BY particular ASC

Upvotes: 0

mohamed elbou
mohamed elbou

Reputation: 1857

try this sql:

SELECT particular, SUM(amount) AS total  FROM tablename GROUP BY particular

This should give you the total of each group.
UPDATE: try this

$sql = "SELECT particular, SUM(amount) AS total  FROM tablename GROUP BY particular";
$result = mysql_query($sql);
while($row = mysql_fetch_array( $result )){
    $sql2 = "SELECT * FROM tablename WHERE particular = '".$row['particular']."'";
    $result2 = mysql_query($sql2);
    while($row2 = mysql_fetch_array( $result2 )){
        echo $row2['particular']. "  ".$row['amount'];
        echo "\n";
    }
    echo "Totol: ".$row['total']."";
}

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can union the queries together:

select particular, amount
from ((select id, particular, amount
       from sales_expense se
      ) union all
      (select 99999 as id, concat('Total: ', particular), sum(amount) as amount
       from sales_expense se
       group by particular
      )
     ) t
group by particular
order by id

Upvotes: 0

Related Questions