group tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nikko50
Forum Commoner
Posts: 43
Joined: Thu Apr 08, 2004 6:28 am

group tables

Post 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";
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
nikko50
Forum Commoner
Posts: 43
Joined: Thu Apr 08, 2004 6:28 am

Post 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]
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
nikko50
Forum Commoner
Posts: 43
Joined: Thu Apr 08, 2004 6:28 am

Post 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>";}


?>
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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?
Post Reply