Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: I need help please, this annoying query is killing me

    Hi all, I need a little help with the query below:

    SELECT COUNT(*), o.suburb
    FROM lesson l,learner c, office o
    WHERE l.learnerno = c.learnerno
    AND o.officeno = c.officeno
    GROUP BY o.suburb,l.learnerno
    ORDER BY 4

    THE RESULT IS

    COUNT(*) SUBURB
    ------------------------------
    11 Gold valley
    10 Gold valley
    10 Gold valley
    9 Nedlands
    4 Nedlands
    12 Nedlands
    11 Perth
    11 Perth
    10 Perth
    17 Woodington
    2 Woodington
    10 Woodington


    How can modify the query above to get the minimum COUNT(*) number in each suburb just like this:

    COUNT(*) SUBURB
    --------------------------
    2 Woodington
    4 Nedlands
    10 Perth
    10 Gold valley


    I tried "SELECT MIN(COUNT(*)),...."
    but i get ORA-00937: not a single-group group function

    Tried "HAVING COUNT(*) = MIN(COUNT(*))"
    I get ORA-00935: group function is nested too deeply

    Any help will be appreciated.
    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post CREATE TABLE for table & INSERT for test data
    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
    Mar 2007
    Posts
    623
    What about using your query as a subquery?
    Code:
    select min(<alias for COUNT(*) in your subquery>), suburb
    from (<your query with aliased COUNT(*)>)
    group by suburb;
    Probably you could use analytic COUNT function instead of that count alias. Just giving the idea what could be aggregated by SUBURB:
    Code:
    select suburb, learnerno, count(*) over (partition by suburb, learnerno)
    from <your tables>
    where <join conditions>
    Analytic functions are described in SQL Reference book, which is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
    For more details, please study it.
    Last edited by flyboy; 10-08-11 at 11:56. Reason: rephrased first sentence

  4. #4
    Join Date
    Oct 2011
    Posts
    2

    Thank you

    Thanks everybody, flyboy I've got working now like this

    Code:
    SELECT MIN(cnt), suburb
    FROM (SELECT COUNT(*) as cnt, o.suburb
             FROM lesson l,learner c, office o
             WHERE l.learnerno = c.learnerno
             AND o.officeno = c.officeno
             GROUP BY o.suburb,l.learnerno)
    GROUP BY suburb
    However, I want to add some more columns in the select statement to display
    (in addition to the suburb and min(cnt)) but it comes back with errors

    for example:

    SELECT MIN(cnt), suburb, learnerno, firstname...

    Thank you very much...

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >it comes back with errors
    while above is likely true, it is 100% devoid of any useful detail.

    my car has error.
    tell me how to make my car go.

    please use COPY & PASTE so we can see exactly what you do & how Oracle actually responds!
    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.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by greaterror View Post
    However, I want to add some more columns in the select statement to display
    (in addition to the suburb and min(cnt)) but it comes back with errors

    for example:

    SELECT MIN(cnt), suburb, learnerno, firstname...
    Of course, as it is not present in the inner query, it is unknown there.
    After placing them into the inner query (assuming that LEARNERNO is unique in LEARNER table and all added columns are from that table), another question arises: as the main query is aggregate of multiple rows, which of those column values shall be taken? For example, there are 10 learners in Perth, but you may put only one value into each column. Which one?
    After you answer this, use any aggregation function you like. They are also described in SQL Reference book.
    Last edited by flyboy; 10-08-11 at 14:35. Reason: added assumption

Posting Permissions

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