Group By in SQL Sever with CUBE, ROLLUP and GROUPING SETS Examples

Create Sample SQL Server Database and Data

USE MASTER 
GO

CREATE DATABASE EmpTest
GO
USE EmpTest
GO
CREATE TABLE EmpSalary
(
id INT PRIMARY KEY IDENTITY(1,1),
EmpName varchar (200),
Department varchar(100),
Category char(1),
Salary money
)

INSERT EmpSalary
SELECT 'Bhavesh Patel','IT','A',$8000
UNION ALL
SELECT 'Alpesh Patel','Sales','A',$7000
UNION ALL
SELECT 'Kalpesh Thakor','IT','B',$5000
UNION ALL
SELECT 'Jay Shah','Sales','B',$4000
UNION ALL
SELECT 'Ram Nayak','IT','C',$3000
UNION ALL
SELECT 'Jay Shaw','Sales','C',$2000
SELECT * FROM EmpSalary

SQL Server GROUP BY Example

SELECT 
Department,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY Department

SELECT
Department,
Category,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY Department, Category

SQL Server GROUP BY with HAVING Example

SELECT 
Department,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY Department
HAVING SUM(salary) = 16000

SELECT
Department,
Category,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY Department, Category
HAVING SUM(salary) = 8000

SQL Server GROUP BY CUBE Example

SELECT 
Department,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY CUBE(Department)

SELECT
Department,
Category,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY CUBE(Department, Category)

SQL Server GROUP BY ROLLUP Example

SELECT 
Department,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY ROLLUP(Department)

SELECT
Department,
Category,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY ROLLUP(Department, Category)
SELECT 
Department,
SUM(Salary) as salary
FROM EmpSalary
GROUP BY ROLLUP(Department)

SELECT
Department,
Category,
SUM(Salary) as salary
FROM EmpSalary
GROUP BY ROLLUP (Category, Department)

SQL Server GROUP BY ROLLUP with GROUPING_ID Example

SELECT 
Department,
Category,
SUM(Salary) as Salary,
GROUPING_ID(Category, Department) as GroupingID
FROM EmpSalary
GROUP BY ROLLUP(Category, Department)

SQL Server GROUP BY GROUPING SETS Example

SELECT 
Department,
Category,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY GROUPING SETS(Category, Department,(Category, Department),())
SELECT 
Department,
Category,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY GROUPING SETS((Department, Category),())
SELECT 
Department,
Category,
SUM(Salary) as Salary
FROM EmpSalary
GROUP BY GROUPING SETS(CUBE(Department, Category), ROLLUP(Department, Category))

Summary

--

--

--

Yeni ol, önde ol. Eseriniz sizi yansıtsın!

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

PHP — P51: Visibility Modifiers

Visibility Modifiers in PHP. Public, Private, Protected.

How Much Do You Need to Know to Build an App?

what i have been waiting for has come !

Call for Submissions for the OpenMBEE Workshop 2020

Never “Never write tests for your app”

Use these code practices for efficient Python code

Why do programmers lose motivation easily?

Don’t scale out scale in!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Birol Topuz

Birol Topuz

Yeni ol, önde ol. Eseriniz sizi yansıtsın!

More from Medium

Python Programs for Beginner Bioinformatics — Part 4

CS50 — Mario-less

Introduction to Scratch

Impromptu with IK