Hi All

I know you're going to ask for table definitions and insert statements, but
I think I'm just confused on the syntax of how to get this query to work
rather than anything else.

My setup is as follows:

TABLE 1 - STOCKCATEGORIES

This table consists of the STOCKCATEGORYID and the NAME

TABLE 2 - STOCK

The only reason I use this table is to link table 1 to table 3 as it
contains the STOCKCATEGORYID relating to table 1 and the STOCKID relating to
table 3.

TABLE 3 - STOCKTRANSACTIONS

This table holds line by line transactions and the data I need is the
STOCKID, DESCRIPTION, QUANTITY, SELLING PRICE.

The 1st draft of my query basically selects the STOCKCATEGORYID, NAME,
STOCKID, DESCRIPTION, QUANTITY, SELLING PRICE for a given stock category
range using the STOCKCATEGORYID field in table 1, a date range using the
TRANSACTIONDATE field in table 3 and a specific TRANSACTIONTYPE from table
3.

This query works fine as I know it is pretty basic. Now comes the bit that
I can't get to work.

What I want to do is summarise the total QUANTITY and SELLING PRICE fields
for each stock code. When I do this (see my query below) I still see many
duplicates of the stock codes.

Could you please have a look at the below query and let me know what I am
doing stupidly wrong.

Many thanks.

Rgds

Robbie

==== My query ======

SELECT STOCKCATEGORIES.STOCKCATEGORYID, STOCKCATEGORIES.NAME,
STOCKTRANSACTIONS.STOCKID, STOCKTRANSACTIONS.DESCRIPTION,
Sum(STOCKTRANSACTIONS.QUANTITY) AS 'Sum of QUANTITY',
Sum(STOCKTRANSACTIONS.SELLINGPRICE) AS 'Sum of SELLINGPRICE'

FROM STOCK, STOCKCATEGORIES, STOCKTRANSACTIONS

WHERE STOCK.STOCKID = STOCKTRANSACTIONS.STOCKID AND STOCK.STOCKCATEGORYID =
STOCKCATEGORIES.STOCKCATEGORYID

GROUP BY STOCKCATEGORIES.STOCKCATEGORYID, STOCKCATEGORIES.NAME,
STOCKTRANSACTIONS.STOCKID, STOCKTRANSACTIONS.DESCRIPTION,
STOCKTRANSACTIONS.TRANSACTIONDATE, STOCKTRANSACTIONS.TRANSACTIONTYPE

HAVING (STOCKCATEGORIES.STOCKCATEGORYID>='002' And
STOCKCATEGORIES.STOCKCATEGORYID<='035') AND
(STOCKTRANSACTIONS.TRANSACTIONDATE>={ts '2002-01-01 00:00:00'} And
STOCKTRANSACTIONS.TRANSACTIONDATE<={ts '2003-12-31 00:00:00'}) AND
(STOCKTRANSACTIONS.TRANSACTIONTYPE=8)

ORDER BY STOCKCATEGORIES.STOCKCATEGORYID, STOCKTRANSACTIONS.STOCKID

Replied in microsoft.public.sqlserver.programming
Please don't multi-post.

--
David Portas
------------
Please reply only to the newsgroup
--