CODEWITHSUNDEEP
sqlsql-servergroup-bymax
ah2Bwise
ah2Bwise

Reputation: 132

Why does MAX statement require a Group By?

I understand why the first query needs a GROUP BY, as it doesn't know which date to apply the sum to, but I don't understand why this is the case with the second query. The value that ultimately is the max amount is already contained in the table - it is not calculated like SUM is. thank you

-- First Query
select
    sum(OrderSales),OrderDates
From Orders


-- Second Query
select
    max(FilmOscarWins),FilmName
From tblFilm

Upvotes: 0

Views: 323

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

If you want the film with the most Oscar wins, then use select top:

select top (1) f.*
From tblFilm f
order by FilmOscarWins desc;

In an aggregation query, the select columns need to be consistent with the group by columns -- the unaggregated columns in the select must match the group by.

Upvotes: 0

IMSoP
IMSoP

Reputation: 97783

It is not the SUM and MAX that require the GROUP BY, it is the unaggregated column.

If you just write this, you will get a single row, for the maximum value of the FilmOscarWins column across the whole table:

        select
            max(FilmOscarWins)
        From
            tblFilm

If the most Oscars any film won was 12, that one row will say 12. But there could be multiple films, all of which won 12 Oscars, so if we ask for the FilmName alongside that 12, there is no single answer.

By adding the Group By, we fundamentally change the query: instead of returning one number for the whole table, it will return one row for each group - which in this case, means one row for each film.

If you do want to get a list of all those films which had the maximum 12 Oscars, you have to do something more complicated, such as using a sub-query to first find that single number (12) and then find all the rows matching it:

select
     FilmOscarWins,
     FilmName
From
     tblFilm
Where FilmOscarWins = (
        select
            max(FilmOscarWins)
        From
            tblFilm
)

Upvotes: 5

Related Questions