Let's say that I have a table called Patients and in this table there's a column age. I want to know who are the oldest patients, the youngest patients and the average age among them.
So, given the table:
Patients
Name: Text
Age: Integer
- Who are the oldest patients in the db?
For this I'm doing:
select name, age from patients
where age = (select max(age) as 'HighestAges' from patients)
group by name;
This way I was able to retrieve every patient taht have the higher ages (if the is more than one patient with the same age in the result).
- Who are the youngest patients?
Well, what I need to do is just change the aggregate function and I'll get the expected result, right? So I did:
select name, age from patients
where age = (select min(age) as 'LowestAges' from patients)
group by name;
And I retrieved any patient who have the lowest ages.
- What is the average age among all patients?
I just select the average age and nothing more:
select avg(age) as 'AverageAge' from patients;
So far so good, now here's the big deal: how do I show the result of these 3 queries in a single result set?
What i want to achieve is a result set like this:
Name HighestAges Name LowestAges AverageAge
Rosemary 62 Tomalino 22 42
Mat 62 Rocat 22 42
You might be thinking "what a stupid result is that?" and you are right, it's just as stupid as it seems and that's why I want to do it, because it's stupid and no one would do it this way. I know there might be thousands of ways of doing it, I want to hear (read) all the ideas. I'm just doing it for fun, I'm learning SQL so I'm not expirienced with it. I learned o lot about full joins, outter joins, inner joins and self joins, I've tried to do them all for about 2 days, but I couldn't get done with it by myself so I'm seeking for help.
Thank you in advance.
Aucun commentaire:
Enregistrer un commentaire