Display equation result

Question

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 %

(B/A + B)*100 if B = 6 and A = 2 become: (6/2 + 6)*100 = 900 — Joe Taras, Feb 06 '17 at 11:06
If you want 75 your expression will become: (6/(2+6))*100 = 75 — Joe Taras, Feb 06 '17 at 11:07

Juozas · Accepted Answer · 2017-02-06T16:07:43.803

0

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] = 'lost'  
    )   AS  [a]
OUTER APPLY
    (
        SELECT [cnt] = CAST(COUNT(*) AS MONEY) FROM [my_table] WHERE [stage] > 1
    )   AS  [b];

edited Feb 06 '17 at 16:07

answered Feb 06 '17 at 11:07

Juozas

916
10
17

it returns zero when i add this in equation ([b].[cnt]/([a].[cnt]+ [b].[cnt]))*100 – sara Feb 06 '17 at 11:16
I updated query. Problems with implicit sql server casts. Problem solved converting data to money type, instead of allowing server to operate with implicint conversions & ints – Juozas Feb 06 '17 at 11:29
You should **not** use the **`money`** data type like this, especially if you are doing division and multiplication. http://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server – SqlZim Feb 06 '17 at 14:00
Just wanted to describe where is the problem. Use decimal, if you need precision. Money type at some cases is good for frontend procedures (f.e. if you need to display currency). – Juozas Feb 06 '17 at 15:48
@sara i found mistake in my query: must be "[stage] = 1 AND [state] = 'lost' ". Please update your code, if you used anywhere same logic. Also, as said SqlZim, its better to use DECIMAL at cases, when precision required – Juozas Feb 06 '17 at 16:08

SqlZim · Answer 2 · 2017-02-06T14:22:45.363

No need for subqueries or outer apply().

select [B/(A+B)]=
    (sum(case when stage >1 then 1.0 else 0.0 end)
        /
    sum(case 
           when stage =1 and state = 'lost' 
             then 1.0 
           when stage > 1
             then 1.0 
           else null 
           end))*100
from t

test setup: http://rextester.com/THWUY43139

create table t (name char(3), stage int, state varchar(4))
insert into t values
   ('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');

query:

select 
      [B]=sum(case when stage >1 then 1 else 0 end)
    , [(A+B)]=sum(case 
           when stage =1 and state = 'lost' 
             then 1
           when stage > 1
             then 1
           else 0
           end)
    , [B/(A+B)]=(sum(case when stage >1 then 1.0 else 0.0 end)
        /
        sum(case 
           when stage =1 and state = 'lost' 
             then 1.0 
           when stage > 1
             then 1.0 
           else null 
           end))*100
from t

results:

+---+-------+----------+
| B | (A+B) | B/(A+B)  |
+---+-------+----------+
| 6 |     8 | 75,00000 |
+---+-------+----------+

Display equation result

2 Answers2