-- 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
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
-- 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
------------
Source : sqlusa
No comments:
Post a Comment