How to calculate percentage from two queries?

Question

Post reply

How to calculate percentage from two queries?

wmalik001

Ten Centuries

Points: 1190
More actions
January 2, 2013 at 3:12 am

#287124

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!

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

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

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

wmalik001 Ten Centuries Points: 1190 More actions · Answer 2

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

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 Points: 182279 More actions · Answer 5

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