Axis Labeling Issues

I'm trying to plot some data with integer x-axis values ranging from 1 to 1000. And I can't get the first x-axis label to be 0 rather than 1. Since I'm incrementing by 100, the rest of the labels are now 101, 201, etc. How do I get these to start at 0? (or even 100...as long as all data shows up)

Did you try to set the 'Min' property to 0 on the x-axis tab of the chart properties dialog?

Btw, more information on axis labels and the category vs. scalar x-axis mode are available here: http://msdn2.microsoft.com/en-us/library/aa964128.aspx#moressrscharts_topic3

-- Robert

Axis Labeling Issues

I'm trying to plot some data with integer x-axis values ranging from 1 to 1000. And I can't get the first x-axis label to be 0 rather than 1. Since I'm incrementing by 100, the rest of the labels are now 101, 201, etc. How do I get these to start at 0? (or even 100...as long as all data shows up)

Did you try to set the 'Min' property to 0 on the x-axis tab of the chart properties dialog?

Btw, more information on axis labels and the category vs. scalar x-axis mode are available here: http://msdn2.microsoft.com/en-us/library/aa964128.aspx#moressrscharts_topic3

-- Robert

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

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)

INSERT INTO @.v_test VALUES(2)

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)

|||

OR

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.

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 and Total per Hour

I have a table with a field date_hour - (it dates) and a field nivel_tq -
(integer)
Example:
data_hora nivel_tq
10/10/2003 08:00:00 50
10/10/2003 08:15:00 75
10/10/2003 08:25:00 65
10/10/2003 08:30:00 70
10/10/2003 09:00:00 70
10/10/2003 09:20:00 60
10/10/2003 09:30:00 50
10/10/2003 10:00:00 50
11/10/2003 12:00:00 50
11/10/2003 12:20:00 60
Doubt: I need a select that comes back me the average of the nível_tq
per hour and it dates
the expected result would be:
data_hora nivel_tq
10/10/2003 08:00:00 65
10/10/2003 09:00:00 60
10/10/2003 10:00:00 50
11/10/2003 12:00:00 55select DATEPART(hh,data_hora),sum(nivel_tq)/count(*) as Average from
tablename
group by DATEPART(hh,data_hora)
--
HTH
Ryan Waight, MCDBA, MCSE
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:ONYk8cSkDHA.3316@.TK2MSFTNGP11.phx.gbl...
>
>
> I have a table with a field date_hour - (it dates) and a field nivel_tq -
> (integer)
> Example:
> data_hora nivel_tq
> 10/10/2003 08:00:00 50
> 10/10/2003 08:15:00 75
> 10/10/2003 08:25:00 65
> 10/10/2003 08:30:00 70
> 10/10/2003 09:00:00 70
> 10/10/2003 09:20:00 60
> 10/10/2003 09:30:00 50
> 10/10/2003 10:00:00 50
> 11/10/2003 12:00:00 50
> 11/10/2003 12:20:00 60
> Doubt: I need a select that comes back me the average of the nível_tq
> per hour and it dates
> the expected result would be:
> data_hora nivel_tq
> 10/10/2003 08:00:00 65
> 10/10/2003 09:00:00 60
> 10/10/2003 10:00:00 50
> 11/10/2003 12:00:00 55
>
>

Autonumber an INT column

Hello,
I have a simple question. How do I autonumber an integer column called
agentID. I need to insert any rang of numbers in this column.Make it an Identity Column.. Just add the word Identity to the column
definition...
Create Table MyTable
(CustomerID Integer Identity Primary Key Not Null,
Name Varchar(35), etc...)
"Lontae Jones" wrote:

> Hello,
> I have a simple question. How do I autonumber an integer column called
> agentID. I need to insert any rang of numbers in this column.