Home » SQL & PL/SQL » SQL & PL/SQL » multiple select from one table (oracle sql)
| multiple select from one table [message #601578] |
Fri, 22 November 2013 06:12  |
 |
managerreci
Messages: 5
Registered: November 2013
Location: Germany
|
Junior Member |
|
|
Hello,
may anybody can help me.
I want to select several records with one statement and sumarize it from a Oracle11g Database.
For example
TABLE: PRODUCT
ID TYP RESID PRODID STD 25STD 33STD 50STD 100STD
1 S 1001 2001 0 44 100 200 300
2 A 1001 2001 300 0 - - -
3 S 1002 2002 444 33 22 101 304
4 S 1003 2003 0 88 66 77 3
5 S 1004 2004 33 0 53 13 12
6 A 1004 2004 0 17 - - -
7 S 1005 2005 120 120 200 250 310
8 S 1006 2006 300 250 111 333 5
9 S 1007 2007 10 12 21 4 8
10 A 1007 2007 20 22 0 0 0
I want only records with the typ 'S' and if records with typ 'A' exists, the the fields STD and 25STD in one result.
The Result have to look like this:
RESID PRODID STD 25STD 33STD 50STD 100STD STD_A 25STD_A
1001 2001 0 44 100 200 300 300 0
1002 2002 444 33 22 101 304 - -
1003 2003 0 88 66 77 3 - -
1004 2004 33 0 53 13 12 0 17
1005 2005 120 120 200 250 310 - -
1006 2006 300 250 111 333 5 - -
1007 2007 10 12 21 4 8 20 22
Thank you for the help.
|
|
|
|
| Re: multiple select from one table [message #601579 is a reply to message #601578] |
Fri, 22 November 2013 06:15  |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
Have a look at decode statement which you can use for std and 25std as searched values
An example (just to see the syntax)
SQL> select t1.department_id,
2 t1.employee_id,
3 t1.first_name,
4 t1.salary,
5 decode(t1.department_id, 90, 'dep is 90', 100, 'dep is 100', 'dep is niether 90 nor 100') result
6 from hr.employees t1
7 where t1.salary > 10000;
DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME SALARY RESULT
------------- ----------- -------------------- ---------- -------------------------
20 201 Michael 13000 dep is niether 90 nor 100
110 205 Shelley 12008 dep is niether 90 nor 100
90 100 Steven 24000 dep is 90
90 101 Neena 17000 dep is 90
90 102 Lex 17000 dep is 90
100 108 Nancy 12008 dep is 100
30 114 Den 11000 dep is niether 90 nor 100
80 145 John 14000 dep is niether 90 nor 100
80 146 Karen 13500 dep is niether 90 nor 100
80 147 Alberto 12000 dep is niether 90 nor 100
80 148 Gerald 11000 dep is niether 90 nor 100
80 149 Eleni 10500 dep is niether 90 nor 100
80 162 Clara 10500 dep is niether 90 nor 100
80 168 Lisa 11500 dep is niether 90 nor 100
80 174 Ellen 11000 dep is niether 90 nor 100
15 rows selected.
SQL>
you can also use the case statement
The very same example above will become:
SQL> select t1.department_id,
2 t1.employee_id,
3 t1.first_name,
4 t1.salary,
5 case t1.department_id
6 when 90 then 'dep is 90'
7 when 100 then 'dep is 100'
8 else 'dep is neither 90 nor 100'
9 end result
10 from hr.employees t1
11 where t1.salary > 10000;
[Updated on: Fri, 22 November 2013 06:32] Report message to a moderator |
|
|
|
|
|
| Re: multiple select from one table [message #601583 is a reply to message #601582] |
Fri, 22 November 2013 06:47 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you post a Test case - create table statements and insert statements for a small, representative sample of data, then we'll be able to work with your tables and data.
What are we grouping by here? resid and prodid?
If so can there only be one record of each type per group?
|
|
|
|
| Re: multiple select from one table [message #601585 is a reply to message #601583] |
Fri, 22 November 2013 07:30 |
|
managerreci
Messages: 5
Registered: November 2013
Location: Germany
|
Junior Member |
|
|
Test Case
CREATE TABLE TABLE_A
(
ID NUMBER NOT NULL
, TYP VARCHAR2(1)
, RESID NUMBER
, PRODID NUMBER
, STD NUMBER
, STD25 NUMBER
, STD33 NUMBER
, STD50 NUMBER
, STD100 NUMBER
, CONSTRAINT TABLE_A_PK PRIMARY KEY
(
ID
)
ENABLE
);
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25, STD33, STD50, STD100) VALUES ('1', 'S', '1001', '2013', '0', '44', '100', '200', '300');
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25) VALUES ('2', 'A', '1001', '2013', '300', '0');
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25, STD33, STD50, STD100) VALUES ('3', 'S', '1002', '2013', '444', '33', '22', '101', '304');
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25, STD33, STD50, STD100) VALUES ('4', 'S', '1003', '2013', '0', '88', '66', '77', '3');
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25, STD33, STD50, STD100) VALUES ('5', 'S', '1004', '2013', '33', '0', '53', '13', '12');
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25) VALUES ('6', 'A', '1004', '2013', '0', '17');
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25, STD33, STD50, STD100) VALUES ('7', 'S', '1005', '2013', '120', '120', '200', '250', '310');
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25, STD33, STD50, STD100) VALUES ('8', 'S', '1006', '2013', '300', '250', '111', '333', '5');
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25, STD33, STD50, STD100) VALUES ('9', 'S', '1007', '2013', '10', '12', '21', '4', '8');
INSERT INTO "TABLE_A" (ID, TYP, RESID, PRODID, STD, STD25) VALUES ('10', 'A', '1007', '2013', '20', '22');
commit;
Desired result:
RESID PRODID STD 25STD 33STD 50STD 100STD STD_A 25STD_A
1001 2001 0 44 100 200 300 300 0
1002 2002 444 33 22 101 304 - -
1003 2003 0 88 66 77 3 - -
1004 2004 33 0 53 13 12 0 17
1005 2005 120 120 200 250 310 - -
1006 2006 300 250 111 333 5 - -
1007 2007 10 12 21 4 8 20 22
Thanks.
|
|
|
|
| Re: multiple select from one table [message #601586 is a reply to message #601585] |
Fri, 22 November 2013 07:35 |
|
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
"managerreci"
Result:
RESID PRODID STD 25STD 33STD 50STD 100STD STD_A 25STD_A
1001 2001 0 44 100 200 300 300 0
Ok, I think I misunderstood the required output in my previous comment.
And what are the rules to produce that output in terms of groups (cookiemonster already asked you)
[Updated on: Fri, 22 November 2013 07:38] Report message to a moderator |
|
|
|
|
|
| Re: multiple select from one table [message #601588 is a reply to message #601583] |
Fri, 22 November 2013 08:10 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That doesn't actually answer this question:
cookiemonster wrote on Fri, 22 November 2013 12:47If so can there only be one record of each type per group?
without an answer to that we can't get anywhere
|
|
|
|
|
|
| Re: multiple select from one table [message #601590 is a reply to message #601589] |
Fri, 22 November 2013 08:19 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
looks like you need max and case:
SQL> SELECT resid, prodid,
2 MAX(CASE WHEN typ = 'S' THEN std END) std,
3 MAX(CASE WHEN typ = 'A' THEN std END) std_a
4 FROM table_a
5 GROUP BY resid, prodid
6 ORDER BY 1,2;
RESID PRODID STD STD_A
---------- ---------- ---------- ----------
1001 2013 0 300
1002 2013 444
1003 2013 0
1004 2013 33 0
1005 2013 120
1006 2013 300
1007 2013 10 20
7 rows selected
SQL>
I'm sure you can add the missing columns
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 23:01:56 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
