By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,358 Members | 2,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,358 IT Pros & Developers. It's quick & easy.

Problem Using Sum with Join

P: 29
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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. STOCKTRANSACTION.CODE,
  3. SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
  4. FROM STOCKTRANSACTION 
  5. 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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. STOCKTRANSACTION.CODE,
  3. STOCKMASTER.NAME,
  4. SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
  5. FROM STOCKTRANSACTION 
  6. INNER JOIN STOCK MASTER
  7. ON STOCKTRANSACTION.CODE=MASTER.CODE
  8. GROUP BY STOCKTRANSACTION.CODE
if i add group by like

Expand|Select|Wrap|Line Numbers
  1. GROUP BY STOCKTRANSACTION.CODE,STOCKMASTER.NAME
It's work but the result is different ( some item shows double)


sory bad english
Mar 28 '08 #1
Share this Question
Share on Google+
1 Reply


amitpatel66
Expert 100+
P: 2,367
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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. STOCKTRANSACTION.CODE,
  3. SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
  4. FROM STOCKTRANSACTION 
  5. 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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. STOCKTRANSACTION.CODE,
  3. STOCKMASTER.NAME,
  4. SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
  5. FROM STOCKTRANSACTION 
  6. INNER JOIN STOCK MASTER
  7. ON STOCKTRANSACTION.CODE=MASTER.CODE
  8. GROUP BY STOCKTRANSACTION.CODE
if i add group by like

Expand|Select|Wrap|Line Numbers
  1. GROUP BY STOCKTRANSACTION.CODE,STOCKMASTER.NAME
It's work but the result is different ( some item shows double)


sory bad english
Try This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT x.CODE, y.name, x.Total FROM
  3. (SELECT 
  4. STOCKTRANSACTION.CODE AS CODE,
  5. SUM(STOCKTRANSACTION.IN-STOCKTRANSACTION.OUT) AS TOTAL
  6. FROM STOCKTRANSACTION 
  7. GROUP BY STOCKTRANSACTION.CODE) x, STOCK MASTER y
  8. WHERE x.code = y.code
  9.  
  10.  
Mar 28 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.