Home » SQL & PL/SQL » SQL & PL/SQL » help with sql grouping
help with sql grouping [message #664887] Thu, 10 August 2017 02:07 Go to next message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
hi I have a scenerio where I have to mark a row as o or 1 based on the values of a group
example
FNO CODE
10 100
10 100
20 100
20 50
30 60
30 70

EXPECTED OUTPUT
FNO CODE FLAG
10 100 1
10 100 1
20 100 0
20 50 0
30 60 0
30 70 0

so basically if all codes under a group(FNO) is 100 then I have to mark flag as 1 else if any one of the code is not 100 or if code value 100 is not present in the group then I have to mark flag as 0. I have managed to write a sql something like below to get the output


select min(flag), fno from (
select distinct fno,1 as flag from table where code=100
union
select distinct fno,0 as flag from table where code<>100
union
select distinct fno,0 as flag from table where code=100 and fno in(select fno from table where code<>100)
) group by fno;

I would like to know if there is a better way to write this in sql.

Thank you for your help.
Re: help with sql grouping [message #664891 is a reply to message #664887] Thu, 10 August 2017 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: help with sql grouping [message #664900 is a reply to message #664887] Thu, 10 August 2017 06:52 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hi,

I would not say that the query you posted produces the expected output - it does not contain CODE column and the GROUP BY clause returns only one row per FNO.

If the expected output and its text description are correct, you will need to use Oracle analytic function(s). Something like this may help you to start:
count(case when code = 100 then 1 end) over (partition by fno)
count(case when code != 100 then 1 end) over (partition by fno)
Re: help with sql grouping [message #664908 is a reply to message #664900] Thu, 10 August 2017 22:50 Go to previous messageGo to next message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
Hi all thanks for the reply.

I would try to present the question better

CREATE table t1
( 
  fno varchar2(5)  NOT NULL,
  code varchar2(5) NOT NULL,
 );

INSERT INTO t1 (fno, code)
VALUES
(10, 100 );

INSERT INTO t1 (fno, code)
VALUES
(10, 100 );

INSERT INTO t1 (fno, code)
VALUES
(20, 100 );

INSERT INTO t1 (fno, code)
VALUES
(20, 30 );

INSERT INTO t1 (fno, code)
VALUES
(20, 40 );

INSERT INTO t1 (fno, code)
VALUES
(30, 30 );

INSERT INTO t1 (fno, code)
VALUES
(30, 20 );

INSERT INTO t1 (fno, code)
VALUES
(40, 50 );

INSERT INTO t1 (fno, code)
VALUES
(50, 100 );

tgt table

CREATE table t2
( 
  fno varchar2(5)  NOT NULL,
  code varchar2(5) NOT NULL,
  flag varchar2(1) notnull
 );

expected output
fno               code              flag
10                 100                1
10                 100                1
20                 100                0
20                 30                 0
20                 40                 0
30                 30                 0
30                 20                 0
40                 50                 0
50                 100                1

Quote:

flyboy: Thanks for the reply but I have to also consider the scenerio where a group in fno say 30 contains code 100 also another code which is <> 100 in this case group 30 should be flagged as 0. My sql does this but i understand that it was a bad way of writing code.
In the above example fno:20 is flagged as 0 because it has a group with code values other than 100. where code values in fno 10 is 100 for whole group hence marked as 1.

Hope I have explained it better this time any inputs is appreciated.

Also there is no cosntarints here as down the lane i am loding data into datasets etc downstream.

Thanks.
Re: help with sql grouping [message #664909 is a reply to message #664908] Thu, 10 August 2017 22:52 Go to previous messageGo to next message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
also I forgot to mention my oracle is 10g and all columns are varchar.
Re: help with sql grouping [message #664912 is a reply to message #664909] Fri, 11 August 2017 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

When you post a test case, please test it before:
SQL> CREATE table t1
  2  (
  3    fno varchar2(5)  NOT NULL,
  4    code varchar2(5) NOT NULL,
  5   );
 )
 *
ERROR at line 5:
ORA-00904: : invalid identifier
SQL> select fno, code,
  2         case
  3           when count(decode(code,100,1)) over (partition by fno)
  4                = count(*) over (partition by fno)
  5             then 1
  6           else 0
  7         end flag
  8  from t1
  9  order by 1, 2
 10  /
FNO   CODE        FLAG
----- ----- ----------
10    100            1
10    100            1
20    100            0
20    30             0
20    40             0
30    20             0
30    30             0
40    50             0
50    100            1

9 rows selected.
Re: help with sql grouping [message #677174 is a reply to message #664912] Wed, 28 August 2019 17:01 Go to previous message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
I apologize for the very late reply the solution posted by michel cadot works perfectly for my scenerio

Thanks all.
Previous Topic: Help with sql using analytical functions.
Next Topic: Create table as select
Goto Forum:
  


Current Time: Fri Mar 29 00:29:50 CDT 2024