How to get values for different rows and multiple columns in sql-CodePudding

I want to get data from three different tables

Table 1 : iep_start_lp_request

lp_id	certifications_check	EmpName	EmpOldID	created_date
6516	yes	A Hemalatha	113111	2022-01-20 08:55:27
6332	yes	A V Shruthi	110816	2022-01-20 08:55:27
6503	no	A. Divya	117909	2022-02-20 08:55:27
11989	yes	Aabha Verma	110415	2022-02-20 08:55:27
6401	no	Aadarsh Keerti	115899	2022-03-20 08:55:27
8864	yes	Aakanksha Rehan	2150618	2022-03-20 08:55:27
8786	yes	Aakarsh Bhatia	110081	2022-05-20 08:55:27

Table2 : iep_employee_list

EmpName	EmpOldID
A Hemalatha	108782
A Hemalatha	113111
A Hemalatha	110421
A R Shashikumar	118531
A S Kundana Sai	115788
A Satheesh	112663
A Suresh	117851
A Umiya Begum	112956
A V Ramanathan	116010
A V Shruthi	110816
A. Divya	117909

Table 3 : iep_postmeta

post_id	meta_key	meta_value
8865	select_category	a:1:{i:0;s:18:"AWS Certifications";}
8603	select_category	a:1:{i:0;s:21:"Google Certifications";}
6656	select_category	a:1:{i:0;s:24:"Microsoft Certifications";}

I need to get count of employees who completed different category certifications in each month

Below is the query I tried

select SUBSTRING_INDEX(SUBSTRING_INDEX( certicatname.`meta_value`,':"',-1) ,'";}',1)
    as 'certicatname',
       max(case when (lp2.`created_date` BETWEEN '2022-04-01' AND '2022-01-01') then COUNT(lp2.`user_emp_id`) end) as Jan,
       max(case when (lp2.`created_date` BETWEEN '2022-08-01' AND '2022-04-01') then COUNT(lp2.`user_emp_id`) end) as Feb,
       max(case when (lp2.`created_date` BETWEEN '2022-12-01' AND '2022-08-01') then COUNT(lp2.`user_emp_id`) end) as mar
    
    from
    `iep_start_lp_request` as lp2 LEFT JOIN `iep_employee_list` as emplyoee2 ON lp2.`user_emp_id`=emplyoee2.`EmpOldID` LEFT JOIN `iep_postmeta` as certicatname ON certicatname.`post_id`=lp2.`lp_id` WHERE (certicatname.`meta_value` REGEXP '.*;s:[0-9] :"AWS Certifications".*' OR certicatname.`meta_value` REGEXP '.*;s:[0-9] :"Microsoft Certifications".*' OR certicatname.`meta_value` REGEXP '.*;s:[0-9] :"Google Certifications".*' ) AND lp2.`certifications_check`='yes' GROUP BY certicatname.`meta_value` ORDER BY certicatname.`meta_value`;

CodePudding user response：

Can you try this :

select
  SUBSTRING_INDEX(SUBSTRING_INDEX( ip.meta_value,':"',-1) ,'";}',1) as certificate,
  coalesce(count(case when month(isr.created_date) = 1 then 1 end), 0) as JAN,
  coalesce(count(case when month(isr.created_date) = 2 then 1 end), 0) as FEB,
  coalesce(count(case when month(isr.created_date) = 3 then 1 end), 0) as MARS
from iep_start_lp_request isr
inner join iep_employee_list iel on iel.EmpOldID = isr.EmpOldID
inner join iep_postmeta ip on ip.iep_postmeta = isr.lp_id
where isr.certifications_check = 'yes'
group by ip.meta_value, month(isr.created_date);

I used almost same logic as yours : SUBSTRING_INDEX to get certificate name, case when to generate columns from rows. and if we want data based on certificate and month then we need to group by both not only certificate.

You can try it from here https://dbfiddle.uk/l-PV7_zj