Denis Szoke Denis Szoke - 1 year ago 127
MySQL Question

How do I select max from a count?

I was trying to select the Band with the highest Albums number, as an error it shows

Unknown column 'N_Albums' in 'field list'

What shoud I do in order to see the name of the band and its albums number?

select Name, max(N_Abums) as `Albums`
from (select a.Name, count(b.CodDisc) as `Nr`
from `S8.Band` a join `S8.Album` b
where a.CodBand = b.CodBand
group by a.CodBand) as Test

Answer Source

Change N_Albums to Nr.

You aliased the count with Nr, so that's what you have to take the max() of.

select Name, max(Nr) as Albums
from (
    select a.Name, count(b.CodDisc) as Nr
    from S8.Band a
    join S8.Album b on a.CodBand = b.CodBand
    group by a.CodBand
) as Test

I also improved your join syntax and removed all backticks as they were unnecessary.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download