2

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 may want to explain why you think the result of your query "is not correct" and what a "correct" result might look like. – mustaccio Jul 6 '15 at 13:42
  • I updated my question. I am getting total number of rows under total_calls, but my expectation is to get 3 instead of 6. The total_calls should be the number of calls by employee. – Bikram Pahi Jul 6 '15 at 14:42
3

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> 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> 
|improve this answer|||||
  • You could also correlate the subquery with the main query by changing its condition to WHERE call_type=d.call_type. Not sure if that would have any implications on performance, but at least you would avoid the possible mistake of changing the argument ('SALES') in one place and forgetting to do that in another. – Andriy M Jul 7 '15 at 5:58
2

Thanks for all your efforts, I did this way.

select a.emp_id, a.total, b.TOTAL_CALLS 
from (SELECT a.emp_id,count(*) total FROM `Dash_Agent_Disp` a where a.call_type = 'sales' GROUP BY a.emp_id) a, 
(select emp_id, count(*) total_calls from `Dash_Agent_Disp` group by emp_id) b
WHERE a.emp_id=b.emp_id

Thanks All.

|improve this answer|||||
  • If your found your answer, please, select one to close the post. – oNare Jul 8 '15 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.