title: SQL - Inline select statements - issue when @retailer_id = null
body: <p>I have this query, which works perfectly when I supply the retailer_id parameter with a value.</p>
  
  <pre><code>SELECT      reda.RetailerId,
                  (   SELECT      SUM(reda.Purchases)
                      FROM        RetailerDaily                   reda
                      WHERE       (reda.RetailerId                = @retailer_id
                      OR          @retailer_id                    IS NULL)
                      AND         (reda.DateStart                 &gt;= @date_from
                      OR          @date_from                      IS NULL)
                      AND         (reda.DateStart                 &lt;= @date_to
                      OR          @date_to                        IS NULL)
                      GROUP BY    reda.RetailerId)                                    AS Purchases,
  
                  (   SELECT      SUM(reda.PurchaseTotal)
                      FROM        RetailerDaily                   reda
                      WHERE       (reda.RetailerId                = @retailer_id
                      OR          @retailer_id                    IS NULL)
                      AND         (reda.DateStart                 &gt;= @date_from
                      OR          @date_from                      IS NULL)
                      AND         (reda.DateStart                 &lt;= @date_to
                      OR          @date_to                        IS NULL)
                      GROUP BY    reda.RetailerId)                                    AS PaymentsTotal,
  
                  (   SELECT      SUM(reda.Refunds)
                      FROM        RetailerDaily                   reda
                      WHERE       (reda.RetailerId                = @retailer_id
                      OR          @retailer_id                    IS NULL)
                      AND         (reda.DateStart                 &gt;= @date_from
                      OR          @date_from                      IS NULL)
                      AND         (reda.DateStart                 &lt;= @date_to
                      OR          @date_to                        IS NULL)
                      GROUP BY    reda.RetailerId)                                    AS Refunds,
  
                  (   SELECT      SUM(reda.RefundTotal)
                      FROM        RetailerDaily                   reda
                      WHERE       (reda.RetailerId                = @retailer_id
                      OR          @retailer_id                    IS NULL)
                      AND         (reda.DateStart                 &gt;= @date_from
                      OR          @date_from                      IS NULL)
                      AND         (reda.DateStart                 &lt;= @date_to
                      OR          @date_to                        IS NULL)
                      GROUP BY    reda.RetailerId)                                    AS RefundsTotal,
  
                  (   SELECT      SUM(reda.OffersOptedInto)
                      FROM        RetailerDaily                   reda
                      WHERE       (reda.RetailerId                = @retailer_id
                      OR          @retailer_id                    IS NULL)
                      AND         (reda.DateStart                 &gt;= @date_from
                      OR          @date_from                      IS NULL)
                      AND         (reda.DateStart                 &lt;= @date_to
                      OR          @date_to                        IS NULL)
                      GROUP BY    reda.RetailerId)                                    AS OffersOptedInto,
  
                  (   SELECT      SUM(reda.RedeemedOffers)
                      FROM        RetailerDaily                   reda
                      WHERE       (reda.RetailerId                = @retailer_id
                      OR          @retailer_id                    IS NULL)
                      AND         (reda.DateStart                 &gt;= @date_from
                      OR          @date_from                      IS NULL)
                      AND         (reda.DateStart                 &lt;= @date_to
                      OR          @date_to                        IS NULL)
                      GROUP BY    reda.RetailerId)                                    AS RedeemedOffers,
  
                  (   SELECT      SUM(reda.RedeemedOfferTotal)
                      FROM        RetailerDaily                   reda
                      WHERE       (reda.RetailerId                = @retailer_id
                      OR          @retailer_id                    IS NULL)
                      AND         (reda.DateStart                 &gt;= @date_from
                      OR          @date_from                      IS NULL)
                      AND         (reda.DateStart                 &lt;= @date_to
                      OR          @date_to                        IS NULL)
                      GROUP BY    reda.RetailerId)                                    AS RedeemedOfferTotal
  
      FROM        RetailerDaily                                                       reda
  
      WHERE       (reda.RetailerId                                                    = @retailer_id
      OR          @retailer_id                                                        IS NULL)
      AND         (reda.DateStart                                                     &gt;= @date_from
      OR          @date_from                                                          IS NULL)
      AND         (reda.DateStart                                                     &lt;= @date_to
      OR          @date_to                                                            IS NULL)
  
      GROUP BY    reda.RetailerId
  </code></pre>
  
  <p>However, when I pass in retailer_id as null I receive the following error message</p>
  
  <pre><code>Msg 512, Level 16, State 1, Line 9
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.
  </code></pre>
  
  <p>Can anybody shed some light onto how to resolve this issue as I can't seem to work it out.</p>
  
  <p>Thanks in advance</p>
  
  <p>Steven</p>