having trouble with count in my select query 1

[pushyr]

yesterday I'd posted something similar to this and it was successfully answered.
after moving onto the next step i've hit a bit of a brick wall so i'm creating a new post...

i have the following table '11_email_tracker', with tracked emails...

campaign | open | clicks
A | 1 | 2
A | 2 | 0
A | 0 | 0
A | 1 | 1
A | 0 | 0
A | 3 | 0
A | 0 | 0
A | 2 | 4
B | 0 | 0
B | 5 | 2
B | 0 | 0
B | 6 | 1
B | 0 | 0
B | 2 | 0
B | 1 | 0
B | 0 | 0
etc...


I can GROUP by campaign and COUNT how many emails were sent, for example...

campaign | sent |
A | 12000 |
B | 30000 |


and to do this i'm using the following query...

$sql_email_rpt = "SELECT COUNT(email_campaign) ";
$sql_email_rpt .= "FROM 11_email_tracker ";
$sql_email_rpt .= "GROUP by email_campaign ";
$sql_email_rpt .= "ORDER by email_campaign DESC";

but my next step is to achieve the following...

campaign | sent | opened | clicked | open_rate% | click_rate%
A | 12000 | 3000 | 416 | 25 | 13.8
B | 30000 | 5000 | 535 | 16.6 | 10.7

all in the same while loop and query...
i must COUNT how many emails were sent and how many were opened,
but i must only count the number of emails opened with a value greater than 0 in column 'open'
and the same for clicks, i must count the number of emails clicked with a value greater than 0 in column 'clicks'
plus i must calculate open rate % and CTR% in the same query

r937 showed me how to calculate open rate % and CTR% for a similar issue i had,
but now i need to modify my query and taking it once step at a time i'm first trying to count the number of emails opened and clicked,

i tried doing the following but it doesn't work... although i thought it must be on the right track?

$sql_email_rpt = "SELECT
COUNT(email_campaign),
COUNT(open) > 0,
COUNT(clicks) > 0,
100.0 * COUNT(open) / COUNT(email_campaign) AS `open_rate%`,
100.0 * COUNT(clicks) / COUNT(open) AS `click_rate%`
";
$sql_email_rpt .= "FROM 11_email_tracker ";
$sql_email_rpt .= "GROUP by email_campaign ";
$sql_email_rpt .= "ORDER by email_campaign DESC";

 

[johnwm]

Can you show us the actual query generated by that code? This is the MySQL forum, so not all of us are fluent in PHP

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[pushyr]

here's the query with the php code removed...

SELECT COUNT(email_campaign), 
COUNT(open) > 0, 
COUNT(clicks) > 0,
100.0 * COUNT(open) / COUNT(email_campaign) AS `open_rate%`,
100.0 * COUNT(clicks) / COUNT(open) AS `click_rate%`
FROM 11_email_tracker
GROUP by email_campaign
ORDER by email_campaign DESC

 

[r937]

you're going to kick youself for this, but based on the sample data you've shown, COUNT(open), COUNT(clicks) and COUNT(email_campaign) will all be identical

i think you probably want to use SUM instead of COUNT



r937.com | rudy.ca

 

[pushyr]

yes they do provide identical results and i can see why you suggest using SUM

only thing... if the values in columns 'open' and 'clicks' were '1' then SUM would tell me how many people opened and how many people clicked, but....

since the values are any value, i.e. 1, 2, 3, 4 etc. then SUM will not provide an accurate figure. see my dilemma?

i guess i could make the php script update and set any value greater than 0 to 1, and then perform the count, but that wouldn't allow me to perform additional queries on who specifically opened or clicked a certain number of times

 

[r937]

okay, before heading off to discuss "who specifically opened or clicked a certain number of times" (which is going to be really difficult, as there isn't a who column in your data), let's resolve the COUNT versus SUM issue

just what do the open and clicks columns represent?

what's the difference between an "open" value of 3 and a "open" value of 1?

if you gave better info, you wouldn't get these shot-in-the-dark attempts to help you

r937.com | rudy.ca

 

[pushyr]

not to complicate things i'd left out the who columns in my illustration of table 11_email_tracker... the columns are 'first_name', last_name', 'email', which allow me to track who specifically opened and clicked and how many times

the 'open' column represents the number of times an individual opened the email

the same as 'clicks' - how many times the individual clicked the email

sorry, i hopes that gives a better description?

 

[r937]

excellent, thanks

okay, i think i now understand why you were doing this --

COUNT(open) > 0,

try this --

SELECT email_campaign
     , opens
     , clicks
     , 100.0 * opens / rows  AS open_pct
     , 100.0 * clicks / rows AS click_pct
  FROM (
       SELECT email_campaign
            , COUNT(*)                AS rows
            , SUM(CASE WHEN open > 0
                       THEN 1
                       ELSE 0 END)    AS opens
            , SUM(CASE WHEN clicks > 0
                       THEN 1
                       ELSE 0 END)    AS clicks
         FROM 11_email_tracker
       GROUP 
           BY email_campaign
       ) as totals

r937.com | rudy.ca

 

[pushyr]

just one little problem that i can't figure out...

i get the number of opens and open rate %

but i don't get a result with number clicks? clicks comes up as 0?

 

[pushyr]

scrap that.... its working!!!

thank you r937!!!! you saved the day!