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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help with DISTINCT

Author  Topic 

mericlese
Starting Member

11 Posts

Posted - 2007-06-04 : 12:13:55
Alright, here we have a query that returns the results that I want:

select distinct CallActions.CallID from Calls inner join CallActions on Calls.CallID = CallActions.CallID
where Calls.OpenDate >= '5/28/2007' and Calls.OpenDate <= '6/4/2007' and CallActions.ActionTaken = 'Assign'

That works perfectly. My problem is that I'm trying to get a count of those results, so I was trying this:

select count(*) as TotalAssigned from (select distinct CallActions.CallID from Calls inner join CallActions on Calls.CallID = CallActions.CallID where Calls.OpenDate >= '5/28/2007' and Calls.OpenDate <= '6/4/2007' and CallActions.ActionTaken = 'Assign')

This doesn't work and Query Analyzer tells me that I have an error around ")". Can anyone tell me what the problem is with the second query or tell me how I can get a count on the first query. Thanks in advance.

-Seth

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-04 : 12:19:19
[code]select
count(distinct CallActions.CallID) as TotalAssigned
from
Calls inner join CallActions on Calls.CallID = CallActions.CallID
where
Calls.OpenDate >= '20070528' and Calls.OpenDate <= '20070604' and CallActions.ActionTaken = 'Assign'[/code]

Note: Oh, and BTW, in your approach, you missed table alias, hence the error.

For example,

[code]Select count(*) from
(
select ...
) as temp[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

mericlese
Starting Member

11 Posts

Posted - 2007-06-04 : 12:21:32
Thanks :) Works great.
Go to Top of Page
   

- Advertisement -