GROUP BY (MySQL vs SQL server)

Question

I am having some issues with the GROUP BY clause. I was originally taught SQL in MySQL and i have recently transferred over to SQL server. However, i am really struggling with the GROUP BY clause.

I am used to being able to create queries such as:

    SELECT Name,Team,Date, SUM(Figure) 
    FROM Table
    GROUP BY Name

This would give me a result such as:

    Name      Team     Date    Figure
    Ben        2       16/09     30
   Simon       4       16/09     55

But when i code the same thing in SQL server i have to GROUP BY all columns in my select clause otherwise it errors which gives me

    SELECT Name,Team,Date, SUM(Figure) 
    FROM Table
    GROUP BY Name,Team,Date

And result:

    Name      Team     Date    Figure
    Ben        2       16/09     10
    Ben        2       19/09     20
   Simon       4       16/09     35
   Simon       4       20/09     20

This is because it is grouping by name,team and date.

I am looking to get a result like i had in MySQL in SQL server but this doesn't seem possible. Does anyone have any advice for this situation?

Thanks

Answer 1

In SQL Server when you use GROUP BY all other columns involved will need to be aggregated properly. To achieve your results you need:

    SELECT Name,Team,MIN(Date), SUM(Figure) 
FROM Table
GROUP BY Name,Team

If you want the greater date you use MAX instead of MIN. I am not familiar with MySQL but your results shouldn't be possible without proper aggregation. Because how will MySql know which date to choose of the two and if there were different Teams for an individual, how will it know which to choose?