Member Avatar for danieldiesel12

Hi!


I´m trying to have a datagrid with some data. It includes a subquery and does only work if I define the parameter. I don´t want that. I would like to group it.


The query is:

SELECT count(*)*1.0/

(select count(*) from events_may_2010 where agent = '5013' and convert(varchar,call_date,112)= convert(varchar,GETDATE(),112) and call_status in ('1110', '1199', '1002') )*100


 FROM events_may_2010 where agent = '5013' and convert(varchar,call_date,112)= convert(varchar,GETDATE(),112) and call_status in ('1110')

The query count how many outbound calls an agent has with call status '1110' divided by his/hers outbound calls with call_status '1110', '1199', '1002'.
That gives me the hitrate.


However. I want to get the result grouped by agents. Can I do that in some way and always be counting and dividing agents specific call status? As for now I must specify the agent in both select statements and I only return one result. If I don´t specify the agent in a where clause all agents call_status will be counted.

Best regards Daniel

Edited by danieldiesel12 because: n/a
  • 2 Contributors
  • 1 Reply
  • 98 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by pclfw
Member Avatar for pclfw

Have you tried

SELECT count(*)*1.0/
       
      (SELECT count(*) FROM events_may_2010 WHERE convert(varchar,call_date,112)= convert(varchar,GETDATE(),112) AND call_status IN ('1110', '1199', '1002') )*100
       
      FROM events_may_2010 WHERE convert(varchar,call_date,112)= convert(varchar,GETDATE(),112) AND call_status IN ('1110')

GROUP BY agent

ORDER BY agent
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.