CODEWITHSUNDEEP
mysqlsql
Dileep Kumar
Dileep Kumar

Reputation: 510

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

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`)

Upvotes: 0

Views: 1179

Answers (1)

user757095
user757095

Reputation:

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

Upvotes: 3

Related Questions