Indra Indra - 1 month ago 8
MySQL Question

PHP Mysql pivot table

I am using the Following code to return a purchase details.That displays the result like this

Name Qty Value
Product1 30 1000
Product1 45 2000
Product2 20 1500
Product2 25 3000


How can sum up the pdt's quantity and value And display like this

Name Qty Value
Product1 75 3000
Product2 45 4500
Total 120 7500


With Select Where clause.

$result = mysql_query("SELECT pdtname,pack,qty,value FROM ist WHERE chemistcode='3121' AND chemistcode='3020' AND companycode = $companycode");

echo "<table id=customers>
<tr>
<th>Product</th>
<th>Pack</th>
<th>Qunatity</th>
<th>Value</th>
</tr>";


while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo"<td>" . $row['pdtname'] . "</td>";
echo"<td>" . $row['pack'] . "</td>";
echo"<td>" . $row['qty'] . "</td>";
echo"<td>" . $row['value'] . "</td>";
echo "</tr>";
}
echo "</table>";

Answer

You can easily get the result by using the aggregate function sum() and by using group by with rollup:

select 
  coalesce(name, 'Total') Name, 
  sum(qty) qty,
  sum(value) value
from ist
group by name with rollup;

See SQL Fiddle with Demo. This gives the result:

|     NAME | QTY | VALUE |
| Product1 |  75 |  3000 |
| Product2 |  45 |  4500 |
|    Total | 120 |  7500 |