SQL help computing averages for creating table?

Question

Say I have the following script and want to create the table Noob.

CREATE TABLE Noob (ProductName text null, Rating1 int null, Rating2 int null, AvgRating int null);

INSERT INTO Noob (Hi, 9.3, 8.0, ?)
INSERT INTO Noob (Ho, 8.4, 7.0, ?)
INSERT INTO Noob (Ha, 9.0, 6.0, ?)

Is it possible to put something in place of the "?" so that that last column is the average of the two columns before it? Or is this not possible since the data does not yet exist or something?

What would be the best possible way to do this so that I could just use "select * from Noob" to show the table I want(averages and all)?

Answer 1

You can use the compute column if SQL Server:



CREATE TABLE Noob (ProductName text null, Rating1 int null, Rating2 int null, AvgRating as (Rating1+Rating2)/2. );

Answer 2

You can't have it both ways. If you want to see AvgRating when you do a Select * from... then it is a field in the table that anyone with the correct security/permissions will be able to see and query against.



And you should use a computed field for the AvgRating. That way you never have to calculate the Average and if you change 1 of the 2 ratings, the computed field automatically have the new average.



And another thing, you are trying to insert decimals into an integer datatype and that won't work either

This is what your table should look like:



CREATE TABLE Noob (

ProductName NVARCHAR(100) not null,

Rating1 decimal(2,1) not null default(0.0),

Rating2 decimal(2,1) not null default(0.0),

AvgRating as (Rating1 + Rating2/2) Persisted);

Answer 3

You are directly putting values in table so you cant predict the average before but Its Easy if you add some programming.



int a=20;

int b=30;

INSERT INTO Noob (Column names here) Values (&a, &b, (a+b)/2);



Declaration of variables slightly changes in other languages just provide variables in insert statement. Hope i helped.

Answer 4

In MS-sq. Server Integer's don't have sizes as such. this is labored out from the information form. in case you want to alter the size use Bigint, smallint or tinyint. you will want the no longer NULL in case you haven't any longer have been given a default value...

Answer 5

I believe you should take a look at this post here.