 sara - 4 years ago 106
SQL Question

# Display equation result

I want to display the results of this equation (B/A+B)*100

I dont want rows of stage=1 and state is open

A are the rows where stage=1 and state is lost

B are the rows where stage=2 or higher irrespective of status

``````------------------------------------
name |stage| state
------------------------------------
| ABC  | 1   | open
| DEF  | 1   | open
| ABB  | 1   | lost
| ABD  | 1   | lost
| PQR  | 2   | won
| PQF  | 3   | lost
| PQY  | 4   | open
| PQN  | 5   | won
| PQM  | 6   | lost
| PQM  | 7   | lost
``````

The result should be (6/6+2)*100= 75 % Juozas
Answer Source
``````SELECT
[equation] =   CASE
WHEN (ISNULL([a].[cnt], 0) + ISNULL([b].[cnt], 0)) = 0 THEN NULL
ELSE (ISNULL([b].[cnt], 0) / (ISNULL([a].[cnt], 0) + ISNULL([b].[cnt], 0))) * 100
END
FROM
(
SELECT  [cnt] = CAST(0 AS MONEY)
)   AS [x]
OUTER APPLY
(
SELECT [cnt] = CAST(COUNT(*) AS MONEY) FROM [my_table] WHERE [stage] = 1 AND [state] = 'open'
)   AS  [a]
OUTER APPLY
(
SELECT [cnt] = CAST(COUNT(*) AS MONEY) FROM [my_table] WHERE [stage] > 1
)   AS  [b];
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download