GroupBy Clause - SQL Server vs MySQL

A GROUP BY Clause is used to group the data based on specific columns along with summary information. However there are some differences in usage of this clause in SQL Server and MySQL

Let us create this testing table with some sample data

create table testing
(
    sales_id int,
    product_id char(7),
    sales_date datetime,
    sales_amount decimal(12,2)
)

insert into testing(sales_id,product_id,sales_date,sales_amount)
select 1,'PR00001','2001-01-01',1200.00 union all
select 2,'PR00002','2003-01-21',3000.50 union all
select 3,'PR00002','2003-01-21',2500.00 union all
select 4,'PR00001','2002-02-15',1000.00 union all
select 5,'PR00003','2005-12-19',3000.50 union all
select 6,'PR00003','2006-11-01',8000.00 union all
select 7,'PR00004','2007-04-22',350.00 union all
select 8,'PR00004','2007-04-22',590.00 union all
select 9,'PR00004','2007-04-22',590.00 union all
select 10,'PR00001','2008-05-27',4800.50

If you want to get total sales amount for each product, you can write this query both in SQL Server and MySQL

select product_id,sum(sales_amount) as sales_amount from testing
group by product_id

As per ANSI SQL, all columns that are not part of aggregate functions should be included in GROUP BY clause

If you run the following code in SQL Server

select product_id,sum(sales_amount) as sales_amount from testing

You will get an error

Msg 8120, Level 16, State 1, Line 1
Column 'testing.product_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

However if you run this code in MySQL, you will get the following result

Product_id        sales_amount
-----------        --------------
PR00001        25031.50

Because MySQL does the auto grouping for the columns specified in the SELECT statement, if they are omitted in GROUP BY clause, it just simply displays the first value of columns along with total of summary column. In this case, it displays the first product id and total of all products

The following is also possible in MySQL

select *,sum(sales_amount) as sales_amount from testing

As explained, it will list out all columns of first row along with total of sales_amount. You need to aware of this feature in MySQL while using GROUP BY Clause

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan