Autonumber field: select

Showing posts with label select. Show all posts

Tuesday, March 27, 2012

Backing Up Muliple Databases in Same Instance

I've 6 databases in the same instance which I wish to se a backup routine
for. How can I select all the databases at a go and create a backup procedure
just once in enerprise manager. I tried highlighting all he databases, but
each time I click backup up. he sysem comes up with one database at a time
for me o create backup for.
--
RegardsIs this something you want to schedule or do only once?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
> I've 6 databases in the same instance which I wish to se a backup routine
> for. How can I select all the databases at a go and create a backup procedure
> just once in enerprise manager. I tried highlighting all he databases, but
> each time I click backup up. he sysem comes up with one database at a time
> for me o create backup for.
> --
> Regards|||I want to schedule it.
--
Regards
"Tibor Karaszi" wrote:
> Is this something you want to schedule or do only once?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
> news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
> > I've 6 databases in the same instance which I wish to se a backup routine
> > for. How can I select all the databases at a go and create a backup procedure
> > just once in enerprise manager. I tried highlighting all he databases, but
> > each time I click backup up. he sysem comes up with one database at a time
> > for me o create backup for.
> > --
> > Regards
>|||Create a Maintenance Plans. The regular backup dialog doesn't support selecting multiple databases,
but a Maint Plans does.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:7EFD5154-BBB8-40D0-8791-51EF2B744CC3@.microsoft.com...
>I want to schedule it.
> --
> Regards
>
> "Tibor Karaszi" wrote:
>> Is this something you want to schedule or do only once?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
>> news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
>> > I've 6 databases in the same instance which I wish to se a backup routine
>> > for. How can I select all the databases at a go and create a backup procedure
>> > just once in enerprise manager. I tried highlighting all he databases, but
>> > each time I click backup up. he sysem comes up with one database at a time
>> > for me o create backup for.
>> > --
>> > Regards
>>

Backing Up Muliple Databases in Same Instance

Friday, February 24, 2012

Avoid duplicates in SELECT

SELECT *
FROM FUND
INNER JOIN POSITION ON FUND.ACCT = POSITION.ACCT
INNER JOIN SECURITY ON POSITION.SEC_ID = SECURITY.SEC_ID
WHERE (FUND.MANAGER = 'smith')
I get duplicate SEC_ID's with this query because Smith may be the manager of
multiple funds that hold positions in the same security. For this query I
only need to return one record per security, doesn't matter which.
Any help would be appreciated.Ah, the old "any row will do" trick... why bother with the extra data if it
really doesn't matter which row it is?
Can you provide DDL, sample data, and desired results? Please see
http://www.aspfaq.com/5006 to help you give better specs and get better
answers
"Terri" <terri@.cybernets.com> wrote in message
news:dbmmpg$4rg$1@.reader2.nmix.net...
> SELECT *
> FROM FUND
> INNER JOIN POSITION ON FUND.ACCT = POSITION.ACCT
> INNER JOIN SECURITY ON POSITION.SEC_ID = SECURITY.SEC_ID
> WHERE (FUND.MANAGER = 'smith')
> I get duplicate SEC_ID's with this query because Smith may be the manager
> of
> multiple funds that hold positions in the same security. For this query I
> only need to return one record per security, doesn't matter which.
> Any help would be appreciated.
>|||At least I didn't ask for the "first" row :)
In this case it truly doesn't matter. The business problem is, Provide a
unique list of securities held by each manager. Managers don't hold
securities directly, they manage multiple funds which in turn hold
securities. Let me get together some DDL. Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Ht5xFYjFHA.232@.TK2MSFTNGP10.phx.gbl...
> Ah, the old "any row will do" trick... why bother with the extra data if
it
> really doesn't matter which row it is?
> Can you provide DDL, sample data, and desired results? Please see
> http://www.aspfaq.com/5006 to help you give better specs and get better
> answers

