Showing posts with label Chỉ Có Thể Là GROUP BY. Show all posts
Showing posts with label Chỉ Có Thể Là GROUP BY. Show all posts

Tuesday, January 27, 2015

Sử dụng với Group By .

-- 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
------------

Source : sqlusa
Posted by at No comments:
Labels:

Update dữ liệu với GROUP BY .

-- SQL GROUP BY Statement Quick Syntax - SQL group by count - group by clause
SELECT Color            = COALESCE(Color,'N/A'), -- grouping column
       ColorCount       = COUNT(* ),             -- aggregate function
       AvgListPrice     = AVG(ListPrice)         -- aggregate function
FROM   AdventureWorks2008.Production.Product
GROUP BY Color
ORDER BY Color
/*    Color       ColorCount  AvgListPrice
Black       93          725.121
Blue        26          923.6792
Grey        1           125.00
………
*/
------------

-- SQL Update Inner Join Quick Syntax - Update using two tables
UPDATE pod  SET pod.ModifiedDate = poh.ModifiedDate
FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader poh
  INNER JOIN AdventureWorks2008.Purchasing.PurchaseOrderDetail pod
    ON poh.PurchaseOrderID = pod.PurchaseOrderID
WHERE poh.PurchaseOrderID >= 4000
------------

------------
-- TSQL UPDATE table with GROUP BY Quick Syntax
------------
-- SQL GROUP BY in Update Statement - update using group by & aggregate function
-- SQL select into table create for demo - T-SQL update query with GROUP BY
SELECT DISTINCT YEAR=YEAR(OrderDate), Sales=CONVERT(money,0.0)
INTO tempdb.dbo.RevenueByYear
FROM AdventureWorks2008.Sales.SalesOrderHeader
GO
UPDATE rby SET rby.Sales = g.Total  -- update using table aliases in FROM clause
FROM tempdb.dbo.RevenueByYear rby   -- SQL UPDATE INNER JOIN
INNER JOIN ( SELECT YEAR=YEAR(OrderDate), Total=SUM(TotalDue) -- SUM aggregate
             FROM AdventureWorks2008.Sales.SalesOrderHeader
             GROUP BY YEAR(OrderDate) ) g   -- GROUP BY on year aliased as "g"
ON rby.Year = g.YEAR
GO
SELECT *, SalesCurrency='$'+CONVERT(varchar,Sales,1) FROM tempdb.dbo.RevenueByYear
ORDER BY YEAR
/*
YEAR  Sales             SalesCurrency
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
*/
DROP TABLE tempdb.dbo.RevenueByYear
GO
------------
USE tempdb;
SELECT Color=ISNULL(Color,'N/A'), ItemCount=0 INTO ProductColor
FROM AdventureWorks2008.Production.Product
GROUP BY Color
GO
-- TSQL update from group by - SQL UPDATE JOIN- SQL update table from group by syntax
UPDATE pc
SET pc.ItemCount = cg.ProductColorCount
FROM ProductColor pc
INNER JOIN (SELECT Color=ISNULL(Color,'N/A'), ProductColorCount=COUNT(*)
            FROM AdventureWorks2008.Production.Product
            GROUP BY Color) cg
ON pc.Color = cg.Color
GO
SELECT * FROM ProductColor
GO
/*
Color             ItemCount
N/A               248
Black             93
Blue              26
Grey              1
Multi             8
Red               38
Silver            43
Silver/Black      7
White             4
Yellow            36
*/
DROP TABLE tempdb.dbo.ProductColor
GO
------------
-- T-SQL group by syntax - group by sum aggregate - group by crosstab query
SELECT YEAR = YEAR(OrderDate),
       COALESCE(CONVERT(VARCHAR,SUM(CASE
                        WHEN DATEPART(QQ,OrderDate) = 1 THEN TotalDue
                                    END),1),'') AS 'Quarter 1',
       COALESCE(CONVERT(VARCHAR,SUM(CASE
                        WHEN DATEPART(QQ, OrderDate) = 2 THEN TotalDue
                                    END),1),'') AS 'Quarter 2',
       COALESCE(CONVERT(VARCHAR,SUM(CASE
                        WHEN DATEPART(QQ, OrderDate) = 3 THEN TotalDue
                                    END),1),'') AS 'Quarter 3',
       COALESCE(CONVERT(VARCHAR,SUM(CASE
                        WHEN DATEPART(QQ, OrderDate) = 4 THEN TotalDue
                                    END),1),'') AS 'Quarter 4'
