User Defined Function with a sub query

Question

Create a user defined function named XXRepeatCustomer (where the XX are your initials). The function is to have one input parameter. Use the INT datatype for the input parameter. When the function is executed it is to return a three column table (CustFirstName, CustLastName, and Phone) for customers that placed a number of orders greater than or equal to the number passed in via the input parameter.

In order to receive a total number of orders placed I have joined together the Customer, and CustOrder tables. The problem only wants me to show the first, last, and phone of each customer but not the total of orders. I'm struggling with assigning the @orders parameter, and counting the total amount of orders in the sub query.

CREATE FUNCTION dbo.JERepeatCustomer
(@orders INT)
RETURNS TABLE AS
RETURN (SELECT CustFirstName, CustLastName, Phone 
FROM Customer C JOIN CustOrder CO
ON C.CustomerID = CO.CustomerID
WHERE @orders <= OrderID AND OrderID = (SELECT COUNT (DISTINCT OrderID) FROM CustOrder)
GROUP BY CustFirstName, CustLastName, Phone)

I expect the user to enter a 7, or any number, and the results show only the customers who have ordered 7, or more.

You may want to look into the keyword HAVING (learn.microsoft.com/en-us/sql/t-sql/queries/…) — Ryan Wilson
– Ryan Wilson, Commented Jun 21, 2019 at 14:39

dfundako · Accepted Answer · 2019-06-21 14:49:48Z

1

The keyword you need is HAVING. HAVING is similar to WHERE. WHERE will filter returned rows based on a specific value in that column, while HAVING will filter rows based on an aggregated value in the column.

For example, you have a customer table, and in your orders table, you have all the orders for each customer.

DECLARE @input INT = 7
SELECT ct.customer, ct.phone, COUNT(ot.orderID)
FROM customertable ct
INNER JOIN ordertable ot
ON ct.customerID = ot.customerID 

GROUP BY ct.customer, ct.phone
HAVING COUNT(ot.OrderID) >= @input

edited Jun 21, 2019 at 14:49

answered Jun 21, 2019 at 14:39

dfundako

8,3643 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Over a year ago

The example the teacher provided showed a sub query being used to solve a similar problem. With that being said, I assumed I needed one too. I didn't even think about the HAVING function. Thank you so much for the quick response, you helped me tremendously.

huMpty duMpty · Accepted Answer · 2019-06-21 14:44:18Z

0

SELECT CustFirstName, CustLastName, Phone 
FROM Customer C 
     CORSS APPLY (
             SELECT COUNT(*) AS Orders
             FROM CustOrder CO
             WHERE  C.CustomerID = CO.CustomerID) CustomerOrders
Where CustomerOrders.Orders >= @orders

answered Jun 21, 2019 at 14:44

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

1 Comment

Josh Over a year ago

This code worked for me as well, but my teacher has not taught us CROSS APPLY yet, I'm not sure if he would be impressed with my coding, or curious as to how I figured it out..haha! Thank you for your help :)

Collectives™ on Stack Overflow

User Defined Function with a sub query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related