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

Scope of variables in Dynamic SQL - SQL Server Vs MySQL‏

I have already posted about how Dynamic SQL works in SQL Server and MySQL at http://www.sqlservercurry.com/2012/08/dynamic-sql-sql-server-vs-mysql.html

There is a significant difference between SQL Server and MySQL as far as the scope of variables is concerned. The variable declared and accessed in Dynamic SQL can be accessed out of Dynamic SQL in MySQL, whereas this is not possible in SQL Server

Consider the following set of data

create table testing(id int, names varchar(100))
insert into testing(id,names)
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3'

MySQL

The purpose is to assign a value to a variable in dynamic sql and access the same variable out of dynamic sql

set @sql:='set @count:=(select count(*) from testing);';
prepare stmt from @sql;
execute stmt ;
select @count;

In the above code, the variable @count is declared and assigned in dynamic sql. But after dynamic sql is executed using the prepare statement, the variable is still accessible. The statement select @count returns the value 3

SQL Server

Create the same table testing in SQL Server. Now execute the following code

declare @sql varchar(8000)
set @sql='
    declare @count int
    set @count=(select count(*) from testing)
    '
execute (@sql)
select @count

You will get the following error

Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@count".

The code before select @count will get executed correctly. But the variable declared in dynamic sql cannot be accessed out of it. If you access it in same dynamic scope like below, it will work

declare @sql varchar(8000)
set @sql='
    declare @count int
    set @count=(select count(*) from testing)
    select @count
'
execute (@sql)
 
So you need to be aware of this behavior when using Dynamic SQL.

Date Functions – SQL Server vs MySQL

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see some Date Functions in SQL Server and MySQL. I have put them in a table format so that it becomes easier for you to follow:

 

SNo	Purpose	SQL Server	MySQL
1	Find out current date and time	Getdate()	now()
2	Find out day name of current date	datename(weekday ,getdate())	Dayname(now())
3	Find out day of month	datename(day,getdate())	dayofmonth(now())
4	Find out day of week	datepart(weekday,getdate())	dayofweek(now())
5	Find out day of year	datepart(weekday,getdate())	dayofyear(now())
6	Find out year value	year(getdate())	year(now())
7	Find out month value	month(getdate())	month(now())
8	Find out day value	day(getdate())	day(now())
9	Find out last day of month	eomonth(getdate())	last_day(now())
10	Find out month name	datename(month,getdate())	monthname(now())
11	Find out Time value from seconds	cast(dateadd(second,seconds_value,0) as time)	sec_to_time(seconds_value)
12	Subtract days from date	dateadd(day,-day_value,getdate())	date_sub(now(), interval day_value day)
13	Add days to date	dateadd(day,day_value,getdate())	adddate(now(), interval day_value day)
13	Get UTCDATE	GETUTCDATE ()	UTC_TIMESTAMP()
14	Format a date (dd-mm-yyyy)	format(getdate(),'d-MM-yyyy')	DATE_FORMAT(now(), '%d-%c-%Y');

Pagination - SQL Server Vs MySQL‏

Continuing my series on how same things can be done differently in SQL Server and MySQL, this week we will see how Pagination works in SQL Server and MySQL. Pagination is a method of showing result in small batches if the results return a large set of data.

Consider the following set of 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

Let us assume that you want to return the first 5 rows from the results ordered by sales_date

SQL Server

Version 2012 onwards you can use OFFSET and FETCH NEXT Clauses

select * from testing
order by sales_date
offset 0 rows
fetch next 5 rows only

The above code orders the results by ascending order of sales_date and fetches 5 rows. The next 5 rows can be returned using the following code

select * from testing
order by sales_date
offset 5 rows
fetch next 5 rows only

The FETCH clause skips the first 5 rows and returns the next five rows


MySQL

The same functionality can be done in MySQL using the LIMIT clause

select * from testing
order by sales_date
LIMIT 0,5

The above code returns first five rows in ascending order of sales_date. To get next five rows, use the following code

select * from testing
order by sales_date
LIMIT 5,10

Arithmetic Operations - SQL Server Vs MySQL

Arithmetic operations done in SQL Server and MySQL may not give the same result. Integer divisions always result in an integer in SQL Server whereas in MySQL, it results to a decimal. Let’s see this with examples.