FROM  AdventureWorks2008.Sales.SalesOrderHeader soh
GROUP BY YEAR(OrderDate)
ORDER BY YEAR(OrderDate)
/* Query results

YEAR  Quarter 1         Quarter 2         Quarter 3         Quarter 4
2001                                      5,850,932.95      8,476,619.28
2002  7,379,686.31      8,210,285.17      13,458,206.13     10,827,327.49
2003  8,550,831.87      10,749,269.37     18,220,131.53     16,787,382.31
2004  14,170,982.55     17,969,750.95     56,178.92  
*/
------------
In the following section, four UPDATE with GROUP BY methods are presented:
derived table, correlated subquery, CTE and subselect.
USE AdventureWorks
GO

-- FIRST SOLUTION: update with group by using a DERIVED TABLE- update from a Group By Clause

 -- Add a new column to table
ALTER TABLE HumanResources.Department
ADD NoOfEmployees int null
GO

-- Update the new column from the GROUP BY derived query DG
-- DG JOINed with the update table - update group clause
-- Inner join group by - sql server update group by - SQL update using join
-- SQL update group by - MSSQL update table with group by
UPDATE DE
SET NoOfEmployees =DG.DeptEmployees
FROM HumanResources.Department DE
INNER JOIN (SELECT D.DepartmentID, COUNT(*) AS DeptEmployees
      FROM HumanResources.EmployeeDepartmentHistory EDH
      JOIN HumanResources.Department D
            ON d.DepartmentID = EDH.DepartmentID
            and EDH.EndDate is null
      JOIN HumanResources.Employee E
            ON EDH.EmployeeID = E.EmployeeID
      GROUP BY D.DepartmentID ) DG
ON DE.DepartmentID = DG.DepartmentID
GO

-- Check results
SELECT * FROM HumanResources.Department
GO

-- Cleanup - remove column from table
ALTER TABLE HumanResources.Department
DROP COLUMN NoOfEmployees
GO

-- SECOND SOLUTION: update with group by applying CORRELATED SUBQUERY

-- Add a new column to table
ALTER TABLE HumanResources.Department
ADD NoOfEmployees int null
GO

-- Update the new column from the GROUP BY correlated subquery
-- the WHERE clause defines the correlation
-- SQL group by within update
UPDATE DE
SET NoOfEmployees = (SELECT COUNT(*) AS DeptEmployees
      FROM HumanResources.EmployeeDepartmentHistory EDH
      JOIN HumanResources.Department D
            ON d.DepartmentID = EDH.DepartmentID
            and EDH.EndDate is null
      JOIN HumanResources.Employee E
            ON EDH.EmployeeID = E.EmployeeID
      WHERE DE.DepartmentID = D.DepartmentID
      GROUP BY D.DepartmentID )
FROM HumanResources.Department DE
GO

-- Check results
SELECT * FROM HumanResources.Department
GO

-- Cleanup - remove just-added column
ALTER TABLE HumanResources.Department
DROP COLUMN NoOfEmployees
GO

------------
-- SQL update group by example for PO and Sales Order counts by year
------------
USE tempdb;
GO
CREATE TABLE AWSummary (
ProfitCenterID int,
FiscalYear int,
POs int,
PODetails int,
SalesOrders int,
SalesOrderDetails int
)
GO

-- SQL insert with select
-- The SELECT uses two group by derived tables
-- Profit Center 1 is the entire company
-- The JOIN assumption is that the [Year]-s are matching
-- SQL inner join group by
INSERT AWSummary (ProfitCenterID, FiscalYear, POs, SalesOrders)
SELECT 1, po.[Year], POCount, SOCount
FROM
(
SELECT  [Year]=YEAR(OrderDate), POCount=COUNT(*)
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
GROUP BY YEAR(OrderDate)
) po
INNER JOIN
(
SELECT  [Year]=YEAR(OrderDate), SOCount=COUNT(*)
FROM AdventureWorks.Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
) so
ON po.[Year] = so.[Year]
ORDER BY po.[Year]
GO

-- SQL update with group by for Purchase Orders Detail
-- SQL derived table join is used for the update
UPDATE AWSummary
      SET PODetails = Details
FROM AWSummary s
INNER JOIN
(SELECT [Year]= YEAR(OrderDate), Details= COUNT(*)
FROM AdventureWorks.Purchasing.PurchaseOrderHeader poh
INNER JOIN AdventureWorks.Purchasing.PurchaseOrderDetail pod
on poh.PurchaseOrderID = pod.PurchaseOrderID
GROUP BY YEAR(OrderDate)
) d
ON d.Year = s.FiscalYear
WHERE s.ProfitCenterID = 1
GO

