i have a result of say.. 6 records.. fectched from db.
each containing values
a 10
b 20
c 10
a 30
c 50
a 05

i need to get printed as
a 45
b 20
c 60

how to get the distinct results with the sum in the next column

Member Avatar for iamthwee

I would do a group by col1 and sum on col2.

E.g

SELECT
     tablename.col1,
     sum(tablename.col2)
FROM
     tablename
GROUP BY
     tablename.col1

Above solution is correct, I would like add more to it
we can add "order by tablename.col1"

i got this set of result through mysqli query and some sets of loops and if conditions. i need to filter and add the sum after getting this result..
thanks in advance :) ( pls do consider me as a newborn in this field)

Member Avatar for diafol

i got this set of result through mysqli query and some sets of loops and if conditions. i need to filter and add the sum after getting this result..

Perhaps you'd like to expand on that?

If I understand you properly, perhaps you're looking for "WITH ROLLUP"

oh may be that could help me..
wats this rollup used for??

my php code is not with me right now(now at home)
let me chk with this 'with rollup' tomorrow.
hope to get a solution..

<?php  

$con=mysqli_connect("localhost","root","","ivf");
 if(isset($_POST['submit']))

$to= $_POST['to'];
$too = date("d/m/y",strtotime($_POST['to']));

$from= date('Y-m-d');;
$aa=0;
echo " <br><h2> Procedure Reports Upto <b>'$too' </b> </h2>"; 
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";  
echo "<tr style='font-weight: bold;'>";  
echo "<td align='center'>Procedure Name</td> <td width='100' align='center'>No of Procedures</td></tr> "; 
echo "</tr>";
$query = mysqli_query($con,"SELECT COUNT(pro) FROM ivf WHERE DATE(epd) >='$from' and DATE(epd) <='$to' GROUP BY pro");
$result1 = mysqli_query($con,"SELECT * FROM ivf WHERE DATE(epd) >='$from' and DATE(epd) <='$to'GROUP BY pro");
while ($row=mysqli_fetch_array($result1))
{ 
$row1=mysqli_fetch_array($query); 
echo "<td align='center' width='200'>" . $row[2] . "</td>"; 
echo "<td align='center' width='100' >"  .$row1[0].  "</td>";
$p[]=$row1[0];
$a[]=$row[2];
$pro[]=$row1[0];
echo"</tr>";
}
echo"</table>";

echo " <br> <br><h2> STOCK REPORT </h2>"; 
$result = mysqli_query($con,"SELECT DISTINCT * FROM med 
JOIN phar
ON med.iname = phar.iname ");
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";  
echo "<tr style='font-weight: bold;'>";  
echo "<td align='center'>Medicine Name</td> 
      <td width='100' align='center'>Procedure</td> 
      <td width='100' align='center'>Stock in Hand</td>
      <td width='100' align='center'>Required Quantity</td></tr> "; 
echo "</tr>";
while($row3=mysqli_fetch_array($result))  
{
$stk[]=$row3['stock']; 
$inme[]=$row3['iname'];
$pp=$row3['pro'];
$r=count($inme);

for ($i = 0; $i <count($pro); $i++)
{
$t[$i]=$row3['iname'];
IF($a[$i] == $row3['pro'] )

{
$aa=$aa+1;
$t[$i]=($row3['max']*$p[$i]);

echo"<tr>";
echo "<td align='center' width='200'>" .$row3['iname']. "</td>"; 
echo "<td align='center' width='200' >" .$pp."</td>";
echo "<td align='center' width='100' >" .$row3['stock']."</td>";
echo "<td align='center' width='10'>".$t[$i]." </td>"; 
echo "</tr>";  
}
}
}
echo "</table>"; 

echo"$aa";
while($row3=mysqli_fetch_array($result))  
{
$stk[]=$row3['stock']; 
$pp=$row3['pro'];
for ($i = 0; $i <$aa; $i++)
{
$t[$i]=$row3['iname'];
IF($a[$i] == $row3['pro'] )

{

$aa=$aa+1;
$t[$i]=($row3['max']*$p[$i]);

echo"<tr>";
echo "<td align='center' width='200'>" .$row3['iname']. "</td>"; 
echo "<td align='center' width='200' >" .$pp."</td>";
echo "<td align='center' width='100' >" .$row3['stock']."</td>";
echo "<td align='center' width='10'>".$t[$i]." </td>"; 
echo "</tr>";  
}
}
}
echo "</table>"; 

mysqli_close($con);
}

?>

`

**giving me the result **
Procedure Reports Upto '31/05/14'

Procedure Name No of Procedures
Andrology Procedure 1
Ovum Pick up 5
Tesa Procedure 2

STOCK REPORT

Medicine Name Stock in Hand Required Quantity
Conical Tube Andrology Procedure -10 4
Gradient 80% (ml) Andrology Procedure 57 2
Gradient 40% (ml) Andrology Procedure 500 2
Sperm Wash (ml) Andrology Procedure 0 10
Volac Pasteur Pippette Andrology Procedure -3 3
Sterile Tips Andrology Procedure 40 4
5ml round bottom tube Andrology Procedure 6 1
Sample Container Andrology Procedure -47 2
14ml round bottom tube Ovum Pick up 250 150
4 well dish Ovum Pick up 0 30
Falcon(3002) Ovum Pick up 266 15
1ml Serological Pippette Ovum Pick up 100 25
Injection Pippette Ovum Pick up 350 5
10ml Serological Pippette Ovum Pick up 60 20
Holding Pippette Ovum Pick up 500 5
Stripper 175 Ovum Pick up 0 5
Sterile Tips Ovum Pick up 40 40
G mops (ml) Ovum Pick up 29 50
Sample Container Ovum Pick up -47 5
Gradient 40% (ml) Tesa Procedure 500 8
Conical Tube Tesa Procedure -10 16
Sample Container Tesa Procedure -47 2
Gradient 80% (ml) Tesa Procedure 57 8
Sperm Wash (ml) Tesa Procedure 0 80
Volac pasteur Pippette Tesa Procedure -3 10
Sterile Tips Tesa Procedure 40 12
5ml round bottom tube Tesa Procedure 6 2
Falcon(3002) Tesa Procedure 266 8

**what i need is not to repeat the medicine name ; but to sum up the required quantity for the same medicine name
**

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.