SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to calculate percentage from two queries?


How to calculate percentage from two queries?

Author
Message
wmalik001
wmalik001
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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)/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!
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 24042
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
wmalik001
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 47
Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? BigGrin
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 24042
wmalik001 (1/2/2013)
Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? BigGrin


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
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5176 Visits: 4076
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
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14825 Visits: 14396
wmalik001 (1/2/2013)
Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? BigGrin

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search