Old 04-23-2017, 05:33 AM   #1
OPI
Human being with feelings
 
OPI's Avatar
 
Join Date: Nov 2014
Posts: 1,900
Default Need some help on an un-related sql programming question

So for class i need to make a "filter" that shows people below a certain average. In this case the average is 200. What do i need to do in order to do this?

heres the example for reference http://forum.cockos.com/attachment.p...1&d=1492950723

Thanks for any help or tips
Attached Images
File Type: png prog.png (54.8 KB, 273 views)
OPI is offline   Reply With Quote
Old 04-23-2017, 08:33 AM   #2
uncleswede
Human being with feelings
 
Join Date: Feb 2015
Posts: 1,096
Default

Quote:
Originally Posted by OPI View Post
So for class i need to make a "filter" that shows people below a certain average. In this case the average is 200. What do i need to do in order to do this?

heres the example for reference http://forum.cockos.com/attachment.p...1&d=1492950723

Thanks for any help or tips
Quote:
Originally Posted by OPI View Post
So for class i need to make a "filter" that shows people below a certain average. In this case the average is 200. What do i need to do in order to do this?

heres the example for reference http://forum.cockos.com/attachment.p...1&d=1492950723

Thanks for any help or tips
Blimey... Haven't written sql for many years but I think you need to use a SELECT - GROUP BY - HAVING Construct. Something like:


Select max(name) group by rating having avg(rating) < 200 where rating is not null
uncleswede is offline   Reply With Quote
Old 04-23-2017, 01:30 PM   #3
OPI
Human being with feelings
 
OPI's Avatar
 
Join Date: Nov 2014
Posts: 1,900
Default

Quote:
Originally Posted by uncleswede View Post
Blimey... Haven't written sql for many years but I think you need to use a SELECT - GROUP BY - HAVING Construct. Something like:


Select max(name) group by rating having avg(rating) < 200 where rating is not null
Thanks uncleswede.

Okay so I misunderstood the initial task. Here's what I actually need! Sorry

I think this is the right construction. But I have to filter all the people with rating less than the average . So in the having clause I should have something like rating < avg(rating). But this is not correct in SQL. Make any sense?
OPI is offline   Reply With Quote
Old 04-23-2017, 01:56 PM   #4
uncleswede
Human being with feelings
 
Join Date: Feb 2015
Posts: 1,096
Default

Quote:
Originally Posted by OPI View Post
Thanks uncleswede.

Okay so I misunderstood the initial task. Here's what I actually need! Sorry

I think this is the right construction. But I have to filter all the people with rating less than the average . So in the having clause I should have something like rating < avg(rating). But this is not correct in SQL. Make any sense?
Hmmm.... Now you're testing me... You might need a sub query as the argument. Something like:

Select max(name) group by rating having avg(rating) < (select avg(rating) where rating is not null) where rating is not null

I think you'll need the 'where rating is not null' clauses because nulls screw up the AVG calculation
uncleswede is offline   Reply With Quote
Old 04-23-2017, 02:29 PM   #5
OPI
Human being with feelings
 
OPI's Avatar
 
Join Date: Nov 2014
Posts: 1,900
Default

Okay that totally makes sense.. buutt

Here's what I'm getting.

http://imgur.com/pGp7Gd5

Not sure why but it's giving me an error
OPI is offline   Reply With Quote
Old 04-23-2017, 11:07 PM   #6
uncleswede
Human being with feelings
 
Join Date: Feb 2015
Posts: 1,096
Default

Quote:
Originally Posted by OPI View Post
Okay that totally makes sense.. buutt

Here's what I'm getting.

http://imgur.com/pGp7Gd5

Not sure why but it's giving me an error
Not sure of Oracle syntax (was a sql server programmer) but, on reflection, I may have over complicated my answer.

Try

Select name
From customers as customer1
Where rating is not null and
rating is < (select avg(rating) from customer as customer2 where dating is not null)
uncleswede is offline   Reply With Quote
Old 04-24-2017, 02:37 AM   #7
pettrer
Human being with feelings
 
pettrer's Avatar
 
Join Date: Apr 2017
Location: Borås, Sweden
Posts: 13
Default

Quote:
Originally Posted by uncleswede View Post
Not sure of Oracle syntax (was a sql server programmer) but, on reflection, I may have over complicated my answer.

Try

Select name
From customers as customer1
Where rating is not null and
rating is < (select avg(rating) from customer as customer2 where dating is not null)
Hi,

Yes, that looks about right (it should be and rating < (select...) though, not an is there). Love the dating part.

Note that you need a semicolon at the end to follow the ISO standard.
__________________
Creating music and writing code are true nine-to-five jobs: nine PM to five AM.
pettrer is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -7. The time now is 05:15 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.