skip to Main Content

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


  1. Aggregate it twice

    select p.post_date,  m.country_id,
        sum(case when registartion_by='offline' then 1 end) Offline_registartion,
        sum(case when registartion_by='online' then 1 end) Online_registartion
    from (
       select post_id,
          max(case when meta_key ='country_id' then meta_value end) country_id, 
          max(case when meta_key ='registartion_by' then meta_value end) registartion_by,
       from wp_postmeta 
       group by post_id ) m
    join  wp_posts p on p.ID = m.post_id
    group by post_date
    

    In MySql sum(case when registartion_by='offline' then 1 end) can be shortened to sum(registartion_by='offline')

    Login or Signup to reply.
  2. 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:

    DROP TABLE IF EXISTS wp_posts;
    
    CREATE TABLE wp_posts
    (id INT AUTO_INCREMENT PRIMARY KEY
    ,post_date DATE NOT NULL
    ,post_title  CHAR(3) NOT NULL
    );
    
    INSERT INTO wp_posts VALUES
    ( 1,'2020-12-10','ABC'),
    ( 2,'2020-12-16','DEG'),
    ( 3,'2020-12-11','XXY'),
    ( 4,'2020-12-12','ABX'),
    ( 5,'2020-12-24','ZYU'),
    ( 6,'2020-12-28','ANM'),
    ( 7,'2020-12-16','DDK'),
    ( 8,'2020-12-30','LKI'),
    ( 9,'2020-12-30','LKI'),
    (10,'2020-12-31','LKI'),
    (11,'2020-12-16','TGY'),
    (12,'2020-12-16','MUN'); 
    
    DROP TABLE IF EXISTS wp_postmeta;
    
    CREATE TABLE wp_postmeta
    (meta_id INT AUTO_INCREMENT PRIMARY KEY
    ,post_id INT NOT NULL
    ,meta_key VARCHAR(20) NOT NULL
    ,meta_value VARCHAR(20) NOT NULL
    ,UNIQUE(post_id,meta_key)
    );
    
    INSERT INTO wp_postmeta VALUES
    ( 1, 1,'country_id',10),
    ( 2, 1,'registration_by','online'),
    ( 3, 3,'registration_by','offline'),
    ( 4, 4,'country_id','100'),
    ( 5, 3,'country_id','100'),
    ( 6, 4,'registration_by','online'),
    ( 7, 5,'registration_by','online'),
    ( 8, 7,'registration_by','offline'),
    ( 9, 7,'country_id','101'),
    (10, 5,'country_id','102'),
    (11, 8,'country_id','103'),
    (12, 9,'registration_by','online'),
    (13, 8,'registration_by','offline'),
    (14, 9,'country_id','10'),
    (15,10,'country_id','104'),
    (16,10,'registration_by','offline'),
    (17,11,'country_id','101'),
    (18,11,'registration_by','online');
    

    SELECT p.post_date
         , MAX(CASE WHEN m.meta_key = 'country_id' THEN m.meta_value END) country 
         , MAX(CASE WHEN m.meta_key = 'registration_by' THEN CASE WHEN m.meta_value = 'offline' THEN 1 ELSE 0 END END) offline
         , MAX(CASE WHEN m.meta_key = 'registration_by' THEN CASE WHEN m.meta_value = 'online' THEN 1 ELSE 0 END END) online
      FROM wp_posts p 
      JOIN wp_postmeta m 
        ON m.post_id = p.id
     GROUP
        BY p.id
         , p.post_date
     ORDER
        BY country
         , post_date;
         
    +------------+---------+---------+--------+
    | post_date  | country | offline | online |
    +------------+---------+---------+--------+
    | 2020-12-10 | 10      |       0 |      1 |
    | 2020-12-30 | 10      |       0 |      1 |
    | 2020-12-11 | 100     |       1 |      0 |
    | 2020-12-12 | 100     |       0 |      1 |
    | 2020-12-16 | 101     |       0 |      1 |
    | 2020-12-16 | 101     |       1 |      0 |
    | 2020-12-24 | 102     |       0 |      1 |
    | 2020-12-30 | 103     |       1 |      0 |
    | 2020-12-31 | 104     |       1 |      0 |
    +------------+---------+---------+--------+
    
    Login or Signup to reply.
  3. You can do this by joining to wp_postmeta twice, once for the country and once for the registrations:

    SELECT date(p.post_date) as Date, pmc.meta_value as Country,
           SUM( pmr.meta_value = 'online' ) as num_online,
           SUM( pmr.meta_value = 'offline' ) as num_offline
     FROM wp_posts p INNER JOIN
          wp_postmeta pmc 
          ON pmc.post_id = p.id AND
             pmc.meta_key = 'country_id' LEFT JOIN
          wp_postmeta pmr
          ON pmc.post_id = p.id AND
             pmc.meta_key = 'registion_by' 
    WHERE p.post_type = 'institute' AND
          p.post_status = 'publish'
    GROUP BY date, country;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search