CODEWITHSUNDEEP
mysqlsqlcount
faraz
faraz

Reputation: 233

Get count of departmant with total employees for period of month in mysql

I have requirement to get count of distinct department with total employees in period of month but unfortunately query is not working and throwing error

My table

Department_id emloyee_id   date_time

  1             1          2020-02-01
  1             2          2020-02-04 
  3             7          2020-02-06
  1             4          2020-02-07

expected output

total department=2
total employee of all department=4

But all should work based on last one record , I am getting sql syntax error

Query:

SELECT COUNT(DISTINCT department_id) x, COUNT(*) y 
  FROM department 
 WHERE date_time>=DATE_FORMAT(NOW() ,'%Y-%m-01') 
   AND date_time<DATE(NOW()+INTERVAL 1 DAY and status='1'

Upvotes: 1

Views: 645

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

You can combine them within only one query :

SELECT COUNT(DISTINCT Department_id), COUNT(DISTINCT employee_id)
  FROM department  
 WHERE date_time >= NOW() - INTERVAL 1 MONTH
   AND status = '1';

counting both distinctly.

Update : If you mean to stay within the current month, then also

AND date_time>=DATE_FORMAT(NOW() ,'%Y-%m-01')

might be added to this query as in your original one.

Upvotes: 3

mattyx17
mattyx17

Reputation: 826

It seems you should use month instead of day and are missing a bracket after month 

SELECT COUNT(DISTINCT department_id) AS departments,
       COUNT(*) AS employees
FROM department
WHERE date_time>=DATE_FORMAT(NOW() ,'%Y-%m-01')
AND date_time < DATE(NOW()+INTERVAL 1 MONTH)
AND status = '1';

Upvotes: 1

Related Questions