Order By month and Year for 2 columns in mysql

Question

Employee table is listed below...

    Name          DOJ                  DOL
 ............   ...............      ............
    Ram            2014-01-12          2014-02-12
    Kiran          2014-02-05          2014-07-05
    Jhon           2014-01-25          2014-10-01

Expected Output is....

    Month          Joining_count     Leaving_count
  ...........     ................  .................
   Jan-2014              2                 0
   Feb-2014              1                 1
      .                  .                 .
      .                  .                 .
      .                  .                 .

I tried with below Mysql Query but i am not able get expected output please help me

        SELECT monthname(current_date) as month, 

        count( `DATE_OF_JOINING`)  as 'Joining_count' , 
        count( `DATE_OF_LEAVING`)  as 'leaving_count' , 

        group by year(`DATE_OF_JOINING`),month('DATE_OF_JOINING`),
        year(`DATE_OF_LEAVING),month(`DATE_OF_LEAVING)

score 1 · Answer 1 · edited May 23 '17 at 11:57

You are performing two different aggregations on the same data - so you'd need two aggregate queries joined. Unfortunately, mysql doesn't have a full outer join, so it's a bit of a hassle to handle both months where someone left but nobody joined and months somebody joined by nobody left. I solves this with three joins - one query to get all the possible dates, and another two for each aggregation, although there are other ways.

SELECT    my_table.d, COALESCE(join_count, 0), COALESCE(leave_count, 0)
FROM      (SELECT DISTINCT DATE_FORMAT(doj, '%b-%y') AS d
           FROM   my_table
           UNION
           SELECT DISTINCT DATE_FORMAT(dol, '%b-%y')
           FROM   my_table) dates
LEFT JOIN (SELECT   DATE_FORMAT(doj, '%b-%y') d, COUNT(*) AS join_count
           FROM     my_table
           GROUP BY DATE_FORMAT(doj, '%b-%y')
          ) joins ON dates.d = joins.d
LEFT JOIN (SELECT   DATE_FORMAT(dol, '%b-%y') d, COUNT(*) AS leave_count
           FROM     my_table
           GROUP BY DATE_FORMAT(dol, '%b-%y')
          ) leaves ON dates.d = leaves.d

Paul · Answer 2 · 2014-10-16T08:08:08.757

Basically, you need two queries, one for grouping the joining dates and one for the leaving dates. You can combine both results using UNION ALL and treat the combined result as a new table and select (and SUM) the values then.

SELECT 
  s.month, 
  SUM(s.Joining_count) AS Joining_count, 
  SUM(s.Leaving_count) AS Leaving_count
FROM
  (
   SELECT 
     DATE_FORMAT(doj, '%b-%y') as `month`, 
     COUNT(ej.doj)  as 'Joining_count',
     0 AS 'Leaving_count'
   FROM employee ej
     GROUP BY `month`

   UNION ALL

   SELECT 
     DATE_FORMAT(dol, '%b-%y') as `month`, 
     0  as 'Joining_count',
     COUNT(el.dol)  as 'Leaving_count'  
   FROM employee el
     GROUP BY `month`
  ) s
GROUP BY s.`month`;

Output will be

month       Joining_count   Leaving_count
---------------------------------------------
Feb-2014    1               1
Jan-2014    2               0
Jul-2014    0               1
Oct-2014    0               1

Order By month and Year for 2 columns in mysql

2 Answers2