Using aggregate function count() 
Author Message
 Using aggregate function count()

Hi,

I am trying to excute the following in order to count the
number of requests every user has made for a test in the
specified month, the count value needs to be qualified by
dividing by 1000:

select user_id, (count(user_id) / 1000) requestcount  
from testtable
where monthindex = 1
group by user_id, monthindex
order by user_id

The field user_id is an int. My problem is that the count
function will give a result of between 1 and 9 and when
this is divided by 1000 requestcount displays 0, not what
I need. Any ideas on how to slove this?



Sun, 09 Jan 2005 19:32:17 GMT
 Using aggregate function count()

Instead of diving with 1000, divide by 1000.0. This will force SQL Server to
think decimal, and not integer.

select user_id, (count(user_id) / 1000.0) requestcount
from testtable
where monthindex = 1
group by user_id, monthindex
order by user_id

HTH,
--
Andrs Taylor


http://www.sql.nu/


Quote:
> Hi,

> I am trying to excute the following in order to count the
> number of requests every user has made for a test in the
> specified month, the count value needs to be qualified by
> dividing by 1000:

> select user_id, (count(user_id) / 1000) requestcount
> from testtable
> where monthindex = 1
> group by user_id, monthindex
> order by user_id

> The field user_id is an int. My problem is that the count
> function will give a result of between 1 and 9 and when
> this is divided by 1000 requestcount displays 0, not what
> I need. Any ideas on how to slove this?



Sun, 09 Jan 2005 19:43:01 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. distinct count in Aggregate function?

2. Distinct Count - Aggregate Function on Measures

3. distinct count in Aggregate function

4. Problem with Distinct Count Aggregate function

5. Distinct Count Aggregate Function?

6. Count() aggregate function with ODBC

7. Puzzler: Distinct counts on aggregate data using cube

8. Can you have an aggregate function over an aggregate

9. Using Avg Aggregate function in Measures?

10. Using Distinct and Aggregate Functions

11. Using Aggregate Function in Select Case

12. using aggregate functions


 
Powered by phpBB® Forum Software