CODEWITHSUNDEEP
sqlsql-servert-sql
solmaz ahamdi
solmaz ahamdi

Reputation: 150

Group By with MAX value from another column

Table FieldStudies is :

ID          Name
---|-----------------------|
1  | Industrial Engineering| 
2  | Civil Engineering     |
3  | Architecture          |
4  | Chemistry             |

And table Eductionals is :

ID  UserID  Degree  FieldStudy_ID
---|------|--------|------------|
1  | 100  | 3      | 4          | 
2  | 101  | 2      | 2          |
3  | 101  | 3      | 2          |
4  | 101  | 4      | 3          |
5  | 103  | 3      | 4          |
6  | 103  | 4      | 2          |

I want to find the number of students in each FieldStudies , provided that the highest Degree is considered.

Output desired:

ID          Name              Count
---|-----------------------|--------|
1  | Industrial Engineering|   0    |
2  | Civil Engineering     |   0    |
3  | Architecture          |   1    |
4  | Chemistry             |   2    |

I have tried:

select Temptable2.* , count(*) As CountField from
    (select fs.*
    from FieldStudies fs
    left outer join
        (select e.UserID , Max(e.Degree) As ID_Degree , e.FieldStudy_ID
        from Eductionals e
        group by e.UserID) Temptable
    ON fs.ID = Temptable.FieldStudy_ID) Temptable2
group by Temptable2.ID

But I get the following error :

Column 'Eductionals.FieldStudy_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If I understand correctly, you want only the highest degree for each person. If so, you can use row_number() to whittle down the multiple rows for a given person and the rest is aggregation and join:

select fs.id, fs.Name, count(e.id)
from fieldstudies fs left join
     (select e.*,
             row_number() over (partition by userid order by degree desc) as seqnum
      from educationals e
     ) e
     on e.FieldStudy_ID = fs.id and seqnum = 1
group by fs.id, fs.Name
order by fs.id;

Upvotes: 1

Related Questions