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 Go to next message
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 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / 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 #601582 is a reply to message #601579] Fri, 22 November 2013 06:41 Go to previous messageGo to next message
managerreci
Messages: 5
Registered: November 2013
Location: Germany
Junior Member
@ dariyoosh
Thank you.
But your example refers to ONE record in a table.

I have to summarize to records in one.
ID   TYP   RESID   PRODID   STD   25STD   33STD   50STD   100STD
1     S    1001     2001      0     44     100     200     300
2     A    1001     2001    300      0       -       -       -

Result
RESID   PRODID   STD   25STD   33STD   50STD   100STD  STD_A  25STD_A
1001     2001      0    44      100     200     300     300      0


Re: multiple select from one table [message #601583 is a reply to message #601582] Fri, 22 November 2013 06:47 Go to previous messageGo to next message
cookiemonster
Messages: 11203
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / 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 #601587 is a reply to message #601586] Fri, 22 November 2013 07:53 Go to previous messageGo to next message
managerreci
Messages: 5
Registered: November 2013
Location: Germany
Junior Member
I want to spend the records with the S types.
The datasets with the types S can have another record with the type A.
These two data sets can be linked through the fields RESID and PRODID.
Re: multiple select from one table [message #601588 is a reply to message #601583] Fri, 22 November 2013 08:10 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
That doesn't actually answer this question:

cookiemonster wrote on Fri, 22 November 2013 12:47
If 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 #601589 is a reply to message #601588] Fri, 22 November 2013 08:14 Go to previous messageGo to next message
managerreci
Messages: 5
Registered: November 2013
Location: Germany
Junior Member
ooh sorry.

Yes.
Re: multiple select from one table [message #601590 is a reply to message #601589] Fri, 22 November 2013 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 11203
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
Re: multiple select from one table [message #601639 is a reply to message #601590] Sat, 23 November 2013 11:07 Go to previous messageGo to next message
Kevin Meade
Messages: 1957
Registered: December 1999
Location: Connecticut USA
Senior Member
how about an outer join? Or are you restricted to now using multiple table references?
Re: multiple select from one table [message #601652 is a reply to message #601639] Sat, 23 November 2013 14:20 Go to previous message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually an outer join is far more obvious than mine and I'm not sure why I didn't think of it.
Previous Topic: User table and their corresponding numbers
Next Topic: object type
Goto Forum:
  


Current Time: Thu Nov 27 21:15:53 CST 2014

Total time taken to generate the page: 0.10187 seconds