## How to calculate percentage from two queries?

 Author Message wmalik001 Say Hey Kid Group: General Forum Members Points: 674 Visits: 47 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)/80How would i write both queries into one to get the percentage?here are the queries:Query 1This gives the total result of 80select 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_IDwhere 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! Mark Cowne SSChampion Group: General Forum Members Points: 14816 Visits: 26578 Try this`select SUM(CASE WHEN SCM_EMAIL_ANSWER_FACT.QUEST_ANSWER IN ('1','2') THEN 100 ELSE 0 END) / COUNT (*) from SCM_EMAIL_CONTROLinner join cli_email_rec on [SCM_EMAIL_CONTROL].RESPONDENT_ID = cli_email_rec.RESPONDENT_IDinner join SCM_EMAIL_ANSWER_FACT on CLI_EMAIL_REC.RESPONDENT_ID = SCM_EMAIL_ANSWER_FACT.RESPONDENT_IDwhere 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 beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 wmalik001 Say Hey Kid Group: General Forum Members Points: 674 Visits: 47 Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? Mark Cowne SSChampion Group: General Forum Members Points: 14816 Visits: 26578 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 beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 Bhuvnesh SSC-Dedicated Group: General Forum Members Points: 32534 Visits: 4079 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;-) Orlando Colamatteo SSC Guru Group: General Forum Members Points: 104926 Visits: 14506 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