SQL Question 17

Problem

  • USE Northwind
  • Write an inline table UDF that takes a CategoryID and returns the CategoryName, ProductName, Count of orders and Sum of Orders for each product in that category

Code

USE Northwind
GO
IF OBJECT_ID('fnProductsbyCategory','FN') IS NOT NULL
DROP FUNCTION fnProductsbyCategory
GO
CREATE FUNCTION fnProductsbyCategory(@Categoryid int)
RETURNS TABLE
AS
RETURN
(
SELECT CategoryName
, ProductName
, Count(Distinct orderid) 'Count_of_Orders'
, CONVERT(varchar(10),Sum(od.quantity*od.unitprice)) 'Sum_of_Orders'
FROM [Order Details] od JOIN Products P ON od.productid = p.productid
JOIN categories c ON p.categoryid = c.categoryid
WHERE p.categoryid = @Categoryid
GROUP BY c.CategoryName, p.ProductName
)
-- Test the function
GO
SELECT * from dbo.fnProductsbyCategory(2)

Result

Question 17 Result

Question 17 Result

Notes

This user defined function returns an inline table -- notice the RETURNS TABLE before the AS followed by RETURN. The only parameter is the categoryID number declared as @CategoryID. The function returns the CategoryName, ProductName, DISTINCT count of orders, and sum of orders. Notice the CONVERT on the 'Sum_of_Orders'. The SELECT and FROM statement tests the function.

 

No Comments Yet.

Leave a comment