Need help with this SQL query

advertisements

This is an example of my table. I have multiple entries on multiple rows for a given post_id (this is metadata for posts).

post_id | meta_key | meta_value
________ __________ ___________
        |          |
1       | _theDate | 2016-03-31
1       | _email   | [email protected]
2       | _theDate | 2016-01-06
2       | _email   | [email protected]
3       | _theDate | 2017-02-14
3       | _email   | [email protected]
4       | _theDate | 2016-10-01
4       | _email   | [email protected]
5       | _theDate | 2016-09-25
5       | _email   | [email protected]
6       | _theDate | 2015-11-19
6       | _email   | [email protected]

What I am trying to accomplish:

I would like to find all instances of a post with the email address [email protected] and the year "2016" in the metadata, and then count those individual posts to find out how many posts were written by the user [email protected] during the year "2016".

For the moment I have managed to find only the instances of the email address using

SELECT DISTINCT post_id
FROM  metatable
WHERE meta_value LIKE '%[email protected]%'

This counts the total posts for that user but not only the ones written in 2016.


Here is one method that uses two levels of aggregation :

SELECT COUNT(*)
FROM (SELECT post_id
      FROM  metatable
      WHERE (meta_key = '_email' AND meta_value = '[email protected]') OR
            (meta_key = '_theDate' AND LEFT(meta_value, 4) = '2016')
      GROUP BY post_id
      HAVING COUNT(DISTINCT meta_key) = 2
     ) p;

Edit : missing a quote