How to get count of employees with total employee with group by year

Question

This is my employee table

empid   name     Date_of_joining
  1      dilip       2010-01-30
  2      suresh      2001-03-01
  3      ramesh     2003-01-01

I want to get the number of employees with total employees group by employee date of joining

expected output

   year    new joining    total employees
   2001          10                10
   2002          12                22
   2003          15                27

query

 select YEAR(`DATE_OF_JOINING`) as 'year', COUNT(*) as 'count1',sum(count(*)) from employee 

 GROUP BY YEAR(`DATE_OF_JOINING`)

Since we don't know anything about your table(s) ... how can we help? — Stultuske, Dec 03 '15 at 10:15
And the problem is ?.... ther query seems ok. what's the problem? error? — ScaisEdge, Dec 03 '15 at 10:15
Possible duplicate of [Calculate a running total in MySQL](http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql) — , Dec 03 '15 at 10:43

score 3 · Answer 1 · 2015-12-03T19:12:27.070

You need a running total using a user defined variable.

You need a derived table cause running totals don't work with group by statement

SET @SUM = 0;
SELECT
  YEAR,
  NoOfEmployee AS newJoining,
  (@SUM := @SUM + NoOfEmployee) AS totalJoining
FROM (
    SELECT
      YEAR(Date_of_joining) AS YEAR,
      COUNT(*) AS NoOfEmployee
    FROM
      employees
    GROUP BY
      YEAR(Date_of_joining)
  ) O

here a sample

How to get count of employees with total employee with group by year

1 Answers1