Calculate percentage against total for a specified

问题:

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 dbo.payments with data as shown below:

-----------------------------------------------
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     |

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 SUM(Commission) of all time 240.00 regardless of the where clause.

-----------------------------------------------------
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     |

Am a SQL newbie, the furthest i could go was:.

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

How to i achieve the desired result, regardlessor the WHERE clause:i.e. WHERE [Trade Date] BETWEEN '21/01/2018' AND '24/01/2018' which reduces the total to 170.00

回答1:

This could be solved using SQL Server window functions, like :

SELECT DISTINCT
    t.Customer,
    SUM(t.Commission) OVER (PARTITION BY Customer, Trade_Date) Total_Commission,
    t.Trade_Date,
     SUM(t.Commission) OVER (PARTITION BY Customer, Trade_Date) / SUM(t.Commission) OVER() "%Commission"
FROM mytable t 
ORDER BY 1, 3

Demo on DB fiddle :

 Customer | Total_Commission | Trade_Date          | %Commission
 :------- | :--------------- | :------------------ | :----------
 AKBMOBIL | 50.00            | 22/01/2018 00:00:00 | 0.208333   
 AMIRALIS | 30.00            | 22/01/2018 00:00:00 | 0.125000   
 AMIRALIS | 10.00            | 23/01/2018 00:00:00 | 0.041666   
 AMIRALIS | 60.00            | 24/01/2018 00:00:00 | 0.250000   
 BRUSSASH | 30.00            | 22/01/2018 00:00:00 | 0.125000   
 BRUSSASH | 50.00            | 23/01/2018 00:00:00 | 0.208333   
 BRUSSASH | 10.00            | 24/01/2018 00:00:00 | 0.041666   

PS : if you need to filter the output by date, you can simply add a WHERE clause to the query. This will filter both computations.

回答2:

You can use an aggregation query. In this case, you can use a window function to calculate the total commission:

select Customer, TradeDate,
       sum(Commission) as total_Commission,
       sum(Commission) / sum(sum(Commission)) over () as percent_commission
from dbo.payments p
group by Customer, TradeDate