Forum Stats

  • 3,782,078 Users
  • 2,254,593 Discussions
  • 7,879,912 Comments

Discussions

Query to show the Works which are in Active state more than once

Anindya G
Anindya G Member Posts: 15 Green Ribbon
edited May 10, 2019 11:29AM in SQL & PL/SQL

Hi,

I have a table having table-name as "Address" and two columns T1 and T2 as below:

My requirement is to show the Works which are in Active state more than once i.e. the output will be Work1 and Work2.

Can anyone please help me out with the query.

 

T1T2
Work1Active
Work1Inactive
Work2Active
Work2Active
Work1Active
Work3Inactive
Work2Inactive

Thanks,

Anindya

Best Answer

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,695 Silver Crown
    edited May 10, 2019 11:20AM Accepted Answer

    This will work:

    SELECT   T1,T2, COUNT(*) AS Quantity, MAX(T3) AS Higher, MIN(T3) AS Lower

    FROM      ADDRESS

    WHERE     T2  = 'Inactive'

    GROUP BY  T1, T2

    HAVING    COUNT (*)  > 1

    ORDER BY  T1;

    But you cannot show T3 alone since you are grouping by T1 and T2. Columns shown on the SELECT clause need to be either on the GROUP BY or as an argument of a Aggregate Function.

«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 10, 2019 10:14AM
    Anindya G wrote:Hi,I have a table having table-name as "Address" and two columns T1 and T2 as below:My requirement is to show the Works which are in Active state more than once i.e. the output will be Work1 and Work2.Can anyone please help me out with the query.T1T2Work1ActiveWork1InactiveWork2ActiveWork2ActiveWork1ActiveWork3InactiveWork2InactiveThanks,Anindya

    Please click on URL below & provide details as stated in #5 - #9 inclusive

    How do I ask a question on the forums?

    Why are NONE of your threads are marked as  ANSWERED?

    https://community.oracle.com/people/Anindya%20G/content?customTheme=otn

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,388 Red Diamond
    edited May 10, 2019 10:13AM

    Hi, Anindya,

    Anindya G wrote:Hi,I have a table having table-name as "Address" and two columns T1 and T2 as below:My requirement is to show the Works which are in Active state more than once i.e. the output will be Work1 and Work2.Can anyone please help me out with the query.T1T2Work1ActiveWork1InactiveWork2ActiveWork2ActiveWork1ActiveWork3InactiveWork2InactiveThanks,Anindya

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
    Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    It always helps to show what you've tried, that is, the code that seems to come closest to what you want.

    Always say which version of Oracle you're using (for example, 12.2.0.1.0).  MATCH_RECOGNIZE is often useful in problems like this, but it's only available in Oracle 12.1 (and higher).
    See the Forum FAQ:

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,388 Red Diamond
    edited May 10, 2019 10:19AM

    Hi,

    Anindya G wrote:...My requirement is to show the Works which are in Active state more than once i.e. the output will be Work1 and Work2....

    Do you mean you want two rows of out[put, with just the t1 column?

    If so, here's one way:

    SELECT    t1FROM      addressWHERE     t2  = 'Active'GROUP BY  t1HAVING    COUNT (*)  > 1ORDER BY  t1;
    Anindya G
  • Anindya G
    Anindya G Member Posts: 15 Green Ribbon
    edited May 10, 2019 10:52AM

    Thanks a lot Frank.

    Is it possible to show t1 and t2 columns both in the output.

    I tried to show that but its throwing error as :

    ORA-00979: not a GROUP BY expression

    00979. 00000 -  "not a GROUP BY expression".

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 10, 2019 10:54AM
    Anindya G wrote:Thanks a lot Frank.Is it possible to show t1 and t2 columns both in the output.I tried to show that but its throwing error as :ORA-00979: not a GROUP BY expression00979. 00000 - "not a GROUP BY expression".

    we can't say what is wrong since you decided to NOT show us the SQL that threw posted error.

    use COPY & PASTE so we can see what you do & how Oracle responds

  • Anindya G
    Anindya G Member Posts: 15 Green Ribbon
    edited May 10, 2019 11:06AM

    Please find the below scripts, statements executed and the error message:

    Scripts:

      CREATE TABLE "ABC"."ADDRESS"

       ( "T1" VARCHAR2(20 BYTE),

    "T2" VARCHAR2(20 BYTE),

    "T3" VARCHAR2(20 BYTE)

       )

      

         

    Insert into ABC.ADDRESS (T1,T2,T3) values ('Work1','Active','900');

    Insert into ABC.ADDRESS (T1,T2,T3) values ('Work1','Inactive','950');

    Insert into ABC.ADDRESS (T1,T2,T3) values ('Work2','Active','800');

    Insert into ABC.ADDRESS (T1,T2,T3) values ('Work2','Active','845');

    Insert into ABC.ADDRESS (T1,T2,T3) values ('Work1','Active','997');

    Insert into ABC.ADDRESS (T1,T2,T3) values ('Work3','Inactive','940');

    Insert into ABC.ADDRESS (T1,T2,T3) values ('Work2','Inactive','945');

    Statement Executed:

    SELECT   T1,T2,T3

    FROM      ADDRESS

    WHERE     T2  = 'Inactive'

    GROUP BY  T1

    HAVING    COUNT (*)  > 1

    ORDER BY  T1;

    Error:

    ORA-00979: not a GROUP BY expression

    00979. 00000 -  "not a GROUP BY expression"

    *Cause:   

    *Action:

    Error at Line: 1 Column: 13

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,388 Red Diamond
    edited May 10, 2019 11:08AM

    Hi,

    Anindya G wrote:Thanks a lot Frank.Is it possible to show t1 and t2 columns both in the output....

    Sure.  If you want to see exactly how, you know what you need to do.  (See replies #1 and #2.)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,388 Red Diamond
    edited May 10, 2019 11:18AM

    Hi,

    Your reply #6 (with the sample data) wasn't visible when I posted reply #7.

    The sample data looks good.  Don't forget to post the exact results you want from that sample data, and an explanation of why you want those results, given that data.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,695 Silver Crown
    edited May 10, 2019 11:20AM Accepted Answer

    This will work:

    SELECT   T1,T2, COUNT(*) AS Quantity, MAX(T3) AS Higher, MIN(T3) AS Lower

    FROM      ADDRESS

    WHERE     T2  = 'Inactive'

    GROUP BY  T1, T2

    HAVING    COUNT (*)  > 1

    ORDER BY  T1;

    But you cannot show T3 alone since you are grouping by T1 and T2. Columns shown on the SELECT clause need to be either on the GROUP BY or as an argument of a Aggregate Function.

  • Anindya G
    Anindya G Member Posts: 15 Green Ribbon
    edited May 10, 2019 11:26AM

    Thanks a lot for your help.

    It worked. Thanks for the explanation too.