Home » SQL & PL/SQL » SQL & PL/SQL » GROUPING ISSUE (merged) (Oracle 11g)
GROUPING ISSUE (merged) [message #578541] Fri, 01 March 2013 02:27 Go to next message
srinivas.k2005
Messages: 283
Registered: August 2006
Senior Member
Hi,

D Status CODE
--- ------ ----
1 recieved 12
2 validate 12
3 recieved 14
4 validate 14
5 recieved 15
6 recieved 15
7 recieved 15


I should retrieve values based on following conditions.

1. Based on GROUPING on column CODE, RETRIEVE ONLY those records having the values 'recieved' AND
2. EXCLUDE those records having the values 'recieved' WHERE grouping based on column CODE include the values 'validate'


For example,

Grouping based on CODE value '12':
1 recieved 12
2 validate 12
Above records are rejected.




Grouping based on CODE value '15':
5 recieved 15
6 recieved 15
7 recieved 15
Above records should be retrieved.




Below is the output I need.

ID Status CODE
--- --- ---
5 recieved 15
6 recieved 15
7 recieved 15


  Create table feed(
      ID Number,
      status varchar2(20),
      CODE number);

insert into feed values (1,'recieved', 12);

insert into feed values (2,'validate' , 12);

insert into feed values (3,'recieved' , 14);

insert into feed values (4,'validate' , 14);

insert into feed values (5,'recieved' , 15);

insert into feed values (6,'recieved' , 15);

insert into feed values (7,'recieved' , 15);



Thanks,
Srk
Re: GROUPING ISSUE [message #578542 is a reply to message #578541] Fri, 01 March 2013 02:32 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Below code may help

SQL> select * from feed;

        ID STATUS                     CODE
---------- -------------------- ----------
         1 recieved                     12
         2 validate                     12
         3 recieved                     14
         4 validate                     14
         5 recieved                     15
         6 recieved                     15
         7 recieved                     15

7 rows selected.

SQL> with t1 as (select * from feed where code=15)
  2  select * from t1;

        ID STATUS                     CODE
---------- -------------------- ----------
         5 recieved                     15
         6 recieved                     15
         7 recieved                     15

SQL> with t1 as (select id,status,code,count(code) over (partition by code) as c
ode_cnt from feed where status='recieved') select id,status,code from t1
  2  ;

        ID STATUS                     CODE
---------- -------------------- ----------
         1 recieved                     12
         3 recieved                     14
         5 recieved                     15
         6 recieved                     15
         7 recieved                     15

[Updated on: Fri, 01 March 2013 02:40]

Report message to a moderator

Re: GROUPING ISSUE [message #578543 is a reply to message #578542] Fri, 01 March 2013 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rishwinger,

This silly, how does it return nothing when you change 15 to 12?

Regards
Michel
Re: GROUPING ISSUE [message #578544 is a reply to message #578542] Fri, 01 March 2013 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK you added a new query but this still does not give the correct output.

Regards
Michel
Re: GROUPING ISSUE [message #578545 is a reply to message #578543] Fri, 01 March 2013 02:44 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
@Michel

how about this one?

If your requirement is to look id wise for 15,12 or whatever

SQL> with t1 as (select id,status,code,count(code) over (partition by code) as c
ode_cnt from feed where status='recieved' ) select id,status,code from t1 where
code=15;

        ID STATUS                     CODE
---------- -------------------- ----------
         5 recieved                     15
         6 recieved                     15
         7 recieved                     15

SQL> with t1 as (select id,status,code,count(code) over (partition by code) as c
ode_cnt from feed where status='recieved' ) select id,status,code from t1 where
code=12;

        ID STATUS                     CODE
---------- -------------------- ----------
         1 recieved                     12

[Updated on: Fri, 01 March 2013 02:47]

Report message to a moderator

Re: GROUPING ISSUE [message #578546 is a reply to message #578541] Fri, 01 March 2013 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@srinivas.k2005

If I understand you, you want the rows where the status in the same code is only and always 'recieved':
SQL> with 
  2    data as (
  3      select id, status, code,
  4             count(*) over (partition by code) total,
  5             count(decode(status, 'recieved', 1)) over (partition by code) received
  6     from feed
  7    )
  8  select id, status, code
  9  from data
 10  where received = total
 11  /

        ID STATUS                     CODE
---------- -------------------- ----------
         5 recieved                     15
         6 recieved                     15
         7 recieved                     15

Regards
Michel
Re: GROUPING ISSUE [message #578547 is a reply to message #578545] Fri, 01 March 2013 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 01 March 2013 09:43
OK you added a new query but this still does not give the correct output.

Regards
Michel


You added one more but still not correct in my opinion given point 2.

Regards
Michel

[Updated on: Fri, 01 March 2013 02:49]

Report message to a moderator

Re: GROUPING ISSUE [message #578548 is a reply to message #578547] Fri, 01 March 2013 03:02 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
oh yes !!I'm silly
Re: GROUPING ISSUE [message #578553 is a reply to message #578546] Fri, 01 March 2013 03:46 Go to previous messageGo to next message
srinivas.k2005
Messages: 283
Registered: August 2006
Senior Member
YES THANKS:)
GROUPING [message #579706 is a reply to message #578541] Thu, 14 March 2013 02:40 Go to previous messageGo to next message
srinivas.k2005
Messages: 283
Registered: August 2006
Senior Member
Hi,

Below are the values from table.

ID Status CODE
--- ------ ----
1 recieved 12
2 validate 12
3 recieved 14
4 validate 14
5 recieved 15
6 recieved 15
7 recieved 15
8 confirm 15
9 recieved 16
10 recieved 16
11 recieved 16
12 recieved 17
13 recieved 17
14 recieved 17
15 NOted 17
16 NOted 17


I should retrieve values based on following conditions.

1. Based on GROUPING on column CODE, RETRIEVE ONLY those records having the values 'recieved' AND not having values 'validate'


For example,

Grouping based on CODE value '12':
1 recieved 12
2 validate 12
Above records are rejected.


Grouping based on CODE value '14':
3 recieved 14
4 validate 14
Above records are rejected.


Grouping based on CODE value '15':
5 recieved 15
6 recieved 15
7 recieved 15
8 confirm 15
Above records should be retrieved.


Grouping based on CODE value '16':
9 recieved 16
10 recieved 16
11 recieved 16
Above records should be retrieved.




Grouping based on CODE value '17':
12 recieved 17
13 recieved 17
14 recieved 17
15 NOted 17
16 NOted 17
Above records should be retrieved.




Below is the output I need.

ID Status CODE
--- --- ---
5 recieved 15
6 recieved 15
7 recieved 15
8 confirm 15
9 recieved 16
10 recieved 16
11 recieved 16
12 recieved 17
13 recieved 17
14 recieved 17
15 NOted 17
16 NOted 17

  Create table feed(
      ID Number,
      status varchar2(20),
      CODE number);

insert into feed values (1,'recieved', 12);

insert into feed values (2,'validate' , 12);

insert into feed values (3,'recieved' , 14);

insert into feed values (4,'validate' , 14);

insert into feed values (5,'recieved' , 15);

insert into feed values (6,'recieved' , 15);

insert into feed values (7,'recieved' , 15);

insert into feed values (8,'confirm' , 15);

insert into feed values (9,'recieved' , 16);

insert into feed values (10,'recieved' , 16);

insert into feed values (11,'recieved' , 16);

insert into feed values (12,'recieved' , 17);

insert into feed values (13,'recieved' , 17);

insert into feed values (14,'recieved' , 17);

insert into feed values (15,'NOted' , 17);

insert into feed values (16,'NOted' , 17);


Regards,
SRK
Re: GROUPING [message #579707 is a reply to message #579706] Thu, 14 March 2013 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not the same question than in your previous topic?
If not then explain and post what you tried to achieve this difference from the query we gave you.

Regards
Michel
Re: GROUPING [message #579708 is a reply to message #579707] Thu, 14 March 2013 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you said:

Quote:
RETRIEVE ONLY those records having the values 'recieved' AND not having values 'validate'


Quote:
16 NOted 17
Above records should be retrieved.


As far as I can see 'NOted' is not 'recieved'.

Anyway, it is easy to modify the previous query above to achieve you new needs. You just have to modify the case expression in line 5.

Regards
Michel
Re: GROUPING [message #579929 is a reply to message #579708] Sun, 17 March 2013 22:08 Go to previous messageGo to next message
ip16022013
Messages: 1
Registered: March 2013
Location: India
Junior Member

SELECT id,
       status,
       code
FROM   feed
WHERE  status != 'validate'
   AND code NOT IN (SELECT code
                      FROM   feed
                     WHERE  status = 'validate')
ORDER  BY 1;
Re: GROUPING [message #580019 is a reply to message #579708] Tue, 19 March 2013 06:06 Go to previous messageGo to next message
srinivas.k2005
Messages: 283
Registered: August 2006
Senior Member
I tried but not working.how to use case expression.

Regards,
SRK
Re: GROUPING [message #580020 is a reply to message #579707] Tue, 19 March 2013 06:13 Go to previous messageGo to next message
srinivas.k2005
Messages: 283
Registered: August 2006
Senior Member
The requirement has been changed. Below is new one.

ID Status CODE
--- ------ ----
1 recieved 12
2 validate 12
3 recieved 14
4 validate 14
5 recieved 15
6 recieved 15
7 recieved 15
8 confirm 15
9 recieved 16
10 recieved 16
11 recieved 16
12 recieved 17
13 recieved 17
14 recieved 17
15 NOted 17
16 NOted 17


I should retrieve values based on following conditions.

1. Based on GROUPING on column CODE, RETRIEVE ONLY those records having the values 'recieved' AND not having values 'validate'


For example,

Grouping based on CODE value '12':
1 recieved 12
2 validate 12
Above records are rejected.


Grouping based on CODE value '14':
3 recieved 14
4 validate 14
Above records are rejected.


Grouping based on CODE value '15':
5 recieved 15
6 recieved 15
7 recieved 15
8 confirm 15
Above records should be retrieved except "confirm"


Grouping based on CODE value '16':
9 recieved 16
10 recieved 16
11 recieved 16
Above records should be retrieved.




Grouping based on CODE value '17':
12 recieved 17
13 recieved 17
14 recieved 17
15 NOted 17
16 NOted 17
Above records should be retrieved except "NOted"




Below is the output I need.

ID Status CODE
--- --- ---
5 recieved 15
6 recieved 15
7 recieved 15
8 confirm 15
9 recieved 16
10 recieved 16
11 recieved 16
12 recieved 17
13 recieved 17
14 recieved 17
15 NOted 17
16 NOted 17


  Create table feed(
      ID Number,
      status varchar2(20),
      CODE number);

insert into feed values (1,'recieved', 12);

insert into feed values (2,'validate' , 12);

insert into feed values (3,'recieved' , 14);

insert into feed values (4,'validate' , 14);

insert into feed values (5,'recieved' , 15);

insert into feed values (6,'recieved' , 15);

insert into feed values (7,'recieved' , 15);

insert into feed values (8,'confirm' , 15);

insert into feed values (9,'recieved' , 16);

insert into feed values (10,'recieved' , 16);

insert into feed values (11,'recieved' , 16);

insert into feed values (12,'recieved' , 17);

insert into feed values (13,'recieved' , 17);

insert into feed values (14,'recieved' , 17);

insert into feed values (15,'NOted' , 17);

insert into feed values (16,'NOted' , 17);



Regards,
SRK
Re: GROUPING [message #580021 is a reply to message #580020] Tue, 19 March 2013 06:24 Go to previous message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not the same thing than the previous question?
If not, what it the difference?
And what did you try from the previous answer I gave you?
As I said: "You just have to modify the case expression in line 5."; well, it is not CASE it is DECODE but this is the same.

Regards
Michel
Previous Topic: PLS-00382: expression is of wrong type
Next Topic: Previous day
Goto Forum:
  


Current Time: Thu Apr 17 05:09:25 CDT 2014

Total time taken to generate the page: 0.07831 seconds