title: Tricky sql query required, finding a sum of a subquery
body: <p>A relevant part of my db looks as follows (MS Visio, I know I'm pathetic :D):
  <a href="https://i.stack.imgur.com/E1iIG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/E1iIG.png" alt="Description?"></a></p>
  
  <p>I need to extract a list consisting of all items in a category as well as bundles. So I have to use <code>UNION</code>. First part of a <code>UNION</code> for your reference (as it sets the data format for the <code>SELECT</code> in the second part of <code>UNION</code>; note that <code>?</code> signifies where an argument goes in <code>node-mysql</code>): </p>
  
  <pre><code>SELECT `ID`, `Name`, `Description`, 
         `PictureID`, `SellingPrice`,
         `Cost`, 0 AS `Bundle` 
  FROM `Item` 
  WHERE `CategoryID`=? AND 
  `ID` IN ( 
           SELECT `ItemID` 
           FROM `Stock` 
           WHERE `CityID`=? 
           AND `IsLimitless`=1 OR `Quantity`&gt;0
          ) 
  </code></pre>
  
  <p>So I want to present my Bundles as if they are also items, with all same fields etc. </p>
  
  <p>My attempt: </p>
  
  <pre><code>SELECT `ID`, `Name`, `Description`, `PictureID`, 
        (
         SELECT SUM( // Here SQL indicates a syntax problem
                    SELECT `ItemAmount`*`PriceModifier`*(
                                           SELECT `SellingPrice` 
                                           FROM `Item` 
                                           WHERE `ID`=`BundleItem`.`ItemID`
                                           ) 
                     FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
                   )
        ) AS `SellingPrice`,
        (
          SELECT SUM(
                     SELECT `ItemAmount`*(
                                          SELECT `Cost` 
                                          FROM `Item` 
                                          WHERE `ID`=`BundleItem`.`ItemID`
                                         )
                      FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
                    )
        ) AS `Cost`, 
        1 AS `Bundle`
  FROM `Bundle` 
  WHERE `ID` IN (
                 SELECT `BundleID` 
                 FROM `BundleCategory` 
                 WHERE `CategoryID`=?
                )
  //No need to check bundles for stock due to business logic
  </code></pre>
  
  <p>I have a faint idea that I'm overcomplicating this, but I can't put my finger on it, unfortunately. </p>
  
  <p>Any advise will be very welcome and thanks in advance for taking your time. &lt;3</p>
  
  <p>Sample data: </p>
  
  <pre><code>Fields of no interest like "Description"/"PictureID"/"SupplierID" will be omitted
  for the relevant parts to fit on screen
  
  **Bundle**
  ID  Name            Description             PictureID
  1   Valentine Pack  Blah-blah tasty buy me  imgur link in text
  
  **Item**
  ID  Name               SellingPrice  Cost  CategoryID
  1   Movie Ticket       10            2     24
  2   Box of Chocolates  5             1     4
  3   Teddy Bear         15            3     2
  4   Roses              10            4     8
  
  **Stock**
  ItemID  CityID  Quantity  IsLimitLess 
  1       1       25        false
  1       2       11        false
  2       1       84        false
  3       1       33        false
  4       1       1         true
  4       3       1         true
  
  **BundleItem**
  BundleID  ItemID  ItemAmount  PriceModifier
  1         1       2           1.25
  1         2       1           1
  1         3       1           1
  1         4       5           0.75
  
  **BundleCategory** (bundle for marketing reasons can appear in different
  categories depending on its contents)
  BundleID  CategoryID
  1         4 //Sweets
  1         2 //Toys
  1         8 //Flowers
  </code></pre>
  
  <p>Desired output: (For searching CityID 1, CategoryID 8, Flowers)</p>
  
  <pre><code>ID  Name    (Descr/PicID)    SellingPrice Cost         Bundle
  
  4   Roses                    10           4            false
  
  1   Valentine Pack           82.5         28           true
                             /*2*10*1.25+   2*2+  &lt;movie
                               1*1*5+       1*1+  &lt;chocolate
                               1*1*15+      3*1+  &lt;teddy bear
                               5*0.75*10    5*4   &lt;roses */
  </code></pre>
  
  <p><strong>User suggested solutions</strong>
  As per @drakin8564 's suggestion I tried doing </p>
  
  <pre><code>SELECT `ID`, `Name`, `Description`, `PictureID`, 
        (
         SELECT SUM(( 
                    SELECT `ItemAmount`*`PriceModifier`*(
                                           SELECT `SellingPrice` 
                                           FROM `Item` 
                                           WHERE `ID`=`BundleItem`.`ItemID`
                                           ) 
                     FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
                          ))
        ) AS `SellingPrice`,
        (
          SELECT SUM((
                     SELECT `ItemAmount`*(
                                          SELECT `Cost` 
                                          FROM `Item` 
                                          WHERE `ID`=`BundleItem`.`ItemID`
                                         )
                      FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
                    ))
        ) AS `Cost`, 
        1 AS `Bundle`
  FROM `Bundle` 
  WHERE `ID` IN (
                 SELECT `BundleID` 
                 FROM `BundleCategory` 
                 WHERE `CategoryID`=8
                )
  </code></pre>
  
  <p>Returns </p>
  
  <pre><code>(1242): Subquery returns more than 1 row. 
  </code></pre>
  
  <p>This happens even when I try <code>SELECT SUM((SELECT ID FROM Item))</code>. Weird. 
  I commented on other solutions about how good they work. I appreciate all you guys taking part in this. &lt;3 </p>