1

ok so , i have a sql query puzzles .

say that i have a table "PERSON" with the data look sumthing like this:

PERSON

    Name              ID
    -----------------------
    John              12123
    Kimberly          33311
    Leo               87892

And i have a table score that look sumthing like this:

SCORE

    ID                 MARKS
    ------------------------
    12123              3.40
    33311              3.75
    12123              3.56
    87892              2.34
    33311              3.75
    12123              3.24
    87892              2.34
    87892              3.42
    33311              3.75

*above is just a sample data, just the structure i wanted to show

And now the question is, how do i retrieve a query of

"All the name of person that their marks AVERAGE is below 3.5 without passing any parameters"

meaning that,

    12123              3.40
    12123              3.56
    12123              3.24

    average of 12123 is equal to 3.4 correct?
    but 12123 is the parameters 

i need to retrieve ALL names..

because for me it is impossible to do it without passing a parameters..or maybe anyone of you have any suggestion?

Edited by WaltP: Not a C question

2
Contributors
1
Reply
5
Views
4 Years
Discussion Span
Last Post by rch1231
0

To work a query like this you have to first select and group the data (name and averages) and then use the HAVING option to select only the entries you want from the selected results. It is not exact but should look something like this:

select 
Person.Name,
Avg(Score.Marks) as Average_Score
from Person 
inner join Score on Person.ID = Score.ID
group by Person.Name
Having Average_score < 3.5
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.