SQLTeam.com | Weblogs | Forums

SQL Column Percentage (with multiple group by)

--- CONTEXT ---

I have this view/exhibition, that lists records with sent date, client name, client position, type of refusal, total of IDClientPosition and total of IDRefusal. I use it to check how many claims sent on each date, were accepted, refused and why they were refused by each Client. (I have related tables to use for ClientPosition, Refusal and Client)

  DtSent    |  IDClient | IDClientPosition | TOTAL Position | IDRefusal | TOTAL Refusal
2020-01-10	       1	         1	               40	        NULL	       0
2020-01-10	       1	         1	               3	          7	           3
2020-01-10	       1	         2	               1            NULL	       0
2020-02-10	       2	         2	               27	          1           27
2020-02-10	       2	         2	               13	          2	          13    

Query so far, that shows the above values:

SELECT        TOP (100) PERCENT dbo.LBA.DtSent, dbo.Claims.IDClient, dbo.LBA.IDClientPosition, 
           COUNT(dbo.LBA.IDClientPosition) AS [TOTAL Position], dbo.LBA.IDRefusal, 
           COUNT(dbo.LBA.IDRefusal) AS [TOTAL Refusal]
FROM            dbo.LBA INNER JOIN
             dbo.ClientPositionLBA ON dbo.LBA.IDClientPosition = 
             dbo.ClientPositionLBA.IDClientPositLBA LEFT OUTER JOIN
             dbo.RefusalLBA ON dbo.LBA.IDRefusal = dbo.RefusalLBA.IDRefusal LEFT OUTER JOIN
             dbo.Claims LEFT OUTER JOIN
             dbo.Clients ON dbo.Claims.IDClient = dbo.Clients.IDClients ON 
             dbo.LBA.ClaimID = dbo.Claims.ClaimID
GROUP BY dbo.LBA.IDClientPosition, dbo.Claims.IDClient, dbo.LBA.IDRefusal, dbo.LBA.DtSent


I would like to add 2 columns, for percentages, based on the basis of values of TOTAL Position and TOTAL Refusal.
With this, besides the total values that I already have (count), I could also know the percentage of how many claims were accepted, rejected and with what justification (IDRefusal), for that date, for each client. Summing up, it's more or less like converting those totals to percentage.

Per month there should be around 100 records per client in a total of 20 clients, so it has to have in consideration the performance.

--- TRIED ---


COUNT(dbo.LBA.IDClientPosition) * 1.0 / COUNT(*),
COUNT(dbo.LBA.IDRefusal) * 1.0 / COUNT(*)

But it returns for both, 1.000000000000 or 0.000000000000.

Any help would be much appreciated. Thanks in advance!

If you provide directly usable data -- CREATE TABLE and INSERT statements -- for the sample data above, I'll write some code for that.