SQL Server

Run the following code

SELECT 5/2

The result is 2 and not 2.5 This is because both 5 and 2 are Integers and the end result is also converted to the data type of integer. So the actual value 2.5 becomes 2 when implicitly converted to an integer datatype.

SELECT 1/0

This results to the error Divide by zero error because any number divided by zero is infinity.

SELECT 'a'/10

The above throws an error "Conversion failed when converting the varchar value 'a' to data type int."
Now let’s observe arithmetic operations in MySQL, given the same set of data.

MySQL

Run the following code

SELECT 5/2

The result is 2.5 Although both 5 and 2 are of integer datatypes, MySQL results to decimal datatype during the division

SELECT 1/0

MySQL returns NULL for the above select statement. It won't give Divide by Zero error

SELECT 'a'/10

The above returns 0 and not an error.

Hope these tips were useful and you should keep them in mind when doing arithmetic calculations in MySQL

Row set Concatenation - SQL Server vs MySQL

Row set concatenation is a frequently required feature. Based on identical value, other unique values should be concatenated.

Consider the following set of data

create table test(id int, names varchar(100))
insert into test(id,names)
select 1,'Suresh' union all
select 1,'Kumar' union all
select 1,'Nithil' union all
select 2,'John' union all
select 2,'Murugan'

SQL Server

We can use FOR XML PATH in SQL Server as shown below

declare @names varchar(8000)
set @names=''
select distinct id,
    stuff((select (','+@names+names) from test as t2 where t1.id=t2.id for xml path('')),1,1,'') as names
from
    test as t1

The FOR XML PATH concatenates the names for each id and the STUFF function removes the first comma from a list

MySQL

MySQL has a built-in function named GROUP_CONCAT()

select id,group_concat(names) from test
group by id

This built-in function concatenates the names for each id. Simple!

Stored Procedure - SQL Server vs MySQL

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how Stored Procedures are supported in both SQL Server and MySQL. We also explore the difference in calling them.

Consider the following code assuming that you have a database named test

SQL Server

Create procedure proc_test
as
select 'Hello World from SQL Server'

GO

The above code creates a stored procedure named proc_test in your database. To execute the procedure, you need to use either EXEC or EXECUTE keywords

EXEC proc_test

This statement will display the result Hello World in SQL Server

MySQL

DELIMITER $$;
DROP PROCEDURE IF EXISTS `test`.`proc_test`$$
CREATE PROCEDURE `test`.`proc_test` ()

BEGIN   
    select 'Hello World from MySQL' as message;

END$$

DELIMITER ;$$

The above code creates a stored procedure named proc_test in the database named test. To execute the procedure, you need to use the keyword CALL

CALL proc_test()

which will display the result Hello World in MySQL.

Note that in MySQL, delimiters are important for creating a stored procedure and the procedure name should be succeed by empty brackets () if there are no parameters.

Dynamic SQL - SQL Server Vs MySQL‏

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how to use dynamic sql

Suppose you want to pass a table name as a parameter and get all rows from it. Create the following table

create table testing(id int, names varchar(100))

insert into testing(id,names)
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3'

SQL Server

declare @table_name varchar(100)
set @table_name='testing'
exec('select * from '+@table_name)

The above code accepts table name as value and select all the rows from it.

MySQL

set @table_name:='testing';
set @sql:=concat('select * from ',@table_name);
prepare st from @sql;
execute st ;

The variable @sql will have the select statement with table name concatenated. The prepare statement prepares the dynamic sql and execute statement executes the statement.

Bulk Insert - SQL Server vs MySQL

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how bulk insert can be done in SQL Server vs MySQL.

We often need to import data from a text file to the server. Consider that the file D:\test.txt has data for three columns and  you want to import this data into a table

In SQL Server we can use the BULK INSERT command

In MySQL we can user Load Data Command

load data local infile 'D:/test.txt' into table test
fields     escaped by '\\'
    terminated by '\,'
    lines terminated by '\n'

Here both of them consider comma as a field separator and new line as the line separator

Note : By Default SQL Server looks for the existence of the file in Server's directory. In MySQL, we need to specify keyword local to instruct that file is available in the local system

