CODEWITHSUNDEEP
phpsql
fightstarr20
fightstarr20

Reputation: 12598

SQL count occurrences of value on specific date

I have an SQL database with the following two tables....

wp_mymeta1....

    id   |   my_id   |   field_number   |   field_id   |   value
    ------------------------------------------------------------
    1    |    101    |       78         |     88       |   apple
    2    |    287    |       76         |     55       |   orange
    3    |    893    |       45         |     33       |   orange
    4    |    922    |       23         |     33       |   grape
    5    |    198    |       09         |     88       |   raisin
    6    |    082    |       55         |     88       |   apple

and wp_mymeta2....

    my_id   |   date_posted   
    -----------------------
    101    |    01-01-2014 
    287    |    17-01-2014 
    893    |    24-02-2014 
    922    |    18-03-2014 
    198    |    04-04-2014 
    082    |    16-04-2014

I am succesfully using the following to count the ocurrences of 'apple'....

$count = $wpdb->get_results("SELECT COUNT(*) as count FROM wp_mymeta1 WHERE value='apple'" );

I am now trying to take this a step further and count the occurrences of the value 'apple' that were posted on a specified date.

As the date_posted is stored in a different table i'm having trouble linking the two. Can anyone help?

Upvotes: 0

Views: 69

Answers (2)

PVC
PVC

Reputation: 101

Try this:

SELECT wp_mymeta2.date_posted, COUNT(wp_mymeta1.my_id)
FROM wp_mymeta1, wp_mymeta2 
WHERE value='apple' AND wp_mymeta2.my_id = wp_mymeta1.my_id
GROUP BY wp_mymeta1.my_id, wp_mymeta2.date_posted;

Upvotes: 0

Andreas
Andreas

Reputation: 5103

SELECT COUNT(*) AS count
FROM wp_mymeta1 a
JOIN wp_mymeta2 b ON b.my_id = a.my_id
WHERE a.value = 'apple'
    AND b.date_posted = '01-01-2014'

Upvotes: 3

Related Questions