locked
SQL subquery problems working out how to do it RRS feed

  • Question

  • User-1001988596 posted

    Hi

    I have a sql query and function which works as it returns the data I require, but I need to sum the data from the function and I cannot work out how to do it.

    Can anyone help

    ALTER FUNCTION [dbo].[AllItemsSold] 
    (
    	-- Add the parameters for the function here
    	@ProductID nvarchar(50),	
    	@Quantity decimal(18,2),
    	@CurrentBalance decimal(18,2),
    	@Cust nvarchar(50)
    )
    RETURNS Decimal(18,2)
    AS
    BEGIN
    	-- Declare the return variable here
    	DECLARE @Price AS DECIMAL(18,2)
    
    	-- Add the T-SQL statements to compute the return value here
    	select @Price = (SELECT Min(s.Price) * i.Qty
    			FROM CUSTOMERS c
    LEFT JOIN Orders o
    ON c.name = o.cust
    LEFT JOIN Sales i
    on i.Order_No = o.Order_no
    Left JOIN Supplies s
    on i.Item = s.Item
    				where s.Item = @ProductID and c.Name = @Cust
    				group by i.Qty)
    			
    
    	-- Return the result of the function
    	RETURN @Price
    
    END

    The above is used by this SQL

    SELECT  c.CustomerName AS 'Customer',dbo.AllItemsSold(s.CustomerItem, i.SalesQty, c.CustomerBalance, c.CustomerName) AS NEWBALANCE
    FROM CUSTOMERS c
    LEFT JOIN Orders o
    ON c.name = o.cust
    LEFT JOIN Sales i
    on i.Order_No = o.Order_no
    Left JOIN Supplies s
    on i.Item = s.Item
    group by c.CustomerName, c.CustomerAddress, c.CustomerBalance,s.CustomerItem,i.SalesQty, s.Price

    That returns

    Cust1	17.45
    Cust1	17.45
    Cust1	3.00
    Cust1	0.69
    Cust2	61.44
    Cust3	NULL
    Cust4	30.54
    Cust4	30.54
    Cust4	10.47
    Cust4	10.47
    Cust4	122.88

    What I need is say for cust4 to be 30.54 + 10.47 +122.88 = 163.89

    Can any help with this

    Thanks

    Sunday, April 24, 2016 11:07 AM

Answers

  • User941753370 posted

    Hi,

    Try:

    SELECT  c.CustomerName AS 'Customer', SUM(a.NEWBALANCE) AS NEWBALANCE
    FROM CUSTOMERS c
    OUTER APPLY
    (
        SELECT dbo.AllItemsSold(s.CustomerItem, i.SalesQty, c.CustomerBalance, c.CustomerName) AS NEWBALANCE
        FROM Orders o
        LEFT JOIN Sales i
            on i.Order_No = o.Order_no
        Left JOIN Supplies s
            on i.Item = s.Item
        WHERE c.name = o.cust
    ) a
    group by c.CustomerName

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 24, 2016 10:41 PM