# Probability Distribution calculation in sql

161 Reputation points
2022-08-29T18:58:57.547+00:00

Hello,

Is there any way to get probability in sql.

Suppose in remarks column we have more loss, and i am trying to show
if we decrease distance or change ChannelSource than there is probability
for particular TrackID to have gain instead of loss.

Pardon me if it is not possible in sql

Data:

``````DECLARE @MYTABLE TABLE
(
ID	VARCHAR (50),
distance INT,
ChannelSource VARCHAR (50),
REMARKS VARCHAR (50)

)

INSERT @MYTABLE

SELECT 'Z1G510A',719,'ThirdParty','Loss' UNION ALL
SELECT 'B18EAE2',91,'website',' Gain' UNION ALL
SELECT 'E116A40',451,'ThirdParty','Loss' UNION ALL
SELECT '91H8B6C',113,'ThirdParty','Loss' UNION ALL
SELECT 'S1Z165F',315,'ThirdParty','Loss' UNION ALL
SELECT 'F1D1308',983,'website','Loss' UNION ALL
SELECT '21577CA',1052,'website','Loss' UNION ALL
SELECT 'X1FF3A9',904,'website','Gain' UNION ALL
SELECT 'S1V7021',621,'website','Gain'
``````

Thank You so Much

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,682 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

1. 22,781 Reputation points Microsoft Vendor
2022-08-30T02:01:32.597+00:00

Hi @Rosalina5

``````DECLARE @MYTABLE TABLE
(
ID    VARCHAR (50),
distance INT,
ChannelSource VARCHAR (50),
REMARKS VARCHAR (50)
)
INSERT @MYTABLE
SELECT 'Z1G510A',719,'ThirdParty','Loss' UNION ALL
SELECT 'B18EAE2',91,'website',' Gain' UNION ALL
SELECT 'E116A40',451,'ThirdParty','Loss' UNION ALL
SELECT '91H8B6C',113,'ThirdParty','Loss' UNION ALL
SELECT 'S1Z165F',315,'ThirdParty','Loss' UNION ALL
SELECT 'F1D1308',983,'website','Loss' UNION ALL
SELECT '21577CA',1052,'website','Loss' UNION ALL
SELECT 'X1FF3A9',904,'website','Gain' UNION ALL
SELECT 'S1V7021',621,'website','Gain'
SELECT * FROM @MYTABLE

;WITH CTE AS
(
SELECT *,SUM(CASE WHEN REMARKS='Loss' THEN 1 ELSE 0 END)OVER(PARTITION BY 1)*1.0/COUNT(REMARKS)OVER(PARTITION BY 1) AS Probability_of_Loss
FROM @MYTABLE
)
UPDATE CTE
SET ChannelSource='website'
,REMARKS = CASE WHEN Probability_of_Loss > 0.5 THEN 'Gain' ELSE 'Loss' END
WHERE ID = 'Z1G510A'

SELECT * FROM @MYTABLE
``````

Best regards,
LiHong