Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    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
           FROM kandidaadid
           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. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    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. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    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. #6
    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
    FROM kandidaadid
    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. #7
    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. #8
    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
           FROM kandidaadid
           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. #9
    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. #10
    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
                   FROM   kandidaadid
                  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. #11
    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. #12
    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
                            FROM   kandidaadid
                           WHERE   SUBSTR (isikukood, 1, 1) IN ('3', '4')
                        GROUP BY   SUBSTR (isikukood, 1, 1),
                                   SUBSTR (isikukood, 2, 2)))
     WHERE   rnk = 1

  13. #13
    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. #14
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Then try a subquery to get the max year and the query you already have to get the avg.
    Dave

  15. #15
    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
  •