Newbie mystery

Try running the code below in QA

USE Northwind
--The quesy below produces the correct numbers.
SELECT CategoryID,(100*((COUNT(*)+.0)/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--The query below produces 0 values and are wrong.
SELECT CategoryID,(100*((COUNT(*))/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--This is the total
SELECT COUNT(*) AS TotalCount FROM Products
--The totals of the groupings
SELECT CategoryID,(COUNT(*)) AS Category_Total FROM Products GROUP BY CategoryID

I think I understand what happens with the above, but what I really want to know is there a good coding habit to prevent it. Some of our reports are very complex and an error could be missed.Dear,

In MSSQL, this is normal behaviour. When you divide an integer (count) by another integer (count), the result is an integer.

Example :

select 1/3 returns 0

select 1/cast(3 as numeric) returns .33333

That is just the way it is, and it is documented in BOL.

Regards,

CVM.

Newbie ahoy! - SQL Server database keeps freezing...

Hi,
I've something of a mystery.
Our DB currently runs as a SQL Server 2000 database linked to a number of
Access MDB front ends via an ODBC connection.
We're having problems with the response of the server. Basically, when a
user is entering / updating data, when they try to update a record, the ODBC
connection times out.
I've checked the server and there doesn't appear to be any problems with
SQL2000.
However, I am something of a complete newbie when it comes to SQL server. In
light of this can anyone recommend any steps I should take to investigate
and diagnose this.
In particular, I know SQL server keeps logs but I'm not sure to what detail
and how to access them.
Sorry to appear like a complete idiot but I guess we all have to start
somewhere. :)
Thanks
Chris StrugA common cause of these symptoms is blocking. You can run sp_who2 from
Query Analyzer to identify the blocked/blocking processes. Note that a
SELECT statement will hold locks until the resultset is processed so it
is important that the application to retrieve results as soon as
possible and keep transactions short.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Chris Strug" <hotmail@.solace1884.com> wrote in message
news:%23Ti2WgETDHA.2852@.tk2msftngp13.phx.gbl...
> Hi,
> I've something of a mystery.
> Our DB currently runs as a SQL Server 2000 database linked to a number
of
> Access MDB front ends via an ODBC connection.
> We're having problems with the response of the server. Basically, when
a
> user is entering / updating data, when they try to update a record,
the ODBC
> connection times out.
> I've checked the server and there doesn't appear to be any problems
with
> SQL2000.
> However, I am something of a complete newbie when it comes to SQL
server. In
> light of this can anyone recommend any steps I should take to
investigate
> and diagnose this.
> In particular, I know SQL server keeps logs but I'm not sure to what
detail
> and how to access them.
> Sorry to appear like a complete idiot but I guess we all have to start
> somewhere. :)
> Thanks
> Chris Strug
>