>|||> At least I didn't ask for the "first" row :)
Well, it's pretty much the same concept, and the same kind of problem that
SQL Server doesn't know how to solve.|||How does DISTINCT "know" which value to select from a list that contains
duplicates?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e4Vr8cgjFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Well, it's pretty much the same concept, and the same kind of problem that
> SQL Server doesn't know how to solve.
>|||>> How does DISTINCT "know" which value to select from a list that contains
duplicates? <<
A table is not a list. And the result set is a new table build from
the query. You are still thinking in physical terms, as if the rows
were punch cards.|||On Wed, 20 Jul 2005 17:12:47 -0600, Terri wrote:

>SELECT *
>FROM FUND
>INNER JOIN POSITION ON FUND.ACCT = POSITION.ACCT
>INNER JOIN SECURITY ON POSITION.SEC_ID = SECURITY.SEC_ID
>WHERE (FUND.MANAGER = 'smith')
>I get duplicate SEC_ID's with this query because Smith may be the manager o
f
>multiple funds that hold positions in the same security. For this query I
>only need to return one record per security, doesn't matter which.
>Any help would be appreciated.
>
Hi Terri,
After reading the thread, I'd say that half of your problem is solved if
you replace "SELECT *" with "SELECT column, column, ...", listing only
the columns you need. Probably "SELECT SecID" or something like that.
The other half of your problem is elementary once the irrelevant extra
columns are no longer in the select list: simply inset the keyword
DISTINCT between SELECT and the column list and the duplicates are gone.
SELECT DISTINCT SecID
FROM Funds
INNER JOIN Positions
ON Positions.Acct = Funds.Acct
INNER JOIN Securities
ON Securites.SecID = Positions.SecID
WHERE Funds.Manager = 'Smith'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Sunday, February 19, 2012

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user USER created in the server, if I try to make a sql query (select * from tablename) the sql panel writes "select * from USER.tablename" and this causes a error when I try make that select query from a application.
How can I avoid the part USER. in the query?Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user USER created in the server, if I try to make a sql query (select * from tablename) the sql panel writes "select * from USER.tablename" and this causes a error when I try make that s
elect query from a application.
How can I avoid the part USER. in the query?
Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user US
ER created in the server, if I try to make a sql query (select * from tablen
ame) the sql panel writes "select * from USER.tablename" and this causes a e
rror when I try make that s
elect query from a application.
How can I avoid the part USER. in the query?Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

avg of most current 50 only

I have a query that returns the averages for a selected group of records.
select AVG(h.stkhstClose), h.stkhstcsisym
from stkhst h
JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
and u.unvmemUnvID = 29001
group by h.stkhstcsisym
order by h.stkhstcsisym
this works and returns 99 averages. However, I need it to average only the
last 50 records of each group based on the date. I'm looking for a clean way
to do this without using a temporary table or cursor. and ideas would be
appreciated
thanks
kesJust a guess. See my signature for a more precise answer.
SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
<whatever> ORDER BY datecolumn DESC) x
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> I have a query that returns the averages for a selected group of records.
> select AVG(h.stkhstClose), h.stkhstcsisym
> from stkhst h
> JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
> and u.unvmemUnvID = 29001
> group by h.stkhstcsisym
> order by h.stkhstcsisym
> this works and returns 99 averages. However, I need it to average only the
> last 50 records of each group based on the date. I'm looking for a clean
way
> to do this without using a temporary table or cursor. and ideas would be
> appreciated
> thanks
> kes|||Thank You Aaron (you seem to answer a lot of my postings and your suggestion
s
have always proven helpful)
this will get the average for one group but how about the rest? Would a
where stkhstDate IN (select top 50 stkhstdate from stkhst where stkhstid =
xx order by stkhstdate DESC)
thank you
kes
"Aaron [SQL Server MVP]" wrote:

> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||>> this will get the average for one group but how about the rest?
Did you read Aaron's post? To repeat:
See his signature for a more precise answer.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
Anith|||ok, fair enough.
stkhst:
CREATE TABLE [stkhst] (
[stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
[stkhstCsiSym] [int] NULL ,
[stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstDate] [int] NULL ,
[stkhstOpen] [decimal](9, 4) NULL ,
[stkhstHi] [decimal](9, 4) NULL ,
[stkhstLow] [decimal](9, 4) NULL ,
[stkhstClose] [decimal](9, 4) NULL ,
[stkhstVol] [int] NULL ,
[stkhstDiv] [int] NULL ,
[stkhstX] [int] NULL ,
[stkhstO] [int] NULL ,
[stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
[stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstCPosL] [int] NULL ,
[stkhstCPosH] [int] NULL ,
[stkHstCCol] [int] NULL
) ON [PRIMARY]
GO
unvmem:
CREATE TABLE [unvmem] (
[unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
[unvmemCsiId] [int] NOT NULL ,
[unvmemUnvID] [int] NOT NULL ,
[unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
) ON [PRIMARY]
GO
"Aaron [SQL Server MVP]" wrote:

> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||noted, posted
thanks
kes
"Anith Sen" wrote:

> Did you read Aaron's post? To repeat:
> See his signature for a more precise answer.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> --
> Anith
>
>|||What about sample data and desired results?
The point is that we're not going to drive to Wichita or Kansas or wherever
you are to see what data is in your table and try to figure out what result
you want from that data. And we're certainly not going to spend our
afternoon inventing fictitious but possibly unrealistic data to populate
your empty table, then spend time developing a solution against that, only
to find out all the "buts" that come with the assumptions we made. Please
supply sample data in the form of INSERT statements, and the resultset you
want based on that data.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO|||Kurt,
Your tables seem to have no primary keys which is a critical design flaw.
Generally for such problems, others cannot test the solutions without sample
data. You have not provided that either. Also as a side note, if your scheme
allows, you may want to look closely at your naming convention as well.
Here is another attempt with guesswork:
SELECT AVG( stkhstClose ), stkhstcsisym
FROM ( SELECT TOP 50 h.stkhstClose, h.stkhstcsisym
FROM stkhst h
INNER JOIN unvmem u
ON h.stkhstcsisym = u.unvmemCsiId
WHERE u.unvmemUnvID = 29001
ORDER BY h.stkhstcsisym ) D ( stkhstClose, stkhstcsisym )
GROUP BY stkhstcsisym ;
Anith|||Kurt,
You want to extract and average the last 50 records for each group...
Just add a where clause that restricts the query to operate only on those
records which have 50 or less "partners" (in the same group) after them...
Select AVG(h.stkhstClose), h.stkhstcsisym
From stkhst h
Where (Select Count(*) From stkhst
Where stkhstcsisym = h.stkhstcsisym
And DateColumn >= h.DateColumn) <= 50
Select * From
"Kurt Schroeder" wrote:
> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO
> "Aaron [SQL Server MVP]" wrote:
>|||My apologies, I did not mean to imply that I needed more than advise. Please
understand that I do not feel it appropriate to ask for more than just that.
I feel it would be unfair to you or anyone else to do my work for me.
Aaron, your first posting to my question gave me what I needed to search for
the answer. My real query is much more complex, but this part of it was
simple enough to post for advice.
Again I wish to thank you for your help.
Humbly yours
kes
"Aaron [SQL Server MVP]" wrote:

> What about sample data and desired results?
> The point is that we're not going to drive to Wichita or Kansas or whereve
r
> you are to see what data is in your table and try to figure out what resul
t
> you want from that data. And we're certainly not going to spend our
> afternoon inventing fictitious but possibly unrealistic data to populate
> your empty table, then spend time developing a solution against that, only
> to find out all the "buts" that come with the assumptions we made. Please
> supply sample data in the form of INSERT statements, and the resultset you
> want based on that data.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
>

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 Function Overflow

Convert the column to BIGINT so you have more range: SELECT AVG(CONVERT(BIGINT, YourColumn)) FROM YourTable -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Dennis Jackson@.discussions.microsoft.com> wrote in message news:43ea50a8-ae33-4816-bb70-8935293b923c@.discussions.microsoft.com...encountered on SQL 2005 CTP Sept.When I am using the AVG function of 2 years worth of stock volume data(503 data points), I get a INT buffer overflow. However, the STDEV function continues to work, which I *think* requires you to calculate average first...Any Suggestions on how to work around this?encountered on SQL 2005 CTP Sept.

When I am using the AVG function of 2 years worth of stock volume data(503 data points), I get a INT buffer overflow.

However, the STDEV function continues to work, which I *think* requires you to calculate average first...

Any Suggestions on how to work around this?|||The reason it doesn't work is AVG is computed as SUM/COUNT and not implemented natively. Since SUM returns for any expression of integer category except bigint, it will error out depending on the values. Ideally, we should implement AVG natively to handle the domain of values. For now, workaround is to cast the expression to higher precision data type.|||I already have the variable defined as a 4byte unsigned INT.
I may try to do AVG(column/1000000).. i will see if that will work.|||I assume then that STDEV is implemented as a native function then.
that was the part that wosnt making sense.. you need average to calculate STDEV, and that worked. but the logical intermediate item it couldnt calculate.

Also another wierd thing I ran across. when I tried to create a column that was stdevP(column) when the sql management studio parses it, it keeps converting it to COUNTBig(column) weird.

Do you know if that is a change they are planning to make?

AVG function on an integer column- truncation

When I use the AVG Function on an integer column, the result is truncated

Example:

Select AVG(field1) from table1

Field1 is an int field and has 4 rows with the values 114,115,115 and 115. This will return 114.

I can get the correct result by using the following SELECT:

SELECT CAST(AVG(CAST (field1 as decimal(18,1)))+ .5 as int) from table1

Am I missing something here? Is there an simpler way to do this?

Any help will be appreciated.

Steve D.

You are correct on the problem, but I am certain you can do it a little easier. Try this example:

DECLARE @.v_test TABLE ([num] INT)

INSERT INTO @.v_test VALUES(1)

INSERT INTO @.v_test VALUES(2)

SELECT AVG(CONVERT(DECIMAL,num))

FROM @.v_Test

|||

The problem is that I need the resultant value to be an integer, not a decimal value. The query from above will return 1.75000.

I need the result back as an integer, which was why I had to cast it back to an integer after adding .5. Just looking for a simpler way to do this

|||

I'm not sure I understand. Would your answer be rounded to 2? Converting it to an INT is what it is doing for you.

You could use this to round it. If you want to recast it to INT, that is possible at this point.

SELECT ROUND(AVG(CONVERT(DECIMAL,num)),0)

|||

SELECT CEILING(AVG(CONVERT(DECIMAL,num))) from yourTable

|||

More info.

I want the Average value to round to the nearest integer.

1.8 would round to 2

1.2 would round to 1

Ceiling looks like it would make 1.2 convert to 2.

Edit-

Tested this further- it looks like ROUND is what I need. It returns a decimal number (ex 1.0000), but I can convert that back to int.

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
--
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
ThanksOn Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
>>I need to average the resulting averages from the query below
>>This query gets the averages for each TowerNumber.
>>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
>> AS AvgSupplyCalciumHardness
>>FROM tblTowers
>>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND
>>(SystemID
>>= @.SystemID) AND
>> (LocationID = @.LocationID)
>>GROUP BY LocationID, SystemID, TowerNumber
> WITH ROLLUP
>
> J.
>|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
Thanks
On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.
|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
> WITH ROLLUP
>
> J.
>
|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.
|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, February 16, 2012

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
--
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
ThanksOn Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemI
D
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.
4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
> WITH ROLLUP
>
> J.
>|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work
.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

average question

SELECT AVG(SCORE) AS AVERAGE_SCORE
FROM GRADES
SCORE is an integar column with values from 0 - 5
Now this only returns integer values such as 3, 4...
How can I make AVERAGE_SCORE to be a decimal?
ie 3.452
Howardselect avg(1.0*SCORE) AS AVERAGE_SCORE FROM GRADES
Without the 1.0*, the average will be SUM(SCORE)/COUNT(SCORE),
which will be a quotient of integers, and use integer division which
discards any remainder.
Steve Kass
Drew University
"Howard" <howdy0909@.yahoo.com> wrote in message
news:uxcoNcggGHA.3996@.TK2MSFTNGP03.phx.gbl...
> SELECT AVG(SCORE) AS AVERAGE_SCORE
> FROM GRADES
> SCORE is an integar column with values from 0 - 5
> Now this only returns integer values such as 3, 4...
> How can I make AVERAGE_SCORE to be a decimal?
> ie 3.452
>
> Howard
>|||Thanks Steve
One more question
Is it possible to update the value of the field CLASS_AVG in the same query?
I tried this but it didn't work
UPDATE RESULTS
SET CLASS_AVG = AVERAGE_SCORE IN
(SELECT AVG(SCORE) AS AVERAGE_SCORE
FROM GRADES)
"Steve Kass" <skass@.drew.edu> wrote in message
news:u99HpjggGHA.1264@.TK2MSFTNGP05.phx.gbl...
> select avg(1.0*SCORE) AS AVERAGE_SCORE FROM GRADES
> Without the 1.0*, the average will be SUM(SCORE)/COUNT(SCORE),
> which will be a quotient of integers, and use integer division which
> discards any remainder.
> Steve Kass
> Drew University
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:uxcoNcggGHA.3996@.TK2MSFTNGP03.phx.gbl...
>|||If you are sure of the functioanltiy then may be you should try this.
UPDATE RESULTS
SET CLASS_AVG = (SELECT AVG(SCORE)
FROM GRADES)
But remember this will update the class_avg with the average that you
caclulate for all the rows in the table.|||Howard (howdy0909@.yahoo.com) writes:
> One more question
> Is it possible to update the value of the field CLASS_AVG in the same
> query?
> I tried this but it didn't work
> UPDATE RESULTS
> SET CLASS_AVG = AVERAGE_SCORE IN
> (SELECT AVG(SCORE) AS AVERAGE_SCORE
> FROM GRADES)
You can say simply:
UPDATE RESULTS
SET CLASS_AVG = (SELECT AVG(SCORE) AS AVERAGE_SCORE FROM GRADES)
But this would update every row in RESULTS with the same value,
which may not be what you want.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just put the query in a VIEW and it will be re-calculated each time.
You are still thinking like a COBOL programmer who wants to write all
his data to a file, not a like an SQL programmer who knows that a VIEW
is also a TABLE and does not have to havea physical existence.|||Or use
select cast(score as decimal (3,2)) as average_score
Same effect, but the explicit cast may be clearer to the next person who
review the code. The next programmer may not know that 1.0*score actually
converts it to a decimal from an int. I never understood it until I started
following this newsgroup.
"Steve Kass" <skass@.drew.edu> wrote in message
news:u99HpjggGHA.1264@.TK2MSFTNGP05.phx.gbl...
> select avg(1.0*SCORE) AS AVERAGE_SCORE FROM GRADES
> Without the 1.0*, the average will be SUM(SCORE)/COUNT(SCORE),
> which will be a quotient of integers, and use integer division which
> discards any remainder.
> Steve Kass
> Drew University
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:uxcoNcggGHA.3996@.TK2MSFTNGP03.phx.gbl...
>

average download rates for SQL Server

I have a SQL Server hosted on a Win2003 server. I have a monitor on the server letting me know the average download speeds. When I do a large select from a remote connection, it looks like the download rate maxes out at around 10 kb/sec.

Is this an average download rate for a server hosting sql server?
actually it looks like the 2003 servers download (via FTP at least) is much much faster. Is there a different configuration for SQL Server that could be limiting the download? I'm really just assuming that the download is limited because when I do a very large select from a remote computer, it takes around 30 seconds, but the download rate never goes above 10 kb/sec.

|||My first impression would be to verify that some network segment is not limiting to 10 MB.|||Your transfer rates will be limited to the transfer rate of the network or the speed of the hard drives.

Monday, February 13, 2012

Average

Hello,

I have two tables:
[Posts] > PostId, ...
[Ratings] > RatingId, PostId, Rating

I want to select all posts and add a new column named AverageRating.
This new column is the average of all ratings associated to that Post.
If a post was not rated then its AverageRating would be NULL.

How can I do this?
Thanks,
Miguel

select* , (SelectAvg(Rating)FROM Ratings RWhere R.PostId= P.PostId)as AverageRating

from Posts P

|||

SELECT p.*, t.avgRatingFROM posts pLEFTJOIN(Select postid,CAST(AVG(CAST(RatingasDecimal(5,2)))asDecimal(5,2))AS avgRatingFROM RatingsGroup By postid) tON p.postid=t.postid

available values and free text

Hi,
I have a parameter that get his available values from a query.

I want that user will have the option to select from the values list OR to type in his own value.

Is there a way to do that?

Thanks,YOu will have to use two parameters and compose your query to accept either the one or the other value.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Hello Jens,

I was afraid of that :(
I was sure at first that there is a simple solution other then that.

Now it mean that I need to check both parameters and if the user fill them both I need to decide which one to choose and so on...

Any way, It seems that its the only way.

Thanks for your replay,
Roy.

available values and free text

Hi,
I have a parameter that get his available values from a query.

I want that user will have the option to select from the values list OR to type in his own value.

Is there a way to do that?

Thanks,

Hello my friend,

I am assuming that your rdl report is working and the user can select from a drop down list and you just want extra functionality to allow the user to choose their own, which may not be on the list. A way to do this would be to use 2 other parameters. One will be the user's chosen value and the other will be a flag to say whether to use the selected drop down value or what the user entered

SELECT * FROM MyTable WHERE (MyID = @.DropDownListValue AND @.UsingDropDownList = 1)

OR (MyID = @.UserValue AND @.UsingDropDownList = 0)

Kind regards

Scotty

|||

Hi Scotty,

Thank for your replay!

I also thought about useing two parameters but it looked like a really poor idea.

After seeing your answer I got the feeling That Its the only way :(

The problem is that I already have to much parameters and helf the screen is with parameters textBox (since it organized them in two column and I can't do any thing about that),

Thanks for your help,

Roy.

|||

Hello again my friend,

You do not need to use the default parameter screen that the report generates. When you submit the report after choosing your parameters, observe the url it generates. It concatenates your parameters on to the URL. Instead of using the default report screen, you can make your own user-friendly web page where they choose the parameters and then just generate the URL based on their choices and redirect them.

Kind regards

Scotty

available values and free text

Hi,
I have a parameter that get his available values from a query.
I want that user will have the option to select from the values list
OR to type in his own value.
Is there a way to do that?
Thanks,You can have either type in values or drop down you cant have both.
Amarnath
"nicknack" wrote:
> Hi,
> I have a parameter that get his available values from a query.
> I want that user will have the option to select from the values list
> OR to type in his own value.
> Is there a way to do that?
> Thanks,
>|||Yap, Thats what i found out.
Thanks
Amarnath =D7=9B=D7=AA=D7=91:
> You can have either type in values or drop down you cant have both.
> Amarnath
> "nicknack" wrote:
> > Hi,
> > I have a parameter that get his available values from a query.
> >
> > I want that user will have the option to select from the values list
> > OR to type in his own value.
> >
> > Is there a way to do that?
> >
> > Thanks,
> >
> >

Tuesday, March 27, 2012

Backing Up Muliple Databases in Same Instance

Backing Up Muliple Databases in Same Instance

Backing Up Muliple Databases in Same Instance

Friday, February 24, 2012

Avoid duplicates in SELECT

Sunday, February 19, 2012

Avoid automatically add of user name to the query

Avoid automatically add of user name to the query

Avoid automatically add of user name to the query

avg of most current 50 only

AVG of integer column returns integers result - newbie

AVG Function Overflow

AVG function on an integer column- truncation

Averaging the Averages

Averaging the Averages

Thursday, February 16, 2012

Averaging the Averages

average question

average download rates for SQL Server

Monday, February 13, 2012

Average

available values and free text

available values and free text

available values and free text

Autonumber field

Blog Archive

About Me