Need some assistance on MySQL Sub query I have a table in MySQL.

The table structure is as follows.

id   |  emp_id  |  call_start  |  call_end  |  call_type
--------------------------------------------------------
1    | 20043    | 20:20:10     | 20:21:19   | SALES
2    | 20043    | 20:25:34     | 20:27:12   | NON-SALES
3    | 20044    | 21:25:34     | 21:27:12   | SALES
4    | 20044    | 22:25:34     | 22:27:12   | SALES
5    | 20044    | 22:45:34     | 22:47:12   | NON-SALES
6    | 20043    | 21:25:34     | 21:27:12   | NON-SALES


I want to select data as total number of calls by an employee, total sales call by an employee.

What will be the appropriate query?

I have written something but result is not correct :

SELECT 
  emp_id, 
  count(*) total, 
  (SELECT COUNT(*) AS TOTAL_CALL FROM Dash_Agent_Disp) total_calls,call_type 
FROM 
  `Dash_Agent_Disp` 
where 
  call_type like 'SALES' 
GROUP BY 
  employee_id


The out put of the query is :

emp_id  |  total  | total_calls  |  call_type
----------------------------------------------
20043   |   1     |   6          |  SALES
20044   |   2     |   6          |  SALES


My Expectation is to get :

emp_id  |  total  | total_calls  |  call_type
----------------------------------------------
20043   |   1     |   3          |  SALES
20044   |   2     |   3          |  SALES
You need to filter you subquery with call_type='SALES', then run:

I use your information:

INSERT INTO test.Dash_Agent_Disp
    (id,emp_id,call_start,call_end,call_type)
VALUES
    ('1','20043','20:20:10','20:21:19','SALES'),
    ('2','20043','20:25:34','20:27:12','NON-SALES'),
    ('3','20044','21:25:34','21:27:12','SALES'),
    ('4','20044','22:25:34','22:27:12','SALES'),
    ('5','20044','22:45:34','22:47:12','NON-SALES'),
    ('6','20043','21:25:34','21:27:12','NON-SALES');


Query: 

SELECT 
    d.emp_id, 
    count(d.id) total, 
    (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls,
    d.call_type
FROM Dash_Agent_Disp as d
WHERE d.call_type='SALES'
GROUP BY 
    d.emp_id,
    d.call_type;


Result:

mysql&gt; SELECT  d.emp_id,  count(d.id) total,  (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls, d.call_type FROM Dash_Agent_Disp as d WHERE d.call_type='SALES' GROUP BY  d.emp_id, d.call_type;
+--------+-------+-------------+-----------+
| emp_id | total | total_calls | call_type |
+--------+-------+-------------+-----------+
|  20043 |     1 |           3 | SALES     |
|  20044 |     2 |           3 | SALES     |
+--------+-------+-------------+-----------+
2 rows in set (0.00 sec)

mysql&gt;