—SQL grand total simple, quick syntax - isnull is used to make report readable
SELECT isnull(convert(VARCHAR,ProductID),'GRAND TOTAL') AS ProductID,
sum(Quantity) AS 'Total In Inventory'
FROM AdventureWorks2008.Production.ProductInventory
WHERE ProductID >= 800
GROUP BY ProductID WITH ROLLUP
ORDER BY ProductID;
GO
/* Partial results
ProductID Total In Inventory
996 970
997 153
998 155
999 194
GRAND TOTAL 65709
*/
------------
-- SQL sales subtotal, total, grand total - simple logic - month, year, all times
SELECT isnull(convert(VARCHAR,Year(OrderDate)),'GRAND TOTAL') AS YEAR,
CASE
WHEN YEAR(OrderDate) IS NOT NULL
THEN isnull(convert(VARCHAR,Month(OrderDate)),'YEARLY TOTAL')
ELSE ''
END AS MONTH,
-- SQL currency formatting
'$'+CONVERT(varchar,SUM(TotalDue),1) AS [MONTHLY SALES SUBTOTAL]
FROM AdventureWorks2008.Sales.SalesOrderHeader
GROUP BY Year(OrderDate),
Month(OrderDate) WITH ROLLUP
ORDER BY YEAR,
MONTH
/* Partial results
YEAR MONTH MONTHLY SALES SUBTOTAL
2004 4 $4,722,890.74
2004 5 $6,518,825.23
2004 6 $6,728,034.99
2004 7 $56,178.92
2004 YEARLY TOTAL $32,196,912.42
GRAND TOTAL $140,707,584.82
*/
------------
------------
-- Microsoft SQL grand total, total, subtotal generation with T-SQL GROUP BY ROLLUP
------------
-- MSSQL grouping function
USE AdventureWorks2008;
SELECT TotalType = CASE
WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4)) = 1
THEN 'GRAND TOTAL'
WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1)) = 1
THEN 'TOTAL - YY'
WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) +
right('0' + CONVERT(VARCHAR,month(OrderDate)),2)) = 1
THEN 'SUBTOTAL - QQ'
ELSE ''
END,
[Year] = ISNULL(left(CONVERT(CHAR(8),OrderDate,112),4),''),
[Quarter] = ISNULL(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1),
''),
[Month] = COALESCE(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) + right('0' +
CONVERT(VARCHAR,month(OrderDate)),2),
''),
Purchases = '$' + CONVERT(VARCHAR,SUM(SubTotal),1)
FROM Purchasing.PurchaseOrderHeader
GROUP BY left(CONVERT(CHAR(8),OrderDate,112),4),
left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1),
left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) + right('0' +
CONVERT(VARCHAR,month(OrderDate)),2) WITH ROLLUP
ORDER BY GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4)),
[Year],
GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1)),
[Quarter],
GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + 'Q' +
CONVERT(CHAR(1),(month(OrderDate) - 1) / 3 + 1) + right('0' +
CONVERT(VARCHAR,month(OrderDate)),2)),
[Month]
/* Results
TotalType Year Quarter Month Purchases
2001 2001Q2 2001Q205 $103,895.82
SUBTOTAL - QQ 2001 2001Q2 $103,895.82
TOTAL - YY 2001 $103,895.82
2002 2002Q1 2002Q101 $299,239.98
2002 2002Q1 2002Q102 $700,406.63
2002 2002Q1 2002Q103 $328,572.46
SUBTOTAL - QQ 2002 2002Q1 $1,328,219.07
2002 2002Q2 2002Q204 $646,975.84
2002 2002Q2 2002Q205 $298,745.12
2002 2002Q2 2002Q206 $106,491.00
SUBTOTAL - QQ 2002 2002Q2 $1,052,211.96
2002 2002Q3 2002Q307 $655,405.08
2002 2002Q3 2002Q308 $116,119.65
2002 2002Q3 2002Q309 $499,186.65
SUBTOTAL - QQ 2002 2002Q3 $1,270,711.38
2002 2002Q4 2002Q410 $186,121.65
2002 2002Q4 2002Q411 $388,868.98
SUBTOTAL - QQ 2002 2002Q4 $574,990.63
TOTAL - YY 2002 $4,226,133.03
2003 2003Q1 2003Q103 $131,485.79
SUBTOTAL - QQ 2003 2003Q1 $131,485.79
2003 2003Q2 2003Q205 $641,097.40
2003 2003Q2 2003Q206 $924,127.80
SUBTOTAL - QQ 2003 2003Q2 $1,565,225.19
2003 2003Q3 2003Q307 $105,014.51
2003 2003Q3 2003Q308 $98,304.85
2003 2003Q3 2003Q309 $5,100,678.73
SUBTOTAL - QQ 2003 2003Q3 $5,303,998.10
2003 2003Q4 2003Q410 $3,449,392.92
2003 2003Q4 2003Q411 $1,775,204.15
2003 2003Q4 2003Q412 $3,424,287.10
SUBTOTAL - QQ 2003 2003Q4 $8,648,884.17
TOTAL - YY 2003 $15,649,593.25
2004 2004Q1 2004Q101 $4,266,022.62
2004 2004Q1 2004Q102 $4,137,584.18
2004 2004Q1 2004Q103 $4,704,085.56
SUBTOTAL - QQ 2004 2004Q1 $13,107,692.37
2004 2004Q2 2004Q204 $5,172,365.20
2004 2004Q2 2004Q205 $5,729,967.42
2004 2004Q2 2004Q206 $5,522,963.61
SUBTOTAL - QQ 2004 2004Q2 $16,425,296.24
2004 2004Q3 2004Q307 $6,710,223.01
2004 2004Q3 2004Q308 $6,824,989.46
2004 2004Q3 2004Q309 $743,151.66
SUBTOTAL - QQ 2004 2004Q3 $14,278,364.13
2004 2004Q4 2004Q410 $1,020.00
SUBTOTAL - QQ 2004 2004Q4 $1,020.00
TOTAL - YY 2004 $43,812,372.74
GRAND TOTAL $63,791,994.84
*/
------------
Second
complex example: summary query generates Subtotal, Total and Grand
Total for monthly, yearly and all times sales. In data warehousing, the
dates are referred to as date (or time) dimension with 3 levels.
------------
-- GRAND TOTAL (all times), TOTAL (year), SUBTOTAL (month) mssql select query
------------
-- Microsoft T-SQL derived table rpt generates the report - select from select
-- The outer query does the final filtering and sorting
USE AdventureWorks;
SELECT *
FROM
(
SELECT YY=COALESCE(CONVERT(varchar,YEAR(OrderDate)),''),
MM=COALESCE(LEFT(CONVERT(varchar,OrderDate,111),7),''),
ORDERS = COUNT(*),
SALES = '$'+CONVERT(varchar,SUM(TotalDue),1),
GRPMM = CASE WHEN
GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 0
AND GROUPING(YEAR(OrderDate)) = 1
THEN 'SUBTOTAL' ELSE '' END,
GRPYY= CASE WHEN GROUPING(YEAR(OrderDate)) = 0
AND GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1
THEN 'TOTAL' ELSE '' END,
GRPALL = CASE WHEN
GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1
AND GROUPING(YEAR(OrderDate)) = 1
THEN 'GRAND TOTAL' ELSE '' END
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), LEFT(CONVERT(varchar,OrderDate,111),7)
WITH CUBE
) rpt
WHERE
GRPMM != '' OR GRPYY !='' OR GRPALL !=''
ORDER BY
CASE WHEN GRPALL!= '' THEN 3
WHEN GRPYY != '' THEN 2
ELSE 1 END,
YY, MM
GO
/* Partial results
YY MM ORDERS SALES GRPMM GRPYY GRPALL
2004/04 2127 $4,268,473.54 SUBTOTAL
2004/05 2386 $5,813,557.45 SUBTOTAL
2004/06 2374 $6,004,155.77 SUBTOTAL
2004/07 976 $56,178.92 SUBTOTAL
2001 1379 $12,693,250.63 TOTAL
2002 3692 $34,463,848.44 TOTAL
2003 12443 $47,171,489.55 TOTAL
2004 13950 $28,887,306.04 TOTAL
31464 $123,215,894.65 GRAND TOTAL
*/
------------
Third complex example: the MonthlyOrderSummary
stored procedure will generate Account Number Total (subtotal), Day Total (total)and Month Total
(grand total) columns.
------------
-- T-SQL grand total, total, subtotal stored procedure
------------
------------
-- T-SQL create stored procedure
USE AdventureWorks2008;
GO
CREATE PROCEDURE MonthlyOrderSummary
-- ALTER PROCEDURE MonthlyOrderSummary
@Year INT,
@Month INT
AS
BEGIN
SELECT soh.AccountNumber,
PO = isnull(soh.PurchaseOrderNumber,''),
convert(CHAR(10),soh.OrderDate,111) AS 'Order Date',
soh.TotalDue,
CASE
WHEN SalesOrderID = (
SELECT TOP 1 SalesOrderID
FROM Sales.SalesOrderHeader
WHERE convert(CHAR(10),OrderDate,111) =
convert(CHAR(10),soh.OrderDate,111)
ORDER BY SalesOrderID DESC) THEN (SELECT '$' +
convert(VARCHAR,sum(TotalDue),1)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID <= soh.SalesOrderID
AND convert(CHAR(11),OrderDate,111) =
convert(CHAR(10),soh.OrderDate,111))
ELSE ' '
END AS 'Day Total',
CASE
WHEN SalesOrderID = (
SELECT TOP 1 SalesOrderID
FROM Sales.SalesOrderHeader
WHERE year(OrderDate) = @Year
AND month(OrderDate) = @Month
ORDER BY OrderDate DESC) THEN (SELECT '$' +
convert(VARCHAR,sum(TotalDue),1)
FROM Sales.SalesOrderHeader
WHERE year(OrderDate) = @Year
AND month(OrderDate) = @Month)
ELSE ' '
END AS 'Month Total'
FROM Sales.SalesOrderHeader soh
WHERE year(OrderDate) = @Year
AND month(OrderDate) = @Month
ORDER BY SalesOrderID
END
GO
-- MSSQL execute stored procedure with 2 parameters
EXEC MonthlyOrderSummary 2003, 7
GO
/* Partial results - TotalDue is SUBTOTAL
Day Total is TOTAL
Month Total is GRAND TOTAL
AccountNumber PO Order Date TotalDue Day Total Month Total
10-4030-011069 2003/07/30 2563.589
10-4030-011101 2003/07/30 2696.189
10-4030-026306 2003/07/30 830.2307
10-4030-024421 2003/07/30 2703.9903
10-4030-022398 2003/07/30 596.689 $23,476.44
10-4030-019435 2003/07/31 2715.3497 $4,681,520.64
*/
------------
Sourse : sqlusa
No comments:
Post a Comment