Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to calculate percentage from two queries? Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 3:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 27, 2013 5:12 AM
Points: 24, 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!
Post #1401728
Posted Wednesday, January 2, 2013 3:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:29 AM
Points: 1,678, Visits: 19,554
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' );



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1401731
Posted Wednesday, January 2, 2013 3:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 27, 2013 5:12 AM
Points: 24, Visits: 47
Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? :D
Post #1401741
Posted Wednesday, January 2, 2013 3:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:29 AM
Points: 1,678, Visits: 19,554
wmalik001 (1/2/2013)
Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? :D


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.


____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1401747
Posted Wednesday, January 2, 2013 4:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
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
Post #1401782
Posted Wednesday, January 2, 2013 9:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 7,127, Visits: 12,728
wmalik001 (1/2/2013)
Thanks a lot Mark, that did the trick, could you also tell me the best resource/book for sql programming? :D

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
Post #1401931
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse