Puttaporn Junlong Puttaporn Junlong - 5 months ago 9
SQL Question

How to get the value of max() group when in subquery?

So i woud like to find the department name or department id(dpmid) for the group that has the max average of age among the other group and this is my query:

select
MAX(avg_age) as 'Max average age' FROM (
SELECT
AVG(userage) AS avg_age FROM user_data GROUP BY
(select dpmid from department_branch where
(select dpmbid from user_department_branch where
user_data.userid = user_department_branch.userid)=department_branch.dpmbid)
) AS query1


this code show only the max value of average age and when i try to show the name of the group it will show the wrong group name.

So, How to show the name of max group that has subquery from another table???

Answer

You may try this..

select MAX(avg_age) as max_avg, SUBSTRING_INDEX(MAX(avg_age_dep),'##',-1)  as max_age_dep from
(
    SELECT 
     AVG(userage) as avg_age, CONCAT( AVG(userage), CONCAT('##' ,department_name)) as avg_age_dep
     FROM user_data 
     inner join user_department_branch 
            on user_data.userid = user_department_branch.userid
     inner join department_branch 
            on department_branch.dpmbid = user_department_branch.dpmbid
     inner join department
            on department.dpmid = department_branch.dpmid
     group by department_branch.dpmid
) tab_avg_age_by_dep
;

I've done some change on ipothesys that the department name is placed in a "department" anagraphical table.. so, as it needed put in join a table in plus, then I changed your query, eventually if the department name is placed (but I don't thing so) in the branch_department table you can add the field and its treatment to your query

update

In adjunct to as said, if you wanto to avoid identical average cases you can furtherly make univocal the averages by appending a rownum id in this way:

select MAX(avg_age) as max_avg, SUBSTRING_INDEX(MAX(avg_age_dep),'##',-1)  as max_age_dep from
(
    SELECT 
     AVG(userage) as avg_age, CONCAT( AVG(userage), CONCAT('##', CONCAT( @rownum:=@rownum+1, CONCAT('##' ,department_name)))) as avg_age_dep
     FROM user_data 
     inner join user_department_branch 
            on user_data.userid = user_department_branch.userid
     inner join department_branch 
            on department_branch.dpmbid = user_department_branch.dpmbid
     inner join department
            on department.dpmid = department_branch.dpmid
     ,(SELECT @rownum:=0) r 
     group by department_branch.dpmid
) tab_avg_age_by_dep
;