very stuck on my newbie query

simple_simon · #1 (**permalink**) 12-16-03, 10:44

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.

r123456 · #2 (**permalink**) 12-16-03, 11:01

Try this,

select species_no, count(*)
from Records_table
where state_no = '001'
group by species_no
having count(*) < 2

joebednarz · #3 (**permalink**) 12-16-03, 11:08

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

r123456 · #4 (**permalink**) 12-16-03, 11:12

"> 2" ?

Would it not be <= 2?

joebednarz · #5 (**permalink**) 12-16-03, 19:31

Yes, kinda fat-fingered that one. Good catch.

simple_simon · #6 (**permalink**) 12-17-03, 05:24

that is excellent, thanks so much for your help!

Internet Services

Web Development

Digital Marketing

Consumer Tech