Counting the return number of query results

[rogerscmg]

Hey Guys,

I have a query that so far works fine. So far what is does is as
fallows:

1) Returns to me the number of Marketing EMails i sent to a specific
magazine, during the Month i specify.
(example, For January I sent 3 emails, and it returns the details
of those 3)

2) I have some functions that run inside the query to return to me
some values. An example is finding the Open Rate (the rate at which
this specific email was open)
Open Rate: [Opens]/[Delivered]*100 is found by dividing the amount of
how many emails were opened, by that of those delivered. I also have
the fallowing:
CTR: [Clicks]/[Opens]*100 (The amount of Clicks in the email by those
that were opened)
Unsub Rate: [Clicks on UNSUS]/[Opens]*100 (the unsubscription rate)

3) Since i have 3 emails sent in January, i will have 3 very different
sets of results for each email. I need to find the AVERAGE Open rate,
CTR (Click through rate) and Unsub Rate for all three emails. This is
easy enough to do if i only had maybe 6 or 7 results, but i will have
more than 100 to do.

Is there any way to add a function or chunk of code to return to me
the details of each month, as well ass the verage of the results?

 

[rogerscmg]

Hey Guys,

I have a query that so far works fine. So far what is does is as
fallows:

1) Returns to me the number of Marketing EMails i sent to a specific
magazine, during the Month i specify.
(example, For January I sent 3 emails, and it returns the details
of those 3)

2) I have some functions that run inside the query to return to me
some values. An example is finding the Open Rate (the rate at which
this specific email was open)
Open Rate: [Opens]/[Delivered]*100 is found by dividing the amount of
how many emails were opened, by that of those delivered. I also have
the fallowing:
CTR: [Clicks]/[Opens]*100 (The amount of Clicks in the email by those
that were opened)
Unsub Rate: [Clicks on UNSUS]/[Opens]*100 (the unsubscription rate)

3) Since i have 3 emails sent in January, i will have 3 very different
sets of results for each email. I need to find the AVERAGE Open rate,
CTR (Click through rate) and Unsub Rate for all three emails. This is
easy enough to do if i only had maybe 6 or 7 results, but i will have
more than 100 to do.

Is there any way to add a function or chunk of code to return to me
the details of each month, as well ass the verage of the results?

Wow sorry about all my spelling mistakes, its almost no sleep for me
in past 2 days, my apologies.

 

[Ken Sheridan]

If you want to do it all it the query you could add three subqueries to the
SELECT clause, e.g. if the year and month are parameters:

SELECT <your current column list>,
(SELECT AVG([Opens]/[Delivered]*100)
FROM YourTable
WHERE YEAR([DateSent]) = [Enter year:]
AND MONTH([DateSent]) = [Enter month as a number 1-12:])
AS AverageOpenRate,
(SELECT AVG([Clicks]/[Opens]*100)
FROM YourTable
WHERE YEAR([DateSent]) = [Enter year:]
AND MONTH([DateSent]) = [Enter month as a number 1-12:])
AS AverageCTR,
(SELECT AVG([Clicks on UNSUS]/[Opens]*100)
FROM YourTable
WHERE YEAR([DateSent]) = [Enter year:]
AND MONTH([DateSent]) = [Enter month as a number 1-12:])
AS AverageUnsubRate
FROM YourTable
WHERE YEAR([DateSent]) = [Enter year:]
AND MONTH([DateSent]) = [Enter month as a number 1-12:]);

Ken Sheridan
Stafford, England