lundi 27 juin 2016

How to join the result of different aggregate function in the same query?

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

  1. 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).

  1. 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.

  1. 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