Any way to improve this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Any way to improve this query?

Author

Topic

JarWithLid
Starting Member

10 Posts

Posted - 2007-12-20 : 18:11:20

The query is to get the average number of kbarticles written per month. It is accomplished by dividing the total number of articles written by the number of different months articles have been written in.


SELECT CAST( (SELECT COUNT(1) FROM kbarticles) AS DECIMAL(2) ) / CAST( COUNT(1) AS DECIMAL(2)) AS perMonthAvg
FROM   (
          SELECT    count(1) as cnt
          FROM      kbarticles
          GROUP BY  (CAST(month(cdate) as varchar(2)) + cast(year(cdate) as varchar(4)))
        ) as total

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-20 : 19:16:08

see if this is any faster:


select count(1) / cast(count(distinct datediff(month, 0, cdate)) as decimal(2)) AS perMonthAvg
from kbarticles

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-21 : 03:58:09

And you need to multiply the first count by 1.0 for accuracy

select count(1)*1.0 / cast(count(distinct datediff(month, 0, cdate)) as decimal(2)) AS perMonthAvg
from kbarticles

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-21 : 08:32:17

quote:
Originally posted by madhivanan

And you need to multiply the first count by 1.0 for accuracy

select count(1)*1.0 / cast(count(distinct datediff(month, 0, cdate)) as decimal(2)) AS perMonthAvg
from kbarticles

I hate to contradict someone with a gajillion posts but...I beg to differ.

You only need to set the one of the operators to a more precise datatype (I always use the denominator).

Here is an exceprt from BOL topic "Data Type Precedence":

quote:
The data type with the lower precedence is converted to the data type with the higher precedence

compare the results of these 2 statememtns:


select count(1)*1.0 / cast(count(distinct datediff(month, 0, refdate)) as decimal(2)) AS perMonthAvg
from sysobjects

select count(1) / cast(count(distinct datediff(month, 0, refdate)) as decimal(2)) AS perMonthAvg
from sysobjects

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-21 : 10:15:35

quote:
Originally posted by TG

quote:
Originally posted by madhivanan

And you need to multiply the first count by 1.0 for accuracy

select count(1)*1.0 / cast(count(distinct datediff(month, 0, cdate)) as decimal(2)) AS perMonthAvg
from kbarticles

I hate to contradict someone with a gajillion posts but...I beg to differ.

You only need to set the one of the operators to a more precise datatype (I always use the denominator).

Here is an exceprt from BOL topic "Data Type Precedence":
quote:
The data type with the lower precedence is converted to the data type with the higher precedence

compare the results of these 2 statememtns:

select count(1)*1.0 / cast(count(distinct datediff(month, 0, refdate)) as decimal(2)) AS perMonthAvg
from sysobjects

select count(1) / cast(count(distinct datediff(month, 0, refdate)) as decimal(2)) AS perMonthAvg
from sysobjects

Be One with the Optimizer
TG

Well. I thought you didnt cast it to decimal (I forgot to notice that)

and FYI

select 1/2*1.0
select 1*1.0/2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources