title: Is there a way to use aggregate functions real-time in SQL
body: <p>I am using a custom query to pull data from MySQL database to Tableau.</p>
  <p>'finalPayment' currently calculates final payment based on whether payment is split into deposit+invoice or full.</p>
  <pre><code>SELECT 
          *,
          CASE
              WHEN invoiceAmount IS NOT NULL THEN ((invoiceAmount + depositAmount)/100)
              ELSE (fullAmount/100)
              END AS final_payment
      FROM
          production
      WHERE 
          fullPaid = 1
          AND
          id IN
              (
              SELECT MIN(id)
              FROM production
              GROUP BY fullPaymentIntentID
              )
  </code></pre>
  <p>The issue now is that for customers that purchase n number of products, the fullAmount is logged for every entry. i.e. If a customer purchases 2 products of $100, there are two entries with $200. The logic has been fixed moving forwards, but for analysis of legacy data, I need to divide the fullAmount by the COUNT of 'fullPaymentIntentID'. I am currently using a shortcut by parsing the first entry of 'fullPaymentIntentID' using GROUP BY MIN. Is there a way for real-time evaluation of COUNT in a CASE statement?</p>
  <pre><code>CASE
      WHEN invoiceAmount IS NOT NULL THEN ((invoiceAmount + depositAmount)/100
      WHEN COUNT(fullPaymentIntentID) &gt; 1 THEN (fullAmount/100/COUNT(fullPaymentIntentID))
      ELSE (fullAmount/100)
      END AS final_payment
  </code></pre>