SET and SELECT - SQL Server Vs MySQL‏

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see the usage of SET and SELECT commands in SQL Server vs MySQL.

SET and SELECT commands can be used to assign values to the variables. But the usage is different in SQL Server and MySQL.

In SQL Server, SET can be used to assign a value to single variable only. SELECT command can be used to assign values to multiple variables.

Consider the following examples

Declare @a int, @b int
set @a=1
set @b=2
select @a,@b

The following will also work

Declare @a int, @b int
select @a=1,@b=2
select @a,@b

OUTPUT



In MySQL, declaration is not needed. Any number of variables can be assigned using a single SET command

set @a:=1, @b:=2;
select @a,@b

The SELECT command can be used to assign values and select the values too. The following is equal to the previous code

select @a:=1, @b:=2;

The above command assigns values to the variables and also returns values assigned

OUTPUT

Temporary Tables - SQL Server vs MySQL

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see temporary table support in SQL Server vs MySQL.

We may often need to create a temporary table while processing data to provide a workspace for storing intermediate results. Both SQL Server and MySQL support temporary tables.

In SQL Server, all temporary tables should be prefixed by the # sign

Consider this table

create table #test
(
id int,
names varchar(100)
)

insert into #test(id, names)
select 1,'test'
select * from #test

We can drop this table by using a DROP command

DROP table #test

In MySQL, we have to use the keyword 'temporary' when creating a temporary table

Consider the following code

create temporary table if not exists test
(
id int,
names varchar(100)
)

insert into test(id, names)
select 1,'test'
select * from test

The above creates a temporary table called test in the current session if it is not already available. To drop a temporary table in MySQL, we can use the following code

drop temporary table test

Dense Rank - MySQL vs SQL Server

                                                                                    
Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how to implement Dense Rank in SQL Server vs MySQL.

Generating a dense_rank is a common requirement when showing resultsets. In SQL Server, starting from version 2005, we can make use of the dense_rank() function. Dense_rank() will generate the serial number for each set of values and keep the same number if the value is duplicated

Consider the following set of data

create table test(names varchar(100))
insert into test
select 'Suresh' union all
select 'Ramesh' union all
select 'Kant' union all
select 'Jerald' union all
select 'Clara' union all
select 'Ramesh' union all
select 'Kant' union all
select 'Jerald' union all
select 'John'



SQL Server

Using the dense_rank()  function, we can generate a serial number and reset for each name

select dense_rank() over (order by names) as sno,names from test

MySQL

Using a variable, we can generate the serial number, and use another variable that keeps same value for duplicates

set @sno:=0;
set @names:='';
select @sno:=case when @names=names then @sno else @sno+1 end as sno,@names:=names as names from test
order by names;

In the above example, variable @sno gets incremented by 1 for each set of values thus keeping the same value for duplicates.

Reset Row Number For Each Group - SQL Server Vs MySQL

Continuing on my SQL Server vs MySQL series, we will see how same things can be done differently in SQL Server and MySQL

Generating a row number  or a serial number and resetting it on each group is a common requirement when showing result sets. In SQL Server, starting from version 2005, we can make use of the row_number() function with the partition clause

Consider the following set of data

SQL Server


Using the row_number() function, we can generate the serial number and reset for each names

select row_number() over (partition by names order by names) as sno,names from test


MySQL


Using a variable, we can generate the serial number, and use another variable that resets first variable for each group

set @sno:=0;
set @names:='';


OUTPUT

Generate Row Number – SQL Server vs MySQL

In this series, we will see how same SQL tasks can be achieved differently in SQL Server and MySQL. Generating a row number  or a serial number is a common requirement when showing the resultsets.

In SQL Server, starting from version 2005, we can make use of the row_number() function

Consider the following set of data

SQL Server


Using the row_number() function we can generate a serial number as follows:

select row_number() over (order by names) as sno,names from test

MySQL


Using a variable in MySQL, we can generate a serial number as follows:

set @sno:=0;
select @sno:=@sno+1 as sno,names from test
order by names;


In the above example, variable @sno gets incremented by 1 for each row.

Stay tuned for more on MySQL vs SQL Server posts.