SQL sum of rows

Question

I have 300 records in my database and my structure is something like this

State                      values
Queensland                 4554
Queensland                 2343
Queensland                 9890
NSW                        1333

I want to retrieve all the records with state name Queensland and I want to sum all the records with field name values and the sum to be divided with the number of records (count) with state name.

Can anybody help me out with the syntax to achieve this?

I need something the output to be 5595.66 (i.e. (4454+2343+9890)/3)

Answer 1

SELECT State, SUM(values) / (COUNT(*) * 1.0)
FROM   tableName
WHERE  State = 'Queensland'
GROUP  BY State

OR

SELECT State, AVG(values * 1.0)
FROM   tableName
WHERE  State = 'Queensland'
GROUP  BY State

• SQLFiddle Demo

As a sidenote, the columnName values is a Reserved keyword. Escape it by a delimiter depending on the RDBMS you are using. Like (backtick) MySQL, (brackets) SQL Server, etc....

Answer 2

Based on

"I want to retrieve all the records with state name "Queensland".."

Are you looking for this (for SQL-Server) ? Fiddle-Demo

select [state], round(avg(value *1.0) over() ,2) avgValue
from T
where [state] ='Queensland'

--Results
state       avgValue
Queensland  5595.67
Queensland  5595.67
Queensland  5595.67