Problem Using Sum with Join

[rusdyrip]

pls help me solve this problem

i have
Table StockTransaction
field:
Code |In |Out

Table StockMaster
field:
Code |Name

now i want to show Last Stock
i use this query

SELECT 
STOCKTRANSACTION.CODE,
SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
FROM STOCKTRANSACTION 
GROUP BY STOCKTRANSACTION.CODE

it's work

but what i want is display the name with other field
i use this query but shows error

SELECT 
STOCKTRANSACTION.CODE,
STOCKMASTER.NAME,
SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
FROM STOCKTRANSACTION 
INNER JOIN STOCK MASTER
ON STOCKTRANSACTION.CODE=MASTER.CODE
GROUP BY STOCKTRANSACTION.CODE

if i add group by like

GROUP BY STOCKTRANSACTION.CODE,STOCKMASTER.NAME

It's work but the result is different ( some item shows double)


sory bad english

[amitpatel66]

pls help me solve this problem

i have
Table StockTransaction
field:
Code |In |Out

Table StockMaster
field:
Code |Name

now i want to show Last Stock
i use this query

SELECT 
STOCKTRANSACTION.CODE,
SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
FROM STOCKTRANSACTION 
GROUP BY STOCKTRANSACTION.CODE

it's work

but what i want is display the name with other field
i use this query but shows error

SELECT 
STOCKTRANSACTION.CODE,
STOCKMASTER.NAME,
SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
FROM STOCKTRANSACTION 
INNER JOIN STOCK MASTER
ON STOCKTRANSACTION.CODE=MASTER.CODE
GROUP BY STOCKTRANSACTION.CODE

if i add group by like

GROUP BY STOCKTRANSACTION.CODE,STOCKMASTER.NAME

It's work but the result is different ( some item shows double)


sory bad english

Try This:

 
SELECT x.CODE, y.name, x.Total FROM
(SELECT 
STOCKTRANSACTION.CODE AS CODE,
SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
FROM STOCKTRANSACTION 
GROUP BY STOCKTRANSACTION.CODE) x, STOCK MASTER y
WHERE x.code = y.code