mysql user signup counts between groups

Lets say I have a table 'wp_users' with the following data:

Name            user_registered        ID
dog            2008-05-14 18:53:30     1
cat            2008-05-14 12:13:20     2
mouse          2008-05-14 08:51:32     3
giraffe        2008-05-15 22:13:31     4
Moose          2008-05-16 13:20:30     5
monkey         2008-05-16 08:51:32     6
mongoose       2008-05-16 22:13:31     7
fish           2008-05-16 13:00:30     8

I then have another table 'wp_usermeta' where wp_users.ID == wp_usermeta.user_id:

user_id   meta_key               meta_value
1         wp_capabilities            a:1:{s:10:"subscriber";s:1:"1";}
2         wp_capabilities         a:1:{s:15:"s2member_level2";s:1:"1";}
2         wp_s2member_subscr_id    I-SDJKSDD
3         wp_capabilities          a:1:{s:15:"s2member_level2";s:1:"1";}
3         wp_s2member_subscr_id    I-sdfsdfsdf
4         wp_cabilities            a:1:{s:10:"subscriber";s:1:"1";}
5         wp_cabilities            a:1:{s:10:"subscriber";s:1:"1";}
6         wp_cabilities            a:1:{s:10:"subscriber";s:1:"1";}
7         wp_capabilities          a:1:{s:15:"s2member_level2";s:1:"1";}
7         wp_s2member_subscr_id    I-sd45gds
8         wp_capabilities          a:1:{s:15:"s2member_level2";s:1:"1";}
8         wp_s2member_subscr_id    I-3fskkhh

What I'm looking for is a way to generate some summary columns that tallies the number of subscriber signups vs. s2member_level2 signups for every day of the wp_users.user_registered date (I don't care about hour of day, just date). To define those a little better:

"subscriber": will NOT have a row where meta_key = wp_s2member_subscr_id - it just doesn't exist. Notice how users 1,4,5 and 6 do not have this row so they are "subscribers". Also in the row "wp_capabilities" they will have the word "subscriber" instead of "s2member_level2". Both criteria do NOT have to be met because they are both true for all subscribers (I mention both in case one is easier to check for than another).

"s2member_level2": DOES have a row where meta_key = wp_s2mem开发者_运维技巧ber_subscr_id with some random letter values (users 2,3,7,8 in tables above). It also will have the word "s2member_level2" in the value for meta_value. Both criteria do NOT have to be met because they are both true for all s2member_level2's (I mention both in case one is easier to check for than another).

The ideal output would then look like this for the example tables above:

Date              subscriber     s2member_level2
2008-05-14         1              2
2008-05-15         1              0
2008-05-16         2              2

This is a more complicated question of the question (and answer) found here that just looked for total number of subscribers of any kind. This seeks to differentiate between the subscribers vs. s2member_level2 . I'm assuming this can be done using some fancy join statements and would appreciate any help!

select date, SUM(case when subscriber = 1 and s2member_level2 = 0 then 1 else 0 end) subscriber,
           SUM(case when s2member_level2 = 1 then 1 else 0 end) s2member_level2
from
(   select  date(user_registered) date,
             id,
            sum(case when B.meta_key = "wp_capabilities" then 1 else 0 end) as subscriber,
            sum(case when B.meta_key = "wp_s2member_subscr_id" then 1 else 0 end) as s2member_level2
    from wp_users A LEFT JOIN wp_usermeta B ON A.Id = B.user_id            
    group by date(user_registered), id
) C
group by date

Heres a workable solution using a temporary table.

First of all, get a line for each user and bring together all of their 'meta keys' as well as dropping the time portion from the datetime stamp:

CREATE TEMPORARY TABLE user_keys
SELECT wp_users.ID, 
       DATE_FORMAT(user_registered,'%Y-%m-%d') AS user_registered
       GROUP_CONCAT(meta_key) AS meta_keys
FROM wp_users
LEFT JOIN wp_usermeta AS subs ON (wp_users.ID = subs.user_id)
GROUP BY wp_users.ID
ORDER BY wp_users.ID

Then SELECT on your temporary table to check what keys each user has and tally them up:

SELECT user_registered, 
   SUM(IF(FIND_IN_SET('wp_s2member_subscr_id',meta_keys) = 0, 1,0)) AS subscriber,
   SUM(IF(FIND_IN_SET('wp_s2member_subscr_id',meta_keys) > 0, 1,0)) AS s2member_level2
FROM user_keys
GROUP BY user_registered
ORDER BY user_registered

I bet someone can come up with a single query solution with all manner of subselects and whatnot, but this works with your example data...

if you use a trigger and a summary table as suggested in your earlier question your inserts will be a little slower but you'll be able to query quickly without on the fly aggregation:

tally/count mysql results per day