title: Calculate percentage against total for a specified condition (SQL Server)
body: <p>I am working on a query that's supposed to return a data set that will be consumed by a report. Suppose i have a table <code>dbo.payments</code> with data as shown below:</p>
  
  <pre><code>-----------------------------------------------
  Customer |Commission| Trade Date | Trade Type | 
  -----------------------------------------------
  AMIRALIS | 20.00    | 22/01/2018 |   SALE     |
  BRUSSASH | 30.00    | 22/01/2018 |  PURCHASE  |
  AMIRALIS | 10.00    | 22/01/2018 |   SALE     |
  AKBMOBIL | 50.00    | 22/01/2018 |  PURCHASE  |
  AMIRALIS | 10.00    | 23/01/2018 |  PURCHASE  |
  BRUSSASH | 10.00    | 23/01/2018 |  PURCHASE  |
  BRUSSASH | 30.00    | 23/01/2018 |   SALE     |
  BRUSSASH | 10.00    | 23/01/2018 |  PURCHASE  |
  AMIRALIS | 60.00    | 24/01/2018 |  PURCHASE  |
  BRUSSASH | 10.00    | 24/01/2018 |   SALE     |
  </code></pre>
  
  <p>Scenario #1: I want to write a query that will give me the results as shown below. Please not that the percentage is against the <code>SUM(Commission)</code> of all time <code>240.00</code> regardless of the where clause.</p>
  
  <pre><code>-----------------------------------------------------
  Customer |Total Commission| Trade Date |%Commission | 
  -----------------------------------------------------
  AMIRALIS |     30.00      | 22/01/2018 |  0.125     |
  BRUSSASH |     30.00      | 22/01/2018 |  0.125     |
  AKBMOBIL |     50.00      | 22/01/2018 |  0.208     |
  AMIRALIS |     10.00      | 23/01/2018 |  0.041     |
  BRUSSASH |     50.00      | 23/01/2018 |  0.208     |
  AMIRALIS |     60.00      | 24/01/2018 |  0.250     |
  BRUSSASH |     10.00      | 24/01/2018 |  0.041     |
  </code></pre>
  
  <p>Am a SQL newbie, the furthest i could go was:.</p>
  
  <p><code>SELECT [TRADE DATE] AS DATE, CUSTOMER, SUM([COMMISSION]) AS TOTAL, ([COMMISSION] / (SELECT SUM([COMMISSION]) FROM DBO.PAYMENTS)) AS '%COMMISSION' FROM DBO.PAYMENTS GROUP BY [TRADE DATE], CUSTOMER, COMMISSION</code></p>
  
  <p>How to i achieve the desired result, regardlessor the WHERE clause:i.e. <code>WHERE [Trade Date] BETWEEN '21/01/2018' AND '24/01/2018'</code> which reduces the total to <code>170.00</code> </p>