I have two tables like this
wp_posts
ID post_date post_title post_type post_status
1 2020-12-10 ABC institute publish
2 2020-12-16 DEG institute publish
3 2020-12-11 XXY institute publish
4 2020-12-12 ABX institute publish
5 2020-12-24 ZYU institute publish
6 2020-12-28 ANM institute publish
7 2020-12-16 DDK institute publish
8 2020-12-30 LKI institute publish
9 2020-12-30 LKI institute publish
10 2020-12-31 LKI institute publish
11 2020-12-16 TGY institute publish
12 2020-12-16 MUN institute publish
wp_postmeta
meta_id post_id meta_key meta_value
1 1 country_id 10
2 1 registartion_by online
3 3 registartion_by offline
4 4 country_id 100
5 3 country_id 100
6 4 registartion_by online
7 5 registartion_by online
8 7 registartion_by offline
9 7 country_id 101
10 5 country_id 102
11 8 country_id 103
12 9 registartion_by online
13 8 registartion_by offline
14 9 country_id 10
15 10 country_id 104
16 10 registartion_by offline
17 11 country_id 101
18 11 registartion_by online
Now I want to make some query so that the final output should be shown something like this
Date Country Offline_registration Online_registartion
2020-12-10 10 0 1
2020-12-11 100 1 0
2020-12-12 100 0 1
2020-12-16 101 1 1
2020-12-24 102 0 1
2020-12-30 103 0 1
2020-12-31 104 1 0
So to achieve this I have made some query like this
SELECT date( posts.post_date ) as Date , postmeta.meta_value as Country
FROM wp_posts as posts INNER JOIN wp_postmeta as postmeta on posts.ID = postmeta.post_id WHERE posts.post_type = 'institute' AND posts.post_status = 'publish' AND postmeta.meta_key = 'country_id' GROUP BY postmeta.meta_value
But I can’t get data of registration type count i.e(online/offline registration). So can someone tell me how to achieve that?
Any suggestions or advice would be really appreciable.
Thanks.
3
Answers
Aggregate it twice
In MySql
sum(case when registartion_by='offline' then 1 end)
can be shortened tosum(registartion_by='offline')
I don’t know why you’ve excluded
| 2020-12-30 | 10 | 0 | 1 |
from the result, so I’ll instead assume you’re after something like this:…
You can do this by joining to
wp_postmeta
twice, once for the country and once for the registrations: