Issue with count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue with count

Author

Topic

danieldiesel12
Starting Member

4 Posts

Posted - 2010-05-07 : 15:18:51

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

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-07 : 15:38:59

yes daniel, you can group data by agents.
please provide at least table columns or even same sample data to help you with this query

danieldiesel12
Starting Member

4 Posts

Posted - 2010-05-07 : 17:36:19

Slimt_slimt

table structure is:

call_date smalldatetime
whole_phone_number varchar(50)
segment varchar(50)
agent varchar(50)
campaign varchar(50)
call_status smallint
verifier varchar(50)
call_type tinyint
DNIS varchar(20)
wait_duration real
call_duration real
queue_duration real
talk_duration real
wrap_duration real

Sample data:

call_date whole_phone_number segment agent campaign call_status verifier call_type DNIS wait_duration call_duration queue_duration talk_duration wrap_duration
2010-05-03 16:11:00 0851172185 FF 1030 FRF_100426 1199 NULL 1 28,22 36,19 0 27,14 0
2010-05-03 16:11:00 013124152 FF 1023 FRF_100426 1199 NULL 1 23,06 23,67 0 17,53 0
2010-05-03 16:11:00 0520441634 FF 1011 FRF_100426 1199 NULL 1 21,48 42,14 0 28,25 0
2010-05-03 16:12:00 051091086 SRK 1012 LYK_100429 1110 NULL 1 28,61 322,26 0 297,54 0
2010-05-03 16:12:00 0520441640 FF 1030 FRF_100426 1199 NULL 1 4,44 67,55 0 55,68 0
2010-05-03 16:12:00 051221173 SRK 1033 LYK_100429 1110 NULL 1 7,06 233,47 0 216,63 0
2010-05-03 16:12:00 052341180 FF 1029 FRF_100426 1199 NULL 1 29,27 119,46 0 92,64 0
2010-05-03 16:12:00 047910333 SRK 1027 LYK_100429 1199 NULL 1 12,88 112,82 0 95,39 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 06:50:22

you want sum of which all values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources