AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
--
10
10/3 = 3.33 but the result is 3SELECT AVG(CAST(AmpReading AS REAL)) ...
David Portas
SQL Server MVP
--|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:

> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
10
10/3 = 3.33 but the result is 3
SELECT AVG(CAST(AmpReading AS REAL)) ...
David Portas
SQL Server MVP
|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:

> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
--
10
10/3 = 3.33 but the result is 3SELECT AVG(CAST(AmpReading AS REAL)) ...
--
David Portas
SQL Server MVP
--|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:
> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

Average of middle 90%

Hello all!

This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:
1
1 <-From here
1
2
3
4
4
5
6 <- To here.
7
I thought I could solve it by subqueries and do the following:
Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9
which would give me what I want, but I realised that when aggregating I cant order the subqueries.

This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.

I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?Do some more nesting:
Select sum(yourField) from
(select top 5% yourField from yourTable order desc)
should give you the sum of the bottom 5%.

(untested)|||Hello!

I tried you query but it didnt work. I got an syntax error on From.
I managed to solve it anyhow by using the IN-clause in the WHERE-clause like this:

Select (sum(myfield)-
(select sum(myfield) from mytable Where mytable.id IN
(select top 5 percent mytable.id from mytable Order By myfield ASC))
+
(select sum(myfield) from mytable Where mytable.id IN
(select top 5 percent mytable.id from mytable Order By myfield Desc))
)/(Count(rows)*0.9)
from mytable

But thanks for all you help anyway. Its greatly appreciated. I probably wouldnt have thought of this unless the suggestion I got. :-)|||I tried you query but it didnt work. I got an syntax error on From.
Told you it was untested :)

I'm glad you could use the idea though.|||Does this work?

SELECT Avg(x.myField)
FROM (SELECT TOP 90 PERCENT FROM myTable ORDER BY someField) As [x]|||george, come on man, slow down

the required sequence is

1. think
2. post

not the other way round!!!|||When reading this thread earlier I wrote a working subquery - that was knocked out from memory. I'm assuming your remark means that I've got it wrong?|||while i have not subjected your solution in post #5 to rigorous testing, i just do not see how it can possibly meet the stated objective of averaging the middle 90%|||I have a set of values that I need to get the average of but only from the middle 90%.
...and later:
I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?

Honestly, I don't see why you guys even bother responding to posts from people that can't even ask a straight question.|||Can you restate your problem in "real world" terms instead of trying to describe what you want the code to do? I can correctly answer the question that you've posed at least six different ways, so what I need to help you is a more exact mental image of what you want. If you describe what you want in terms of "real world" actions, I think that I can help you.

-PatP

autonumbering question - newbie

I'm just getting into SQL server, so I apologize if this is a basic
question. Most of my experience with DBs comes from Oracle, so that's
the reference from which I'm working.

I'm trying to use the identity property in MS SQL Server to autonumber
a unique identifier field, and I'm wondering if it's possible to
manually assign a value for this field in my insert statement, or if
using the identity property ties me to the sequence. I know in
Oracle, since it uses sequences, one can choose whether or not to use
the next number in the sequence to assign a value, or to do it
manually. Since the insert statement for SQL Server doesn't allow a
value (or a reference or any kind of placeholder) of any sort, I'm at
a loss as to how to manually assign a value. Is it possible?

If it's not possible, could somebody lead me in the direction of
another way to address the situation? My main concern is having to
import data from another DB x years from now and that I'll want to
keep the unique contraints and all referential integrity associated
with the importing database.

Any help anybody could give me would be greatly appreciated. Thanks!You can manually assign a value to an identity column by turning on
IDENTITY_INSERT and explicitly specifying a column list. For example:

CREATE TABLE MyTable
(
MyIdentityColumn int NOT NULL IDENTITY(1, 1),
MyData int NOT NULL
)
GO
SET IDENTITY_INSERT MyTable ON
GO
INSERT INTO MyTable (MyIdentityColumn, MyData) VALUES(1, 1)
INSERT INTO MyTable (MyIdentityColumn, MyData) VALUES(2, 1)
INSERT INTO MyTable (MyIdentityColumn, MyData) VALUES(3, 1)
GO
SET IDENTITY_INSERT MyTable OFF
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"systems_newbie" <ccrupper@.hotmail.com> wrote in message
news:5939e7a7.0308201121.4aca153b@.posting.google.c om...
> I'm just getting into SQL server, so I apologize if this is a basic
> question. Most of my experience with DBs comes from Oracle, so that's
> the reference from which I'm working.
> I'm trying to use the identity property in MS SQL Server to autonumber
> a unique identifier field, and I'm wondering if it's possible to
> manually assign a value for this field in my insert statement, or if
> using the identity property ties me to the sequence. I know in
> Oracle, since it uses sequences, one can choose whether or not to use
> the next number in the sequence to assign a value, or to do it
> manually. Since the insert statement for SQL Server doesn't allow a
> value (or a reference or any kind of placeholder) of any sort, I'm at
> a loss as to how to manually assign a value. Is it possible?
> If it's not possible, could somebody lead me in the direction of
> another way to address the situation? My main concern is having to
> import data from another DB x years from now and that I'll want to
> keep the unique contraints and all referential integrity associated
> with the importing database.
> Any help anybody could give me would be greatly appreciated. Thanks!|||It worked perfectly! Thank you very much for the solution and for the
prompt reply.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Charles Crupper" <ccrupper@.hotmail.com> wrote in message
news:3f43df99$0$200$75868355@.news.frii.net...
> It worked perfectly! Thank you very much for the solution and for the
> prompt reply.

I'll just point out a possible "flaw" in Dan's suggestion.

If two clients try to update the row with the same ID, you'll have to handle
that.

And that could cause issues if a client tries to do an insert and use the
Identity column vs. a client not using it.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||> I'll just point out a possible "flaw" in Dan's suggestion.
> If two clients try to update the row with the same ID, you'll have to
handle
> that.
> And that could cause issues if a client tries to do an insert and use
the
> Identity column vs. a client not using it.

Good point. I should have mentioned that IDENTITY_INSERT should be
used for infrequent data migration or import and not part of routine
processing.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:R5S0b.122271$wk4.47486@.twister.nyroc.rr.com.. .
> "Charles Crupper" <ccrupper@.hotmail.com> wrote in message
> news:3f43df99$0$200$75868355@.news.frii.net...
> > It worked perfectly! Thank you very much for the solution and for
the
> > prompt reply.
> I'll just point out a possible "flaw" in Dan's suggestion.
> If two clients try to update the row with the same ID, you'll have to
handle
> that.
> And that could cause issues if a client tries to do an insert and use
the
> Identity column vs. a client not using it.
>
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:y4U0b.3512$sV.364@.newsread1.news.atl.earthlin k.net...
> > I'll just point out a possible "flaw" in Dan's suggestion.
> > If two clients try to update the row with the same ID, you'll have to
> handle
> > that.
> > And that could cause issues if a client tries to do an insert and use
> the
> > Identity column vs. a client not using it.
> Good point. I should have mentioned that IDENTITY_INSERT should be
> used for infrequent data migration or import and not part of routine
> processing.

I'll admit I had to test something first and was pleasantly surprised by the
results.

I wasn't sure if the seed was updated if you did IDENTITY_INSERTs, but
apparently it is, which is nice.

(I still have nightmares about SQL 6.5 and how easily it would fubar it's
IDENTITY Seed.)

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||> (I still have nightmares about SQL 6.5 and how easily it would fubar
it's
> IDENTITY Seed.)

Yea, the whole identity assignment method was re-written for SQL 7 and
above. I have no qualms about using IDENTITY is later releases.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------

"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:bXU0b.122319$wk4.13930@.twister.nyroc.rr.com.. .
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:y4U0b.3512$sV.364@.newsread1.news.atl.earthlin k.net...
> > > I'll just point out a possible "flaw" in Dan's suggestion.
> > > > If two clients try to update the row with the same ID, you'll have
to
> > handle
> > > that.
> > > > And that could cause issues if a client tries to do an insert and
use
> > the
> > > Identity column vs. a client not using it.
> > Good point. I should have mentioned that IDENTITY_INSERT should be
> > used for infrequent data migration or import and not part of routine
> > processing.
> I'll admit I had to test something first and was pleasantly surprised
by the
> results.
> I wasn't sure if the seed was updated if you did IDENTITY_INSERTs, but
> apparently it is, which is nice.
> (I still have nightmares about SQL 6.5 and how easily it would fubar
it's
> IDENTITY Seed.)
>
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP