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

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