Count in subselection of column

3 Views

I need to count users (unique ID provided: user_id) based on subsets. The table looks like this (it’s a WordPress user meta table) 

mysql> describe wp_usermeta;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Now users have some settings saved in their user meta table that I need to count.

Read More
mysql> select count(user_id), meta_key, meta_value 
    from wp_usermeta 
    where meta_key = 'front_page' 
    and ( meta_value in ( 'Foo', 'Bar', 'Dragons' ) ) 
    group by meta_value;
+----------------+------------+-------------+
| count(user_id) | meta_key   | meta_value  |
+----------------+------------+-------------+
|            282 | front_page | Foo         |
|           1355 | front_page | Bar         |
|            536 | front_page | Dragons     |
+----------------+------------+-------------+
3 rows in set (0.03 sec)

This is the “preselection”. Now I need to find out which users of those who have front_page set to for e.g. Foo also have a meta_value of '1' in another row with a key of checked_in. Example: 

where meta_key = 'checked_in' and meta_value = '1'

A combined query is not a problem. I’m just missing the “intersection” between the values: 

select count(user_id), meta_key, meta_value 
    from wp_usermeta 
    where ( meta_key = 'front_page' or meta_key = 'checked_in' ) 
    and ( meta_value in ( 'Foo', 'Bar', 'Dragons', '1' ) ) 
    group by meta_value;
+----------------+------------+-------------+
| count(user_id) | meta_key   | meta_value  |
+----------------+------------+-------------+
|           3431 | checked_in | 1           |
|            282 | front_page | Foo         |
|           1355 | front_page | Bar         |
|            536 | front_page | Dragons     |
+----------------+------------+-------------+
4 rows in set (0.08 sec)

No idea how to do a subselection. Pointers welcome.

Post Views: 3

Related posts

1 comment

  1. It’s easy (and slow) to solve this by doing a subquery. Just fetch a set of user_id values and search in it with the second query.

    select distinct count(user_id), meta_key, meta_value 
from wp_usermeta 
where meta_key = 'front_page' 
    and meta_value is not null 
    and trim(meta_value) <> '' 
    and meta_value in ( 'Foo', 'Bar', 'Dragons' ) 
    and user_id in ( 
        -- this is the subquery that returns a set of `user_id`s that can be searched in
        select user_id 
        from wp_usermeta 
        where meta_key = 'checked_in' 
        and meta_value = '1' 
    ) 
    order by meta_value, user_id;

Comments are closed.