October 23, 2011 at 9:34 pm
The test table contains the following 3 columns
DriverID (a unique identifier for a person in the test table. This DriverID
will repeat for every disposition the person has in the test table)
Fname (First Name)
Lname (Last Name)
Disposition (Possible values = "Guilty" or "Not Guilty")
1. There is one thing wrong with this query. What is it?
select fname,lname,disposition as verdict,COUNT(*) as cnt
from test
group by fname,disposition
having COUNT(*) > 2
2. There are 2 things wrong with this query. What are they and if fixed, what will the results mean?
select fname,lname,disposition as verdict,COUNT(*) as cnt
from test
where lname in (select lname,count(*)
from test
where SUBSTRING(DOB,1,4) = '1977'
and disposition = 'Guilty'
group by lname
having COUNT(9) > 1
)
group by fname,disposition
having COUNT(*) > 1
3. What is this query calculating the average of?
select AVG(cnt)
from (
select DriverID,cast(COUNT(9) as float) cnt
from test a
where exists (select 1 from test
where DriverID = a.DriverID
and disposition='Guilty'
)
group by DriverID
) a
October 24, 2011 at 1:40 am
select fname,lname,disposition as verdict,COUNT(*) as cnt
from test
group by fname,disposition
having COUNT(*) > 2
u need group by on lname.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply