Quick thought, first of all, I think there is an error in the code as purchase registryid does not match the item id.
😎
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
Should be
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.registryid
WHERE gi.registryid=2
The query can be changed to return both the total and the item price per item line by adding the OVER clause to the SUM
SELECT
SUM(gi.amount) OVER (PARTITION BY gi.registryid) AS TOTAL_AMOUNT
,SUM(gi.amount) OVER (PARTITION BY gi.id) AS ITEM_AMOUNT
,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.registryid
WHERE gi.registryid=2
Results
TOTAL_AMOUNT ITEM_AMOUNT id registryid title ogimg description URL amount price createdate id registryid itemid emailid amount createdate
------------ ----------- ----------- ----------- ---------------------------------------------------------------------------------- ------------------------------------------------------------- --------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------- ------ ----------- ----------------------- ----------- ----------- ----------- ----------- ------ -----------------------
13 12 4 2 coffee cups some coffee cups http://www.coffee.com 12 10 2015-05-02 01:02:24.053 1 2 4 1 3 2015-05-02 22:21:41.640
13 1 5 2 Microsoft Surface Pro 3 - 12" Tablet - 256GB SSD, Intel Core i7 Haswell, 8GB RAM http://i.ebayimg.com/images/i/281656969697-0-1/s-l1000.jpg US $1,149.99 Manufacturer refurbished in Computers/Tablets & Networking, iPads, Tablets & eBook Readers http://www.ebay.com/itm/Microsoft-Surface-Pro-3-12-Tablet-256GB-SSD-Intel-Core-i7-Haswell-8GB-RAM-/281656969697 1 1010 2015-05-02 21:27:02.363 1 2 4 1 3 2015-05-02 22:21:41.640
