Fungsi agregat hanya dapat muncul di SELECT statement, COMPUTE/ COMPUTE BY dan HAVING Clause.
Contoh Fungsi agregat :
Contoh Fungsi agregat :
- AVG
- SUM
- MIN
- MAX
- COUNT
- GROUPING
- VAR
Fungsi agregat biasanya tidak mempedulikan NULL namun pada waktu tertentu dapat mengsilkan result yang tak terduga, oleh karena itu gunakan fungsi ISNULL untuk menggantikan nilai NULL menjadi 0.
contoh:
SELECT AVG (ISNULL([kolom yang akan di-aveg], 0)) AS '[nama kolom]' FROM [table]
Perbedaan Count(kolom) dengan Count(*) adalah Count (kolom) akan menghitung seluruh records dengan mengacuhkan yang bernilai NULL sehingga lebih tepat digunakan Count(*) untuk dapat mendapat menghitung records dengan tepat
LATIHAN
Task 1: Create and execute a query to generate a report that lists the
average order quantity and sum of line total for each product with a
line total that exceeds $1000000.00, and with average quantity less
than 3
• Create a query that returns the ProductID, average OrderQty and total of
LineTotal.
• Use a GROUP BY to ensure the averages are computed for the correct column
• Use a HAVING clause to restrict the query to run for cases where the total an
averages of LineTotal and OrderQty respectively are in the proper ranges.
• Don’t be afraid to use functions in the HAVING clause.
• Execute the query and review the results.
Task 2: Create and execute a query to group the products and then
compute the sum of the quantity shelf-wise
• Create a query that returns the ProductID Shelf and total Quantity for a group
of products.
• Use a GROUP BY with the ROLLUP operator to calculate the columns in a
specific order.
• Execute the query and review the results.
Task 3: Create and execute a query to generate a summary report
• Create a query that summarizes sales information from the
SalesOrderDetail table using the CUBE operator.
• Sum the number of orders.
• Specify the CUBE operator with the correct columns from the table (orders are
related to products and the id of the SalesOrderDetail row.
• Execute the query and review the results.
Task 4: Distinguish the rows generated by the summary or
aggregations and actual table rows
• Create a query that summarizes sales quotas and total YTD sales from the
SalesPerson table using the CUBE operator.
• Use the GROUPING operator in tandem with the CUBE operator to indicate
the column not being aggregated.
• Sum the SalesYTD column.
• Execute the query and review the results.
LATIHAN
Task 1: Create and execute a query to generate a report that lists the
average order quantity and sum of line total for each product with a
line total that exceeds $1000000.00, and with average quantity less
than 3
• Create a query that returns the ProductID, average OrderQty and total of
LineTotal.
• Use a GROUP BY to ensure the averages are computed for the correct column
• Use a HAVING clause to restrict the query to run for cases where the total an
averages of LineTotal and OrderQty respectively are in the proper ranges.
• Don’t be afraid to use functions in the HAVING clause.
• Execute the query and review the results.
select ProductID, avg (OrderQty) as 'Order Qty', SUM(LineTotal)as 'Line Total' from Sales.SalesOrderDetail
group by CUBE (ProductID)
having SUM (LineTotal) >= 1000000 and AVG (OrderQty) <= 3
Task 2: Create and execute a query to group the products and then
compute the sum of the quantity shelf-wise
• Create a query that returns the ProductID Shelf and total Quantity for a group
of products.
• Use a GROUP BY with the ROLLUP operator to calculate the columns in a
specific order.
• Execute the query and review the results.
SELECT ProductID, Shelf, SUM(Quantity) AS 'QtySum'
FROM Production.ProductInventory
GROUP BY ROLLUP(ProductID, Shelf)
Task 3: Create and execute a query to generate a summary report
• Create a query that summarizes sales information from the
SalesOrderDetail table using the CUBE operator.
• Sum the number of orders.
• Specify the CUBE operator with the correct columns from the table (orders are
related to products and the id of the SalesOrderDetail row.
• Execute the query and review the results.
SELECT SalesOrderID, ProductID,
SUM(OrderQty) AS SumQuantity
FROM Sales.SalesOrderDetail
GROUP BY CUBE(SalesOrderID, ProductID)
ORDER BY SalesOrderID, ProductID
Task 4: Distinguish the rows generated by the summary or
aggregations and actual table rows
• Create a query that summarizes sales quotas and total YTD sales from the
SalesPerson table using the CUBE operator.
• Use the GROUPING operator in tandem with the CUBE operator to indicate
the column not being aggregated.
• Sum the SalesYTD column.
• Execute the query and review the results.
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD',
GROUPING(SalesQuota) AS 'Grouping'
FROM sales.SalesPerson
GROUP BY CUBE(SalesQuota)
0 comments:
Post a Comment