CODEWITHSUNDEEP
sqlsql-server
Takudzwa Mawarire
Takudzwa Mawarire

Reputation: 105

Calculate percentage value for each row based on calculated total in SQL Server

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     |

I want to return the percentage against the total for the specified period. Say WHERE [Trade Date] BETWEEN '21/01/2018' AND '24/01/2018' total = 170.00

-----------------------------------------------------
Customer |Total Commission| Trade Date |%Commission | 
-----------------------------------------------------
AMIRALIS |     30.00      | 22/01/2018 |  0.176     |
BRUSSASH |     30.00      | 22/01/2018 |  0.176     |
AKBMOBIL |     50.00      | 22/01/2018 |  0.294     |
AMIRALIS |     10.00      | 23/01/2018 |  0.059     |
BRUSSASH |     50.00      | 23/01/2018 |  0.294     |

Am a SQL newbie, the furthest i could go to return the desired result was:.

SELECT [TRADE DATE] AS DATE, CUSTOMER, SUM([COMMISSION]) AS TOTAL, ([COMMISSION] / SUM([COMMISSION])) AS '%COMMISSION' FROM DBO.PAYMENTS GROUP BY [TRADE DATE], CUSTOMER, COMMISSION

and am not getting the desired outcome.

Also, the WHERE clause to the query i.e. ... WHERE DATE BETWEEN '21/01/2018' AND '24/01/2018' specifying the date range is appended by the report after the user selects the desired dates.

How do i return the percentage against the total for the specified period ?

Upvotes: 1

Views: 792

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you would just add a where clause to your original query (which is the answer here)

select Customer, TradeDate,
       sum(Commission) as total_Commission,
       sum(Commission) / sum(sum(Commission)) over () as percent_commission
where TradeDate >= '2018-01-21' and
      TradeDate < '2018-01-24'
from dbo.payments p
group by Customer, TradeDate;

Notes:

  • Date constants should always use YYYYMMDD or YYYY-MM-DD formats. These are ISO 8601 standards accepted by almost all databases.
  • Don't use BETWEEN with dates. Here is a very good explanation by Aaron Bertrand.
  • If commission is an integer, then you need a non-integer for the division. I often just add * 1.0 before the /.

Upvotes: 1

PSK
PSK

Reputation: 17943

This can be done using CTE like following.

;WITH cte 
     AS (SELECT customer, 
                commission, 
                [trade date], 
                [trade type], 
                Sum(commission) OVER() tot 
         FROM   dbo.payment 
         WHERE  [trade date] > '21/01/2018' 
                AND [trade date] < '24/01/2018') 
SELECT *, 
       commission * 100.0 / tot AS [%Commission] 
FROM   cte

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

The simplest solution would be to use sum with an empty over clause to get the total sum of commission selected by the query. This way, you don't have to worry about the where clause:

SELECT  [TRADE DATE] AS DATE, 
        CUSTOMER, 
        COMMISSION,
        CAST(COMMISSION as float) / SUM([COMMISSION]) OVER() AS '%COMMISSION', 
FROM DBO.PAYMENTS 
WHERE ...

Also, please note the cast of the COMMISSION to float (assumed it's an int). If you divide int with another int, you'll get an integer division which will never return a decimal.

Upvotes: 1

Related Questions