articles

Home / DeveloperSection / Articles / Union Example in SQL Server

Union Example in SQL Server

Chris Anderson6331 08-Oct-2014

In this article I am going to explain a use of union operator in SQL Server Database with a real life scenario and example.

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement with the UNOIN must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Before moving towards union query, let understand what is our scenario. Suppose we have three tables (Product, Customer and Order) in our database and we want to see the number of orders by month name, week days and between three hours gaps in day.

Let’s move towards our example:

1-Product table structure and data. This table is not necessary to create for this example if you already have a ProductId in other table, to relate with Order table.

Union Example in SQL Server

2-Customer table structure and data. This table is also not necessary to create for this example if you already have a CustomerId in other table, to relate with Order table.

Union Example in SQL Server

3-Order table structure and data. This table is necessary specially OrderDate column of this table because we are going to use OrderDate column in all our further queries which shows us use of union operator in real scenario.

Union Example in SQL Server

4-First union query which show the orders (number of orders received by month) result by month name:

Select [Month], [Orders] From ( 
      Select 'January' as [Month], 1 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 1
            Union
      Select 'Febuary' as [Month], 2 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 2
            Union
      Select 'March' as [Month], 3 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 3
            Union
      Select 'April' as [Month], 4 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 4
            Union
      Select 'May' as [Month], 5 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 5
            Union
      Select 'June' as [Month], 6 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 6
            Union
      Select 'July' as [Month], 7 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 7
            Union
      Select 'August' as [Month], 8 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 8
            Union
      Select 'September' as [Month], 9 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 9
            Union
      Select 'October' as [Month], 10 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 10
            Union
      Select 'November' as [Month], 11 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 11
            Union
      Select 'December' as [Month], 12 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 12
) Results Order by MonthNum

 

Union Example in SQL Server

 

Second union query which show the orders (number of orders received by week day) result by week day:

Select [DayofWeek], [Orders] From ( 
      Select 'Sunday' as [DayofWeek], 1 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 1
            Union
      Select 'Monday' as [DayofWeek], 2 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 2
            Union
      Select 'Tuesday' as [DayofWeek], 3 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 3
            Union
      Select 'Wednesday' as [DayofWeek], 4 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 4
            Union
      Select 'Thursday' as [DayofWeek], 5 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 5
            Union
      Select 'Friday' as [DayofWeek], 6 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 6
            Union
      Select 'Saturday' as [DayofWeek], 7 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 7
) Results Order by [WeekNum]

 

Union Example in SQL Server

Third union query which show the orders (number of orders received by gap of 3 hours in a day) result by gap of 3 hours in a day:

Select [TimeofDay], [Orders] From ( 
      Select '12 AM to 03 AM' as [TimeofDay], 1 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '00:00:00' and '03:00:00'
            Union
      Select '03 AM to 06 AM' as [TimeofDay], 2 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '03:00:00' and '06:00:00'
            Union
      Select '06 AM to 09 AM' as [TimeofDay], 3 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '06:00:00' and '09:00:00'
            Union
      Select '09 AM to 12 PM' as [TimeofDay], 4 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '09:00:00' and '12:00:00'
            Union
      Select '12 PM to 03 PM' as [TimeofDay], 5 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '12:00:00' and '15:00:00'
            Union
      Select '03 PM to 06 PM' as [TimeofDay], 6 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '15:00:00' and '18:00:00'
            Union
      Select '06 PM to 09 PM' as [TimeofDay], 7 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '18:00:00' and '21:00:00'
            Union
      Select '09 PM to 12 AM' as [TimeofDay], 8 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '21:00:00' and '00:00:00'
) Results Order by [TimeNum]

Union Example in SQL Server

Thanks for reading this article. Please suggest me how can I improve this one and other alternative way of doing the similar scenario.


Updated 07-Sep-2019
hi I am software developer at mindstick software pvt. ltd.

Leave Comment

Comments

Liked By