Sql query calculating the mean

Question

Trying to write a simple query in access to calculate the mean and sum of items. Raw Table: ID Color Value ---------------------- 1 black 4 2 black 4 3 green 3 4 green 7 5 red 5 6 red 1 OutPut Table: ID Color SUM Mean 1 black 2 4 2 green 2 5 3 red 2 3

Subhendu De · Answer

IDENTITY ( data_type [ , seed , increment ] ) Arguments data_type --> Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type. seed --> Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1. increment --> Is the increment to add to the seed value for successive rows in the table. #Temp_Mean is a temporary table while MeanTable is original table. Since I need to create autogenerated ID column thats why I need to use temporary table to hold the resultset . Thanks.....

David Smith · Answer

Quick question about the syntax. 1.can you explain the Identity function, and each of its parameters. there are three parameters you included (int, 1, 1) 2. you syntax #Temp_Mean. what does the "#" mean? Temp_Mean is a table

Subhendu De · Answer

I consider it for SQL Server select ident = IDENTITY(int, 1, 1),Color, COUNT(*) as count, SUM(Value)/COUNT(*) as mean into #Temp_Mean from MeanTable group by Color select * from #Temp_Mean drop table #Temp_Mean Thanks.....

Sql query calculating the mean

Answers (3)