Creating Ratio from summary Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating Ratio from summary Data

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-12-01 : 05:01:25

Hi

I am doing some work in microstrategy reports and using SQL data cube. I am creating a summary report (Counting on Unique ID) in MS where when I put in a particular attribute/column from cube it splits the data count down into 2 separate values that I have defined in the select case statement of the SQL query.

I want to work out a ratio of the count unique ID between these two values but can't do this in MS report as it is not a physical column but summary split of data.

Is there a way I can do this in SQL? My summary looks something like this:

            N                       E
       ======================  =========================
        **Y**           N         **Y**           N
      =========    ==========   =========     =========
         570           140         89            56

Where the Y/N field is the one I have split down against the N/E column. I want to work out ratio between the 2 "Y" fields but in SQL. Here is a SQL example snapshot of my data:


Unique_ID   New/Existing Application      Attend_Interview         
=========   ========================     ================    
12554445    E                            Y
65766879    N                            N
53375654    N                            Y
44323224    E                            N
93656786    E                            Y

wondering if I might be able to do a procedure or similar or something more dynamic in SQL query?

Many thanks in advance for any suggestions.

G

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-12-01 : 18:29:09

Perhaps something like:

select new_yes
      ,new_no
      ,exist_yes
      ,exist_no
      ,1.0*new_yes/exist_yes as ratio_yes
      ,1.0*new_no/exist_no as ratio_no
  from (select sum(case when [New/Existing Application]='N' and Attend_Interview='Y' then 1 else 0 end) as new_yes
              ,sum(case when [New/Existing Application]='N' and Attend_Interview='N' then 1 else 0 end) as new_no
              ,sum(case when [New/Existing Application]='E' and Attend_Interview='Y' then 1 else 0 end) as exist_yes
              ,sum(case when [New/Existing Application]='E' and Attend_Interview='N' then 1 else 0 end) as exist_no
          from yourtable
       ) as a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources