How to calculate percentage from two queries?

  • HI,

    I have two queries, Query 1 - is the total result equal to 80 the Query -2 is a subtotal with a different where condition equal to 25 result, i want to calculate the percentage as (25*100)/80

    How would i write both queries into one to get the percentage?

    here are the queries:

    Query 1

    This gives the total result of 80

    select COUNT (*)

    from SCM_EMAIL_CONTROL with (nolock)

    inner join cli_email_rec on [SCM_EMAIL_CONTROL].RESPONDENT_ID = cli_email_rec.RESPONDENT_ID

    inner join SCM_EMAIL_ANSWER_FACT on CLI_EMAIL_REC.RESPONDENT_ID = SCM_EMAIL_ANSWER_FACT.RESPONDENT_ID

    where

    SCM_EMAIL_CONTROL.PGM_CD = '123'

    and SCM_EMAIL_CONTROL.DISP_CD = 'CMP'

    and convert(date,SCM_EMAIL_CONTROL.LAST_VISIT_DATE) between '2012-07-01' and '2012-09-30'

    --and CLI_EMAIL_REC.Survey_Type = 'Defector'

    and SCM_EMAIL_ANSWER_FACT.QUESTION_NUM in ('Q4_16')

    and (SCM_EMAIL_ANSWER_FACT.QUEST_ANSWER <> '0' )

    Query 2:

    This results into 25:

    select COUNT (*)

    from SCM_EMAIL_CONTROL with (nolock)

    inner join cli_email_rec on [SCM_EMAIL_CONTROL].RESPONDENT_ID = cli_email_rec.RESPONDENT_ID

    inner join SCM_EMAIL_ANSWER_FACT on CLI_EMAIL_REC.RESPONDENT_ID = SCM_EMAIL_ANSWER_FACT.RESPONDENT_ID

    where

    SCM_EMAIL_CONTROL.PGM_CD = '123'

    and SCM_EMAIL_CONTROL.DISP_CD = 'CMP'

    and convert(date,SCM_EMAIL_CONTROL.LAST_VISIT_DATE) between '2012-07-01' and '2012-09-30'

    and SCM_EMAIL_ANSWER_FACT.QUESTION_NUM in ('Q4_16')

    and (SCM_EMAIL_ANSWER_FACT.QUEST_ANSWER <> '0' )

    and (SCM_EMAIL_ANSWER_FACT.QUEST_ANSWER = '1' OR SCM_EMAIL_ANSWER_FACT.QUEST_ANSWER = '2')

    Thanks for your help!

  • Try this

    select SUM(CASE WHEN SCM_EMAIL_ANSWER_FACT.QUEST_ANSWER IN ('1','2') THEN 100 ELSE 0 END) / COUNT (*)

    from SCM_EMAIL_CONTROL

    inner join cli_email_rec on [SCM_EMAIL_CONTROL].RESPONDENT_ID = cli_email_rec.RESPONDENT_ID

    inner join SCM_EMAIL_ANSWER_FACT on CLI_EMAIL_REC.RESPONDENT_ID = SCM_EMAIL_ANSWER_FACT.RESPONDENT_ID

    where

    SCM_EMAIL_CONTROL.PGM_CD = '123'

    and SCM_EMAIL_CONTROL.DISP_CD = 'CMP'

    and convert(date,SCM_EMAIL_CONTROL.LAST_VISIT_DATE) between '2012-07-01' and '2012-09-30'

    --and CLI_EMAIL_REC.Survey_Type = 'Defector'

    and SCM_EMAIL_ANSWER_FACT.QUESTION_NUM in ('Q4_16')

    and (SCM_EMAIL_ANSWER_FACT.QUEST_ANSWER <> '0' );

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? 😀

  • wmalik001 (1/2/2013)


    Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? 😀

    To be honest, I'm not a big fan of text books, most of my learning has been on-the-job and by getting involved in sites such as this one.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Mark-101232 (1/2/2013)


    To be honest, I'm not a big fan of text books, most of my learning has been on-the-job and by getting involved in sites such as this one.

    +1 , invest your time in sql forums. 🙂

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • wmalik001 (1/2/2013)


    Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? 😀

    I owe a great deal to the kind folks who answer questions and write articles for SSC, and other sites too. I also owe a lot to the people that post their questions because they provide real-world problems for me to research and resolve, which has proven to be an exceptional way to learn. Personally I think book-reading and staying active online complement each other quite well. If you're into T-SQL development I would recommend anything written by Itzik Ben-Gan:

    Amazon > Books › "Itzik Ben-Gan"

    This one if you're just getting started and want to stay in front of the newest version...or have been at it a while but want a formal comprehensive walk-through to fill in any holes in your knowledge you suspect you may have:

    Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply