# Thread: MAX average points by year and sex

1. Registered User
Join Date
Nov 2009
Posts
19

## Unanswered: MAX average points by year and sex

Hi everybody,

I have a table

Code:
```Name                                      Null?          Type
----------------------------------------- -------- ----------------------------
EESNIMI                                            VARCHAR2(24)
PERENIMI                                           VARCHAR2(24)
ISIKUKOOD                                          VARCHAR2(11)
MATEMAATIKA                                        NUMBER(3)
VOORKEEL                                           NUMBER(3)
EMAKEEL                                            NUMBER(3)```
I want to receive free values and two rows from that table - sex (man or woman), year and maximum average point by year.

Isikukood is a personal ID, where are 11 numbers. First number is 3 or 4. If 3 then man and if 4 then woman. Second 2 numbers are year of birth. And emakeel is the field where I want to calulate average grouped by the year. And after that get maximum of average grouped by the sex and year.

For example

Code:
```SEX          YEAR             MAXIMUM
-------    --------      --------------
3              82                    90
4              85                    94```
I've tried this query:
Code:
```SELECT ap.sugu AS SUGU, ap.aasta AS SYNNIAASTA, MAX(emakeel_kesk) AS MAXKESKMINE
FROM
(
SELECT SUBSTR(isikukood, 1, 1) AS sugu, SUBSTR(isikukood, 2, 2) AS aasta, AVG(emakeel) AS emakeel_kesk
WHERE SUBSTR(isikukood, 1, 1) IN ('3', '4')
GROUP BY SUBSTR(isikukood, 1, 1), SUBSTR(isikukood, 2, 2)
ORDER BY SUBSTR(isikukood, 1, 1), SUBSTR(isikukood, 2, 2)
) ap
GROUP BY ap.sugu, ap.aasta
ORDER BY ap.sugu;```
But this doesn't work. It returns all the rows and don't group by sex. The example result is:
Code:
```SEX YEAR MAXKESKMINE
- -- -----------
3 83  49.6153846
3 84  63.8139535
3 85  62.1834862
3 86        61.6
3 87        69.5
4 11           3
4 56          12
4 72          65
4 75          65
4 78          94
4 79          80```
How can I do this query?

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>Isikukood is a personal ID, where are 11 numbers. First number is 3 or 4. If 3 then man and if 4 then woman. Second 2 numbers are year of birth.

If this is real life production "design", the person responsible should be promoted to chimney sweep trainee.

Otherwise it is a decent homework assignment.

3. Registered User
Join Date
Nov 2009
Posts
19
yeah, in estonia each person have a ID, when you birth then you get it. It consist of 11 numbers. For example 38209093421. 3 - means a man, 82 is a year of birth, 09 is a month of birth and second 09 is a day of birth. Four last digits contains different information too. I don't get your chimney sweep trainee stuff...

If it's school homework and I've tried to get it work. It means you can't help me and not to make some path to the right answer?

4. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>yeah, in estonia each person have a ID, when you birth then you get it. It consist of 11 numbers.
>For example 38209093421. 3 - means a man, 82 is a year of birth, 09 is a month of birth and second 09 is a day of birth.
>Four last digits contains different information too

From my perspective, The SQL would be much simpler if "Identifier" were split into 3 separate fields:
Gender
Birthdate
Other_ID

I see little to be gained by concatenate into a single field.
You might want to consider CREATE VIEW to individually present each of these 3 as their own field.

5. Registered User
Join Date
Nov 2009
Posts
19
As you said, it's schoolwork and I can't chnage the table . What I can do, is a right query for a wrong table design.

6. Registered User
Join Date
Dec 2003
Posts
1,074
Could you add some clarification? Maximum Average?

Also, what criteria would designate the final 2 rows that you want to see in the results? The data, as you describe it when aggregated, will have 2 rows per year, one for each gender, with the "maximum average". Do you mean you are expecting 2 records for every year in the results?

This all makes sense to me so far

Code:
```create table kandidaadid
(isikukood varchar2(11),
emakeel   number(3)
);

insert into kandidaadid values ('38209093421', 70);
insert into kandidaadid values ('38210213421', 80);
insert into kandidaadid values ('48207063421', 90);

insert into kandidaadid values ('38309093421', 80);
insert into kandidaadid values ('48310213421', 90);
insert into kandidaadid values ('48307063421', 100);

SELECT SUBSTR(isikukood, 1, 1) AS sugu, SUBSTR(isikukood, 2, 2) AS aasta, AVG(emakeel) AS emakeel_kesk, MAX(emakeel) as mx
WHERE SUBSTR(isikukood, 1, 1) IN ('3', '4')
GROUP BY SUBSTR(isikukood, 1, 1), SUBSTR(isikukood, 2, 2)
ORDER BY SUBSTR(isikukood, 2, 2), SUBSTR(isikukood, 1, 1);

S AA EMAKEEL_KESK         MX
- -- ------------ ----------
3 82           75         80
4 82           90         90
3 83           80         80
4 83           95        100

4 rows selected.```
--=Chuck
Last edited by chuck_forbes; 11-16-09 at 12:22.

7. Registered User
Join Date
Nov 2009
Posts
19
I'm expecting one result per men and one result for women. Year is not important. And maximum of average. If there were to queries - then I take all men and women and take the average grouped by year. After that get maximum of results and display maximum average of men and maximum average for women.

8. Registered User
Join Date
Dec 2003
Posts
1,074
You're on the right track, then. Look at your GROUP BY clause in your outer query. You should only have 1 column listed, the one which refers to gender. And that means you have to remove the YEAR column from the SELECT (since it's no longer mentioned in the GROUP BY and it's not an aggregate expression)

Code:
```SELECT ap.sugu AS SUGU,  MAX(emakeel_kesk) AS MAXKESKMINE
FROM
(
SELECT SUBSTR(isikukood, 1, 1) AS sugu, SUBSTR(isikukood, 2, 2) AS aasta, AVG(emakeel) AS emakeel_kesk
WHERE SUBSTR(isikukood, 1, 1) IN ('3', '4')
GROUP BY SUBSTR(isikukood, 1, 1), SUBSTR(isikukood, 2, 2)
) ap
GROUP BY ap.sugu
ORDER BY ap.sugu;```

9. Registered User
Join Date
Nov 2009
Posts
19
This will result
Code:
```S    MAXKESK
- ----------
3         90
4         94```
But I need
Code:
```S AA    MAXKESK
- --- ----------
3  82        90
4  85        94```
Year is important in final output. I need to output sex, year and the maximum of average of results

10. Registered User
Join Date
Dec 2003
Posts
1,074
Like this

Code:
```WITH ap
AS (  SELECT   SUBSTR (isikukood, 1, 1) AS sugu,
SUBSTR (isikukood, 2, 2) AS aasta,
AVG (emakeel) AS emakeel_kesk
WHERE   SUBSTR (isikukood, 1, 1) IN ('3', '4')
GROUP BY   SUBSTR (isikukood, 1, 1), SUBSTR (isikukood, 2, 2))
SELECT   sugu, aasta, emakeel_kesk
FROM   ap
WHERE   (sugu, emakeel_kesk) IN (  SELECT   sugu, MAX (emakeel_kesk)
FROM   ap
GROUP BY   sugu)```
I like to use the WITH clause when I'm pulling in the same query repeatedly, but you could do the same as above with inline views.

Just a note, if there are multiple years where the MAX(AVG) are the same, for the same gender, then both records would appear.

--=Chuck

11. Registered User
Join Date
Nov 2009
Posts
19
Wow, this works like a charm. Can you say me how to do this with starting SELECT ...

We don't have learned WITH clause yet.

12. Registered User
Join Date
Dec 2003
Posts
1,074
I guess I'd go with this ... but are you learning Analytic Functions yet either?

Code:
```SELECT   sugu, aasta, emakeel_kesk
FROM   (SELECT   sugu,
aasta,
emakeel_kesk,
RANK () OVER (PARTITION BY sugu ORDER BY emakeel_kesk DESC) AS rnk
FROM   (  SELECT   SUBSTR (isikukood, 1, 1) AS sugu,
SUBSTR (isikukood, 2, 2) AS aasta,
AVG (emakeel) AS emakeel_kesk
WHERE   SUBSTR (isikukood, 1, 1) IN ('3', '4')
GROUP BY   SUBSTR (isikukood, 1, 1),
SUBSTR (isikukood, 2, 2)))
WHERE   rnk = 1```

13. Registered User
Join Date
Nov 2009
Posts
19
Noup . I don't even know what this RANK function will do. But I need to get from database only two MAX items. One for women and one for men. If there are for example in men's group two same values in different years, then I need to display only one...

14. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
Then try a subquery to get the max year and the query you already have to get the avg.
Dave

15. Registered User
Join Date
Dec 2003
Posts
1,074
Someone else can help you from here. I've given you 2 solutions. I probably shouldn't have invested so heavily in someone's homework assignment.

I never thought that it would have bugged me, but "Yes, that's a good answer, but I'm not going to use it. Next!" kind of riles me. I understand your perspective, but it's starting to cut into my time now, and you don't really *need* the answer to get real work done.

--=Chuck

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•