complicated query

[rasmas]

HI All

I have a table that contains all the transactions on our stock

I need to extract the transactions with an entry type of I or C for a date period
I then need to add all the quantities for each stock Item sold to individual customers
eg

07/06/2007 I @OF001 7704190 2
04/07/2007 I @OF001 7704190 2
07/09/2007 I @OF001 7704190 1
15/10/2007 C @OF001 7704190 -1
01/02/2008 I @OF001 7704190 1
06/03/2008 I @OF001 7704190 1
15/02/2008 I @OF001 4400396SA 1
18/02/2008 I @OF001 4400434SA 1
05/03/2008 I @OF001 4400434SA 1
25/02/2008 I AMY001 4400424 2
13/02/2008 I AMY001 4400396SA 2
08/02/2008 I AMY001 4400434SA 1

My Result should be

@OF001 7704190 6
@OF001 4400396SA 1
@OF001 4400434SA 2
AMY001 4400424 2
AMY001 4400396SA 2
AMY001 4400434SA 1

I have the following Query

$sql_strans = SELECT
DATE,
ENTRY,
ACCNO,
STOCKNO,
QTY,
FROM
sttrans
WHERE
DATE >= '2007-01-07' AND
DATE <= '2008-03-31' AND
ENTRY = 'C' OR
ENTRY = 'I'
GROUP BY
STOCKNO
ORDER BY
ACCNO ASC

There seems to be a problem with the "Where entry" and the I need to get it to add together.

What Have I done Wrong

Thanks

Martin

[Lars_Berg]

SELECT ACCNO,
       STOCKNO,
       SUM(QTY)
  FROM sttrans
 WHERE `DATE` >= '2007-01-07' 
   AND `DATE` <= '2008-03-31' 
   AND ENTRY in ('C','I')
 GROUP BY STOCKNO,ACCNO
 ORDER BY ACCNO ASC

[rasmas]

Thanks

But but it only displays the Accno and the Stockno the QTY is blank, if I leave out the sum and Just put QTY it displays the Qty

Thanks
Martin

[bogu]

rasmas wrote:

Thanks

But but it only displays the Accno and the Stockno the QTY is blank, if I leave out the sum and Just put QTY it displays the Qty

Thanks
Martin

What type is your QTY field?

[Lars_Berg]

What does your code for displaying the value of sum(qty) look like?

[rasmas]

HI

I have solved that problem I had to add sum(QTY) as SQTY, to refer to it in my code

I have expanded the query now and added the following

SELECT ACCNO,
STOCKNO,
SUM(QTY) as SQTY
SUM(QTYRETAIL) AS selling
SUM(QTYCOST) AS costp
SUM(QTYRETAIL)-SUM(QTYCOST) as gp
FROM sttrans
WHERE DATE >= '2007-01-07'
AND DATE <= '2008-03-31'
AND ENTRY in ('C','I')
GROUP BY STOCKNO,ACCNO
ORDER BY ACCNO ASC

is there away to simply the last line?

while ($row = mysql_fetch_array($rs))
		{
		$i++;
		$bgcolor          = ($i % 2) ? '#E5E5E5' : '#B5B5B5';
			$mach_list .="<TR>";
			$mach_list .="<TD bgcolor= \"$bgcolor\" width =\"250\" align = \"center\">".$row["ACCNO"]."</TD>";
      $mach_list .="<TD bgcolor= \"$bgcolor\" width =\"100\" align = \"center\">".$row["STOCKNO"]."</TD>";
      $mach_list .="<TD bgcolor= \"$bgcolor\" width =\"100\">".$row["SQTY"]."</TD>";
      $mach_list .="<TD bgcolor= \"$bgcolor\" width =\"75\" align = \"center\">".$row["selling"]."</TD>";
      $mach_list .="<TD bgcolor= \"$bgcolor\" width =\"200\">".$row["costp"]."</TD>";
      $mach_list .="<TD bgcolor= \"$bgcolor\" width =\"200\">".$row["gp"]."</TD>";
      }

	$mach_list .= "</table>";

echo ($mach_list);

How wold I put a total in at the end of the List

Thanks for the Help

Martin