Dividing the counts of two separate queries

• jonathanmreynolds

  SSC Eights!

  Points: 955

  More actions

  June 2, 2009 at 3:00 pm

  #93577

  Hello, this should be easy but i'm dumb.

  I have two tables that I want to cross-reference.

  Table A = NAME_PHONE

  Table B = RESERVATION_NAME

  I want to count one column value in each table and then divide those values together to come to a percentage.

  Here are the queries separated.

  Query #1

  SELECT COUNT(N.PHONE_NUMBER)

  FROM NAME_PHONE N, RESERVATION_NAME RN

  WHERE N.NAME_ID=RN.NAME_ID

  AND N.PHONE_TYPE='EMAIL'

  AND RN.BEGIN_DATE BETWEEN PMS_P.BUSINESS_DATE -30 AND PMS_P.BUSINESS_DATE

  Query #2

  SELECT COUNT(RESV_NAME_ID)

  FROM RESERVATION_NAME

  WHERE BEGIN_DATE BETWEEN PMS_P.BUSINESS_DATE -30 AND PMS_P.BUSINESS_DATE

  So in the first query i have to link the tables on "name_id" because i only want to count email addresses that arrived during my time frame. In the second query i don't care about linking because i just want the total reservation arrivals for that time period.

  I basically am trying to know what my "email capture rate" is...basically Query#1/Query#2.

  Can anyone help?

• arun.sas

  SSChampion

  Points: 11831

  More actions

  June 2, 2009 at 9:24 pm

  #1003744

  jonathanmreynolds (6/2/2009)

  ---

  I basically am trying to know what my "email capture rate" is...basically Query#1/Query#2.

  Hi,

  try this,

  select count(N.PHONE_NUMBER),

  (count(N.PHONE_NUMBER)/RN.RESV_NAME_ID_COUNT)email_capture_rate

  from

  NAME_PHONE N,

  (select NAME_ID,

  COUNT(RESV_NAME_ID)RESV_NAME_ID_COUNT

  FROM RESERVATION_NAME

  WHERE BEGIN_DATE BETWEEN PMS_P.BUSINESS_DATE -30 AND PMS_P.BUSINESS_DATE) RN

  WHERE N.NAME_ID=RN.NAME_ID

  AND N.PHONE_TYPE='EMAIL'

  ARUN SAS