DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the GROUP BY statement?

Execute the following Microsoft SQL Server 2008 T-SQL database scripts in Query Editor to demonstrate the use of the T-SQL GROUP BY clause with Aggregate Functions.

-- SQL GROUP BY clause with COUNT aggregate Quick Syntax - sql server select count *

SELECT Title, Employees = COUNT(*)

FROM AdventureWorks.HumanResources.Employee

GROUP BY Title

ORDER BY Employees DESC

/* Partial results

Title                   Employees

Sales Representative    14

Buyer                   9

Marketing Specialist    5

Scheduling Assistant    4

*/

 

-- SQL having statement - finding duplicates / multiple detail items
SELECT SalesOrderID, DetailLineItems = COUNT(*)
FROM AdventureWorks2008.Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING COUNT(*) > 1
ORDER BY SalesOrderID
/*    SalesOrderID      DetailLineItems

43659             12

43660             2

....      */

- T SQL SUM with GROUP BY - sql group by aggregate functions

SELECT   YEAR(OrderDate) AS 'Year',

         SUM(TotalDue)   AS 'Total Order Amount',

         '$'+ CONVERT(varchar,SUM(TotalDue),1) AS 'Total in Currency Format'

FROM     AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

ORDER BY Year

/*    Year        Total Order Amount      Total in Currency Format

2001        14327552.2263           $14,327,552.23

2002        39875505.095            $39,875,505.10

2003        54307615.0868           $54,307,615.09

2004        32196912.4165           $32,196,912.42   */

------------

-- SQL GROUP BY and CASE function - SUM aggregate with CASE expression

SELECT YEAR = YEAR(OrderDate),

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ,OrderDate) = 1 THEN Subtotal

                                    END),1),'') AS 'Q1',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 2 THEN Subtotal

                                    END),1),'') AS 'Q2',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 3 THEN Subtotal

                                    END),1),'') AS 'Q3',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 4 THEN Subtotal

                                    END),1),'') AS 'Q4'

FROM  AdventureWorks2008.Sales.SalesOrderHeader soh

WHERE YEAR(OrderDate) > 2001

GROUP BY YEAR(OrderDate)

ORDER BY YEAR(OrderDate)

/* YEAR     Q1          Q2                Q3                Q4

2002  6,678,449.12      7,430,122.29      12,179,372.04     9,798,486.39

2003  7,738,309.35      9,727,845.55      16,488,806.73     15,192,201.07

2004  12,824,418.47     16,262,217.91     50,840.63   */

------------

 

-- SQL Server group by statement - SQL GROUP BY clause
-- SQL GROUP BY on 2 columns -- sql group by inner join

USE AdventureWorks;

SELECT   Country = CountryRegionCode,

         StateProvince = StateProvinceCode,

         StaffCount = COUNT(* ) – aggregate function

FROM     HumanResources.EmployeeAddress ea

         INNER JOIN Person.Address a

           ON ea.AddressID = a.AddressID

         INNER JOIN Person.StateProvince sp

           ON sp.StateProvinceID = a.StateProvinceID

GROUP BY CountryRegionCode,

         StateProvinceCode

ORDER BY CountryRegionCode,

         StateProvinceCode;

        

/*  Partial results

Country     StateProvince     StaffCount

US          MN                2

US          OR                1

US          TN                1

US          UT                1

US          WA                275

*/

------------

------------

-- SQL GROUP BY half an hour - sql server order by half an hour

------------

-- Set GROUP BY interval half hour or other value like quarter hour (15 minutes)

DECLARE  @IntervalMinutes INT = 30

 

USE tempdb;

SELECT PurchaseOrderID,

       TotalDue,

       OrderDate = DATEADD(MINUTE,CHECKSUM(PurchaseOrderID),OrderDate)

INTO   POH   -- Generate test data with SELECT INTO table create

FROM   AdventureWorks2008.Purchasing.PurchaseOrderHeader

 

-- MSSQL group by half an hour time only without date

SELECT   Period = Convert(VARCHAR,DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',

                   OrderDate) / @IntervalMinutes) * @IntervalMinutes,

                                          '19000101'),108),

         TotalPurchase = SUM(TotalDue) -- SUM aggregate function

FROM     POH

GROUP BY Convert(VARCHAR,DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',

                   OrderDate) / @IntervalMinutes) * @IntervalMinutes,

                                 '19000101'),108)

ORDER BY Period;

/* Partial results:

Period      TotalPurchase

21:00:00    991821.1719

21:30:00    1047153.5762

22:00:00    969689.2412

22:30:00    1072308.1789

23:00:00    1145267.5879

23:30:00    890941.4027

*/

-- T-SQL group by half an hour for each order date

SELECT   Period = DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',

                   OrderDate) / @IntervalMinutes) * @IntervalMinutes,

                                          '19000101'),

         TotalPurchase = SUM(TotalDue) -- SQL SUM aggregate function

FROM     POH

GROUP BY DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',

                   OrderDate) / @IntervalMinutes) * @IntervalMinutes,

                                 '19000101')

ORDER BY Period;

GO

/* Partial results

Period                        TotalPurchase

2004-03-15 08:00:00.000       133756.1379

2004-03-15 08:30:00.000       186778.3909

2004-03-16 08:30:00.000       162812.8586

2004-03-16 18:30:00.000       5036.1465

*/

DROP TABLE tempdb.dbo.POH

------------

Related links:

GROUP BY (Transact-SQL)

How to Use GROUP BY in SQL Server

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE