CODEWITHSUNDEEP
sqlsql-server-2008
Sebas Silva
Sebas Silva

Reputation: 115

How can I use the return of a select into another select in order to sum them?

My problem is that I want to sum the column of quantity with their corresponding employee ID but it doesnt let me rename the result of the query with as

Here is my query:

 SELECT Orders.OrderID,Orders.EmployeeID,[Order Details].Quantity 
    FROM Orders  INNER JOIN DimEmployee 
    ON Orders.EmployeeID=DimEmployee.EmployeeID 
    INNER JOIN [Order Details] 
    ON [Order Details].OrderID=Orders.OrderID

It doesnt let me put:

AS name (query)

or (query) AS name

In order for me to use another select query like this:

SELECT SUM([Orders Detail].Quantity) FROM queryResult Group By Orders.EmployeeID

Upvotes: 1

Views: 70

Answers (3)

Edward
Edward

Reputation: 772

Something like this should give you what you want. 

    SELECT 
    Orders.EmployeeID AS EmployeeID,
    SUM([Order Details].Quantity) AS TotalQty
    FROM Orders 
    INNER JOIN DimEmployee 
      ON Orders.EmployeeID=DimEmployee.EmployeeID 
    INNER JOIN [Order Details] 
        ON [Order Details].OrderID=Orders.OrderID 
    GROUP BY 
     Orders.EmployeeID

Upvotes: 2

Ian-Fogelman
Ian-Fogelman

Reputation: 1605

Try this: 

   SELECT SUM(QUANTITY),EmployeeId FROM(
         SELECT ORD.OrderID,ORD.EmployeeID,OD.Quantity 
            FROM Orders AS ORD
            INNER JOIN DimEmployee  AS DE
            ON ORD.EmployeeID=DE.EmployeeID 
            INNER JOIN [Order Details] AS OD 
            ON OD.OrderID = ORD.OrderID 
            ) AS X
Group By EmployeeId

Upvotes: 2

S3S
S3S

Reputation: 25112

You can do this with a straight forward aggregation, or a window function to return all details. You may need to remove the OrderID from the SELECT list and GROUP BY if you don't want the results grouped at that level.

SELECT 
    Orders.OrderID,
    Orders.EmployeeID,
    EmpTotal = SUM([Order Details].Quantity) 
FROM Orders  
    INNER JOIN DimEmployee 
    ON Orders.EmployeeID=DimEmployee.EmployeeID 
    INNER JOIN [Order Details] 
    ON [Order Details].OrderID=Orders.OrderID
GROUP BY
    Orders.OrderID,
    Orders.EmployeeID


SELECT 
    Orders.OrderID,
    Orders.EmployeeID,
    PartitionedTotal = SUM([Order Details].Quantity) OVER (PARTITION BY Orders.EmployeeID, Orders.OrderID) --here you may only want to partition by the EmployeeID instead of both
FROM Orders  
    INNER JOIN DimEmployee 
    ON Orders.EmployeeID=DimEmployee.EmployeeID 
    INNER JOIN [Order Details] 
    ON [Order Details].OrderID=Orders.OrderID

Upvotes: 1

Related Questions