A few things i need help with...plz

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A few things i need help with...plz

Author

Topic

HCGamer13
Starting Member

1 Post

Posted - 2011-10-23 : 23:21:20

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-24 : 00:00:52

Sorry, we don't do homework / assignment here.

Please attempt it yourself and if you have any specific question after that, we will gladly answer that.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-10-24 : 02:26:20

1 You should include lname in the Group by clause
2 Get hint from point 1
3 Reab aout AVG function in SQL Server file

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 04:16:53

quote:
Originally posted by HCGamer13

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

why dont you try running this in sql management studio and start with the error you're getting in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources