Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    16

    Red face Unanswered: very stuck on my newbie query

    Hi

    I'm very stuck with this query, any help would be much appreciated i'm very new to SQL. Basically I need the query to give me a list of hospitals that have less than a given number of patients (for example less than 2) within a certain state (for example 001).

    My current attempt is:

    select count(hospital_no)quantity,hospital_no
    from Records_table
    where state_no = '001'
    and where quantity < '2'
    group by species_no

    but I cant see where I am going wrong, I think i may need to add a subquery somehow? In the above code, 'quantity' is a name given to the result of the count as shown. I have tried to reference it later to say give me all records with less than 2 for quantity but it doesnt take it.

    I am using SQL Query Analyzer for this project.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Try this,

    select species_no, count(*)
    from Records_table
    where state_no = '001'
    group by species_no
    having count(*) < 2
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You were close. Try this:

    Code:
    SELECT Count(hospital_no) Quantity, Hospital_no
    FROM records_table
    WHERE state_no = '001'
    GROUP BY hospital_no
    HAVING Count(hospital_no) > 2;
    JoeB

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    "> 2" ?

    Would it not be <= 2?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Yes, kinda fat-fingered that one. Good catch.

  6. #6
    Join Date
    Dec 2003
    Posts
    16
    that is excellent, thanks so much for your help!

Posting Permissions

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