Page 1 of 1
group tables
Posted: Sat Apr 17, 2004 8:04 am
by nikko50
Sharon here I have 2 tables that I would like to print a summary.
Table 1 has this data...
Equipment Received
flash cards 50
copier 50
keyboard 50
printer 50
Table 2 has this data...
Equipment Shipped
copier 1
copier 2
printer 1
printer 3
usb cable 4
128MB MEM 4
keyboard 3
keyboard 3
I would like a sql statement to print a summary that shows all equipment items, shipped, received.
So it should like like this........
Equipment Received Shipped
flash cards 50 0
copier 50 2
keyboard 50 3
printer 50 2
usb cable 0 4
128mb mem 0 4
All I have so far is this
$equiplist="SELECT item, sum(quantity) as quantity FROM shiplist GROUP BY item";
Posted: Sat Apr 17, 2004 8:48 am
by JAM
Try something like:
Code: Select all
select recieved.type, sum(recieved.amount) as 'recievedsum', sum(shipped.amount) as 'shippeddsum'
from recieved
left join shipped on shipped.type = recieved.type
group by recieved.type
Posted: Sat Apr 17, 2004 10:35 am
by nikko50
Hi there. Thanks for the response. The sums are very high. It seems to be adding everything 4 times over. Here is my code. I should mention I have 2 Tables called RECEIVE and SHIPLIST.
Code: Select all
$equiplist= "select shiplist.item, sum(shiplist.quantity) as 'shippedsum', sum(receive.quantity) as 'receivedsum'
from shiplist
left join receive on receive.equipment = shiplist.item
group by shiplist.item";
$equipresult=mysql_query($equiplist) or print (mysql_error());
echo "<p align='center'><font size='4'><b>Inventory </b></font></p>";
print "<br>";
while ($row= mysql_fetch_array($equipresult))
{ extract($row);
print "<table border=1 width=70% cellpadding=1 cellspacing=1 align=center>";
print "<tr>
<td width=35%><font size='2'>$item</font></td>
<td width=5%><font size='2'>$receivedsum</font></td>
<td width=5%><font size='2'>$shippedsum</font></td>
</tr>
</table>";}
?>
[Edit: Added PHP tags for eyecandy -- JAM]
Posted: Sat Apr 17, 2004 11:11 am
by JAM
Hard to grasp, as I tried a similar table structure here, and it worked like a charm...
Someone else might have some ideas? You can also dump some table data and an example of the errorous output.
Posted: Sat Apr 17, 2004 1:44 pm
by nikko50
Here's my code. It seems when I add something in the receive or shiplist table the query adds whatever quantity I added to both the received and shipped fields.
//get equipment list
$equiplist= "select shiplist.item, sum(shiplist.quantity) as 'shippedsum', sum(receive.quantity) as 'receivedsum'
from shiplist
left join receive on receive.equipment = shiplist.item
group by shiplist.item";
$equipresult=mysql_query($equiplist) or print (mysql_error());
echo "<p align='center'><font size='4'><b>Inventory </b></font></p>";
print "<br>";
print "<table border=1 width=70% cellpadding=1 cellspacing=1 align=center>";
print "<tr>
<td width=35%><font size='2'>Equipment</font></td>
<td width=8%><font size='2'>Shipped</font></td>
<td width=8%><font size='2'>Received</font></td>
<td width=8%><font size='2'>On Hand</font></td>
</tr>
</table>";
while ($row= mysql_fetch_array($equipresult))
{ extract($row);
$onhand=$receivedsum-$shippedsum;
$onhand = ( $onhand <= 0 ) ? '<span style="color: red">'.$onhand.'</span>' : $onhand;
print "<table border=1 width=70% cellpadding=1 cellspacing=1 align=center>";
print "<tr>
<td width=35%><font size='2'>$item</font></td>
<td width=8%><font size='2'>$shippedsum</font></td>
<td width=8%><font size='2'>$receivedsum</font></td>
<td width=8%><font size='2'>$onhand</font></td>
</tr>
</table>";}
?>
Posted: Sat Apr 17, 2004 2:46 pm
by JAM
nikko50 wrote:Here's my code. It seems when I add something in the receive or shiplist table the query adds whatever quantity I added to both the received and shipped fields. <cut>
Then the select query is correct and you should post the code that is doing the insert?