Sum a Column

Question

Sum a Column

James Hunt

SSC-Addicted

Points: 440
More actions
June 12, 2006 at 2:30 pm

#113323

Here is the query I'm working with. This gives a list of all amounts summed from main and expanded items. For instance, it sums a system and the items associated with the part.
SELECT     ((CASE WHEN SUM(usersItemExpanded.amount) IS NULL
                                              THEN userSystems.amount ELSE (userSystems.amount + SUM(usersItemExpanded.amount)) END) * userSystems.qty)
                                              AS totalamount
                       FROM          userSystems LEFT OUTER JOIN
                                              usersItemExpanded ON userSystems.itemid = usersItemExpanded.mainItemId AND
                                              usersItemExpanded.uniqueId = userSystems.uniqueid
                       WHERE      userSystems.uniqueId = '1452' AND userSystems.companyid = 1
                       GROUP BY qty, userSystems.uniqueId, userSystems.companyid, userSystems.amount

What I want to do is sum these summed items. When I try to put a select sum around the query this it doesn't work. Any ideas?
select sum(totalamount) from
(
SELECT     ((CASE WHEN SUM(usersItemExpanded.amount) IS NULL
                                              THEN userSystems.amount ELSE (userSystems.amount + SUM(usersItemExpanded.amount)) END) * userSystems.qty)
                                              AS totalamount
                       FROM          userSystems LEFT OUTER JOIN
                                              usersItemExpanded ON userSystems.itemid = usersItemExpanded.mainItemId AND
                                              usersItemExpanded.uniqueId = userSystems.uniqueid
                       WHERE      userSystems.uniqueId = '1452' AND userSystems.companyid = 1
                       GROUP BY qty, userSystems.uniqueId, userSystems.companyid, userSystems.amount
)

Thanks!
James

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

You're summing out of a derived table. This has to be tagged with an alias after the closing parenthesis, even if it's the only table in the query:

select sum(totalamount) from

(

SELECT ((CASE WHEN SUM(usersItemExpanded.amount) IS NULL

THEN userSystems.amount ELSE (userSystems.amount + SUM(usersItemExpanded.amount)) END) * userSystems.qty)

AS totalamount

FROM userSystems LEFT OUTER JOIN

usersItemExpanded ON userSystems.itemid = usersItemExpanded.mainItemId AND

usersItemExpanded.uniqueId = userSystems.uniqueid

WHERE userSystems.uniqueId = '1452' AND userSystems.companyid = 1

GROUP BY qty, userSystems.uniqueId, userSystems.companyid, userSystems.amount

) dt