-- SQL update with group by for Sales Orders Detail
-- SQL derived table is used for the update
-- SQL update with join
UPDATE AWSummary
      SET SalesOrderDetails = Details
FROM AWSummary s
INNER JOIN
(SELECT [Year]= YEAR(OrderDate), Details= COUNT(*)
FROM AdventureWorks.Sales.SalesOrderHeader soh
INNER JOIN AdventureWorks.Sales.SalesOrderDetail sod
on soh.SalesOrderID = sod.SalesOrderID
GROUP BY YEAR(OrderDate)
) d
ON d.Year = s.FiscalYear
WHERE s.ProfitCenterID = 1
GO

-- Check results 
SELECT FiscalYear, POs, PODetails,  SalesOrders, SalesOrderDetails
FROM AWSummary
WHERE ProfitCenterID=1
ORDER BY FiscalYear
GO
/* Results

FiscalYear  POs         PODetails   SalesOrders SalesOrderDetails
2001        8           15          1379        5151
2002        272         629         3692        19353
2003        1035        2344        12443       51237
2004        2697        5857        13951       45576
*/

-- Cleanup
DROP TABLE AWSummary
GO
------------

-- THIRD SOLUTION: update group by using a CTE - Common Table Expression

ALTER TABLE HumanResources.Department
ADD NoOfEmployees int null
GO

-- Update the new column from the GROUP BY cte
-- cte JOINed with the update table
-- SQL Server update group by
;WITH cteDeptCount AS
(SELECT D.DepartmentID, COUNT(*) AS DeptEmployees
      FROM HumanResources.EmployeeDepartmentHistory EDH
      JOIN HumanResources.Department D
            ON d.DepartmentID = EDH.DepartmentID
            and EDH.EndDate is null
      JOIN HumanResources.Employee E
            ON EDH.EmployeeID = E.EmployeeID
      GROUP BY D.DepartmentID )
UPDATE DE
SET NoOfEmployees =DC.DeptEmployees
FROM HumanResources.Department DE
INNER JOIN cteDeptCount DC
ON DE.DepartmentID = DC.DepartmentID
GO

-- Check results
SELECT * FROM HumanResources.Department
GO

-- Cleanup - remove just-added column
ALTER TABLE HumanResources.Department
DROP COLUMN NoOfEmployees
GO
------------

-- FOURTH SOLUTION: update with SUBSELECT and GROUP BY

------------
-- SQL update with subselect and group by
------------
USE tempdb;
-- SQL select into create table for example
SELECT DISTINCT   SupervisorName=LastName+', '+FirstName,
                  SupervisorID=e2.EmployeeID, DirectlySupervise = 0
INTO DirectSupervisor
FROM AdventureWorks.HumanResources.Employee e1
INNER JOIN AdventureWorks.HumanResources.Employee e2
      ON e1.ManagerID = e2.EmployeeID
INNER JOIN AdventureWorks.Person.Contact c
      ON e2.ContactID = c.ContactID
ORDER BY SupervisorName
GO
-- SELECT * FROM DirectSupervisor
-- (47 row(s) affected)

SELECT TOP 5 * FROM DirectSupervisor ORDER BY NEWID()
GO
/* Partial results

SupervisorName                SupervisorID      DirectlySupervise
Kleinerman, Christian         49                0
Brown, Jo                     16                0
Kim, Shane                    159               0
Kane, Lori                    197               0
Wright, A. Scott              44                0
*/
-- SQL update with subselect
-- SQL Server update with subselect
UPDATE DirectSupervisor
SET DirectlySupervise = (SELECT COUNT(*)
                        FROM AdventureWorks.HumanResources.Employee e
                        WHERE E.ManagerID = DirectSupervisor.SupervisorID)
GO
-- (47 row(s) affected)

SELECT TOP 5 * FROM DirectSupervisor ORDER BY NEWID()
GO
/* Partial results

SupervisorName                      SupervisorID      DirectlySupervise
Krebs, Peter                        21                22
Sánchez, Ken                        109               6
Barreto de Mattos, Paula            30                5
Bradley, David                      6                 8
Welcker, Brian                      273               3
*/

-- Equivalent update with group by - SQL Server inner join group by
-- SQL Server update with group by
UPDATE dr
SET DirectlySupervise = sc.StaffCount
FROM DirectSupervisor dr
INNER JOIN (SELECT e.ManagerID, StaffCount=COUNT(*)
            FROM AdventureWorks.HumanResources.Employee e
            GROUP BY ManagerID) sc
      ON sc.ManagerID = dr.SupervisorID
GO

-- Cleanup
DROP TABLE tempdb.dbo.DirectSupervisor
GO
------------

Source : sqlusa
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)