Very New to SQL *Help* - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Very New to SQL *Help*

Author

Topic

Chilly
Starting Member

3 Posts

Posted - 2007-03-30 : 09:41:51

I am trying to extract data from an oracle database. I would like to retrieve multiple fields and group by part number with the total quantity. The script I used that will extract the data I want is as follows:

SELECT ITEM.PART,CAT1.NSN,ITEM.RECEIPT_UM,ITEM.QTY,WHSE.STOCK_LEVEL
FROM ITEM,CAT1,WHSE
WHERE ITEM.PART = CAT1.PART AND WHSE.PART = ITEM.PART

It will display the fields I want but includes multiple lines with the same part number because the parts are serialized and have their own instance in the table

Example
PART NSN RECEIPT_UM QTY STOCK_LEVEL
123 jjjjj EA 1 3
123 jjjjj EA 1 3
123 jjjjj EA 1 3
456 kkkkk KT 2 5
456 kkkkk KT 3 5

How I would like the data to look like is:
PART NSN RECEIPT_UM QTY STOCK_LEVEL
123 jjjjj EA 3 3
456 kkkkk KT 5 5

I have tried to use the SUM and group by which works if I only use part and qty from the same table
SELECT PART,SUM(QTY)
FROM ITEM
WHERE SC = 'CUSTOMER'
GROUP BY PART;

I also plan to use a where clause.

Any advice or help is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 09:58:18

SELECT ITEM.PART, CAT1.NSN, ITEM.RECEIPT_UM, SUM(ITEM.QTY), MAX(WHSE.STOCK_LEVEL)
FROM ITEM
INNER JOIN CAT1 ON CAT1.PART = ITEM.PART
INNER JOIN WHSE ON WHSE.PART = ITEM.PART
GROUP BY ITEM.PART, CAT1.NSN, ITEM.RECEIPT_UM
ORDER BY ITEM.PART, CAT1.NSN, ITEM.RECEIPT_UM

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-30 : 09:58:50

Well this being a sql server site.....

SELECT DISTINCT should stilll work in H'Oracle

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-30 : 10:01:14

This should give you what you want. I have changed the join syntax from what you had although that would also have worked, but this is the recommended syntax.
Note that this is a SQL Server forum and you should rather try forums.oracle.com or dbforums.com for Oracle specific help.

SELECT ITEM.PART, CAT1.NSN, ITEM.RECEIPT_UM, SUM(ITEM.QTY) AS QTY, WHSE.STOCK_LEVEL
FROM ITEM
INNER JOIN CAT1 ON ITEM.PART = CAT1.PART
INNER JOIN WHSE ON WHSE.PART = ITEM.PART
WHERE <any non-join criteria here>
GROUP BY ITEM.PART, CAT1.NSN, ITEM.RECEIPT_UM, WHSE.STOCK_LEVEL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 15:00:30

quote:
Originally posted by X002548

SELECT DISTINCT should stilll work in H'Oracle

PART NSN RECEIPT_UM QTY STOCK_LEVEL
123 jjjjj EA 1 3
123 jjjjj EA 1 3
123 jjjjj EA 1 3
456 kkkkk KT 2 5
456 kkkkk KT 3 5

How I would like the data to look like is:
PART NSN RECEIPT_UM QTY STOCK_LEVEL 
123 jjjjj EA 3 3
456 kkkkk KT 5 5

1+1+1 = 3
2+3   = 5

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources