Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2011
    Posts
    20

    Unanswered: Query to find status with different values for a combination of data

    contract ; accountno ; sequence ; status
    1001 ; 220 ; 1 ; 1
    1001 ; 220 ; 2 ; 1
    1001 ; 220 ; 3 ; 1

    1001 ; 300 ; 1 ; (null)
    1001 ; 300 ; 2 ; (null)

    1002 ; 230 ; 1 ; 0
    1002 ; 230 ; 2 ; 0
    1002 ; 230 ; 3 ; 0

    1003 ; 500 ; 1 ; 0
    1003 ; 500 ; 2 ; 1
    1003 ; 500 ; 3 ; 0
    1003 ; 500 ; 4 ; 0

    1003 ; 800 ; 1 ; 1
    1003 ; 800 ; 2 ; 1
    1003 ; 800 ; 3 ; (null)

    1005 ; 200 ; 1 ; 0
    1005 ; 200 ; 2 ; (null)


    Need some help with this query. See above table. Need to find combination of contract and accounts that do not have the same status.

    For example:

    Contract 1001, accountno 220 - do not select this contract/invoice because the status are all '1's

    Contract 1001, accountno 300 - do not select this contract/invoice because the status are all null

    Contract 1002, accountno 230 - do not select this contract/invoice because the status are all '0's

    Contract 1003, accountno 500 - select this contract/invoice because the status are a combination of '0' and '1'

    Contract 1003, accountno 800 - select this contract/invoice because the status are a combination of '1' and null

    Contract 1005, accountno 200 - select this contract/invoice because the status are a combination of '0' and null

    The query therefore should return the following contract and accountno:

    1003, 500
    1003, 800
    1005, 200
    Last edited by teachme; 05-18-12 at 18:16. Reason: Format table for ease of reading

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    we don't do homework assignment.
    post SQL that gets you closest to the desired solution.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2011
    Posts
    20
    Quote Originally Posted by anacedent View Post
    we don't do homework assignment.
    post SQL that gets you closest to the desired solution.

    No worries, at least I did try to help others while you post useless info in the forum.

    See the useless info you post here and see how I try to help others in the same thread:

    http://www.dbforums.com/oracle/16702...es-oracle.html

    http://www.dbforums.com/oracle/1670649-need-help.html

    http://www.dbforums.com/oracle/16702...oracle-8i.html


    To help you, I can find groups of contract, accountno that are greater than 1, but within each group, I need to select those with different status:

    select contract, accountno, count(*)
    from sales
    group by contract, accountno
    having count(*) > 1
    Last edited by teachme; 05-18-12 at 19:42.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Of course the easiest way of determining count of distinct values in a column would be simple
    Code:
    COUNT( DISTINCT <column_name> )
    expression.
    Here, as it does not count NULLs, you would have to make some "workaround" for treating it, e.g.
    1) map NULL to some value non-existent in data (-1?) using NVL or COALESCE function.
    2) check the difference between COUNT(*) and COUNT(<column_name>) - if they are not the same, NULL is present in some row(s)

    As your post does not contain whether STATUS may contain different values than the ones posted (by the way, code of CHECK constraint in CREATE TABLE statement would clearly reveal that, only if you posted it) and if so, how to behave in that case, I will let the final decision on you.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that this is a good problem, wheather it is a homework or not.

    The point(for me) is that differences of COUNT(*), COUNT(expr), and COUNT(DISTINCT expr) can be used effectively to solve the problem.

    COUNT

    Purpose

    COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function.

    ...

    If you specify expr, then COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr.

    If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNT never returns null.
    Last edited by tonkuma; 05-19-12 at 03:43. Reason: Add reference to online document(COUNT) and quote form that.

  6. #6
    Join Date
    Sep 2011
    Posts
    20
    Quote Originally Posted by tonkuma View Post
    I thought that this is a good problem, wheather it is a homework or not.
    Only short-sighted people like anacedent will assume it is homework without asking. Guess this question is too hard for him to reply.


    Quote Originally Posted by flyboy View Post
    Of course the easiest way of determining count of distinct values in a column would be simple
    Code:
    COUNT( DISTINCT <column_name> )
    expression.
    Here, as it does not count NULLs, you would have to make some "workaround" for treating it,

    Let me try to help, as I have always believe in helping others as well as learning from others, to count distinct nulls, a simple way of doing that is to use DUMP as part of the count distinct.

    Example: select count (distinct dump(spousalplan))

    This will count all distinct spousalplan including the null value.

    Anyone who can write a query to solve the original problem?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think DUMP may be not usefull.

    DUMP
    If expr is null, then this function returns NULL.
    "count (distinct dump(spousalplan))" wouldn't count null values.

    My idea was...
    Code:
    SELECT contract , accountno
     FROM  sales
     GROUP BY
           contract , accountno
     HAVING
           COUNT(DISTINCT status) > 1
       OR  COUNT(status)
           BETWEEN 1 AND COUNT(*) - 1
    ;
    or, removing OR condition...
    Code:
    SELECT contract , accountno
     FROM  sales
     GROUP BY
           contract , accountno
     HAVING
           COUNT(DISTINCT status) > 1 + SIGN( COUNT(status) - COUNT(*) )
                                 /* IF null row exists THEN
                                       0
                                    ELSE
                                       1
                                    END IF
                                 */
    ;

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by teachme View Post
    Anyone who can write a query to solve the original problem?
    What about using the expression I proposed instead of COUNT(*) in your query? As tonkuma already provided some conditions, here is verbatim transformation of my second point:
    Code:
       COUNT( distinct <column_name> )
     + CASE WHEN COUNT( <column_name> ) != COUNT( * )
            THEN 1 -- some rows include NULLs
            ELSE 0 -- no NULLs present
       END
    (by the way you did not answer my questions; or is your homework assignment also not counting with that cases)

    Although you will not believe me, I also try to help others, but instead of posting "solutions" I tend people force to *think* about their problem - data they currently have, result set they require and rules which shall lead to it. I believe that after doing these steps (yes, they will ache their heads), most people will be able to construct correct solution themselves without any "help" not only for the posted problem, but also in other cases they will face in the future - so they no more need to flood forums on Friday afternoon to get solution of their homework assignment (with unsure prospect) before Monday term. In addition, any given "solution" is useless when the one who asks it does not understand it and so he is unable to adjust it when the assignment is stated more precisely.

  9. #9
    Join Date
    Sep 2011
    Posts
    20
    Quote Originally Posted by tonkuma View Post
    I think DUMP may be not usefull.

    I'm not sure if you have tested DUMP or not, but it is counting nulls according to the following:

    Code:
    select distinct spousalplan from insurance
     
    SPOUSALPLAN
    -----------
    (null)
    Y
    N
    
    select count (distinct spousalplan) from insurance
     
    COUNT(DISTINCTSPOUSALPLAN)
    --------------------------
    2 
    
    select count (distinct dump(spousalplan))  from insurance
    
    COUNT(DISTINCTDUMP(SPOUSALPLAN)) 
    -------------------------------- 
    3

    The above shows DUMP can count distinct values including the NULL value, if not use in the COUNT DISTINCT, the NULL will not be counted.


    I will take all ideas here, including yours flyboy to see how I can solve the problem. I need some time to think on how I should construct the SQL query. Your help is appreciated, got me thinking as to what approach to take to solve the problem....

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the result, if looked values of dump?

    select distinct dump(spousalplan) from insurance

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You already tried

    select contract, accountno, count(*)
    from sales
    group by contract, accountno
    having count(*) > 1
    and

    Code:
    select count (distinct dump(spousalplan))  from insurance
    
    COUNT(DISTINCTDUMP(SPOUSALPLAN)) 
    -------------------------------- 
    3
    So, why not try to replace "count(*)" in the first query with "count (distinct dump(/*spousalplan*/status))"?
    Last edited by tonkuma; 05-20-12 at 06:42. Reason: Replace spousalplan with status

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea.

    Code:
    SELECT contract , accountno
     FROM  sales
     GROUP BY
           contract , accountno
     HAVING
           COUNT(DISTINCT status) > TRUNC( COUNT(status) / COUNT(*) )
    ;

  13. #13
    Join Date
    Sep 2011
    Posts
    20
    Wanted to confirm all the 3 ideas worked. Thanks for teaching.

    Quote Originally Posted by tonkuma View Post
    Another idea.

    Code:
    SELECT contract , accountno
     FROM  sales
     GROUP BY
           contract , accountno
     HAVING
           COUNT(DISTINCT status) > TRUNC( COUNT(status) / COUNT(*) )
    ;

    My idea was...

    Code:
    SELECT contract , accountno
     FROM  sales
     GROUP BY
           contract , accountno
     HAVING
           COUNT(DISTINCT status) > 1
       OR  COUNT(status)
           BETWEEN 1 AND COUNT(*) - 1
    ;
    or, removing OR condition...

    Code:
    SELECT contract , accountno
     FROM  sales
     GROUP BY
           contract , accountno
     HAVING
           COUNT(DISTINCT status) > 1 + SIGN( COUNT(status) - COUNT(*) )
                                 /* IF null row exists THEN
                                       0
                                    ELSE
                                       1
                                    END IF
                                 */
    ;

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by teachme View Post
    I'm not sure if you have tested DUMP or not, but it is counting nulls according to the following:

    Code:
    select distinct spousalplan from insurance
     
    SPOUSALPLAN
    -----------
    (null)
    Y
    N
    
    select count (distinct spousalplan) from insurance
     
    COUNT(DISTINCTSPOUSALPLAN)
    --------------------------
    2 
    
    select count (distinct dump(spousalplan))  from insurance
    
    COUNT(DISTINCTDUMP(SPOUSALPLAN)) 
    -------------------------------- 
    3

    The above shows DUMP can count distinct values including the NULL value, if not use in the COUNT DISTINCT, the NULL will not be counted.

    ....
    Quote Originally Posted by tonkuma View Post
    What are the result, if looked values of dump?

    select distinct dump(spousalplan) from insurance
    What were the results of the query?

    or what results did you got from the following query?
    select distinct spousalplan , dump(spousalplan) from insurance
    Last edited by tonkuma; 05-24-12 at 00:04. Reason: Add quote from teachme.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to know the results of the queries in the last post,
    because I want to confirm the handling of NULLs by COUNT and DUMP functions.

    Code:
    select distinct spousalplan from insurance
     
    SPOUSALPLAN
    -----------
    (null)
    Y
    N
    
    ...
    
    select count (distinct dump(spousalplan))  from insurance
    
    COUNT(DISTINCTDUMP(SPOUSALPLAN)) 
    -------------------------------- 
    3
    The another possibility for the results might be that some spousalplan had trailing blank(s).
    For example: 'Y' = 'Y ' is true. DUMP('Y') = DUMP('Y ') is false.

    Or, you are right!
    "count (distinct dump(spousalplan))" counts null(s) too.


    Anyhow,
    I don't know DDL of insurance table and it's data.
    So, please publish your test results of
    select distinct dump(spousalplan) from insurance
    and/or
    select distinct spousalplan , dump(spousalplan) from insurance
    Last edited by tonkuma; 05-24-12 at 03:17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •