CODEWITHSUNDEEP
sqlsql-servert-sql
griegs
griegs

Reputation: 22760

Get a sum from a temp table into my select query

I have some SQL that calls a stored procedure and return a number of rows from various users. Each row has an amount and I want to get the sum of those amounts per user into another select query.

So the query to get the amounts;

Declare @tempUserPurchases table(
    Email nvarchar(50), Amount money
)

-- get all the data into the temp table.
insert into @tempUserPurchases
    exec stpGetUserPurchases @CompanyId = 312546

So the above generates several records per member each with an email and an amount and all for the specified company.

Now I have another Select statement and I want to put the sum for each member from the query above into the corresponding members record.

select 
    FirstName,
    LastName,
    Email,
    tblSum.Amount
Into #returnedTable
from
    tblMembers
    Cross Join
    (
        Select sum(Amount) Amount from @tempUserPurchases
        Where email = returnedTable.email
    ) tblSum

So at the end I should end up with a single row per email with the summed amount from the tempUserPurchases table.

I can't figure out how to get the email into the Cross Join. Or am I not even close on how to do this?

Upvotes: 0

Views: 4007

Answers (1)

Mike D.
Mike D.

Reputation: 4114

You don't need to do a CROSS JOIN because that will give you a Cartesian product. You can use a LEFT JOIN (or if you're sure there will always be a match in your table variable you can use an INNER JOIN as it will be slightly faster).

Like this:

SELECT FirstName, LastName, Email, tblSum.Amount
FROM tblMembers
LEFT JOIN (
    SELECT email, SUM(Amount) AS Amount
    FROM @tempUserPurchases
    GROUP BY email
) AS tblSum ON tblSum.email=tblMembers.email

The way you're trying to do it is called a correlated subquery. In order for that to work it would need to be in the SELECT clause.

SELECT FirstName, LastName, Email, (
        SELECT SUM(Amount)
        FROM @tempUserPurchases AS t
        WHERE t.email=m.email
    ) AS Amount
FROM tblMembers AS m

However, the join method will perform much better because the correlated subquery will have to be evaluated for every row. Using the JOIN method the aggregation query will only be run once.

Upvotes: 2

Related Questions