Geek Answers Handbook

Sum of SQL Rows

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

State values Queensland 4554 Queensland 2343 Queensland 9890 NSW 1333

I want to get all records with Queensland status name, and I want to sum all records with field names and the sum, which will be divided by the number of records (account) with state name.

Can someone help me with the syntax to achieve this?

I need something so that the output is 5595.66 (i.e. (4454 + 2343 + 9890) / 3)

+6
source share
2 answers
 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

As a side element, columnName values is the Reserved keyword. Use the separator to remove it, depending on the DBMS you are using. Like (backtick) MySQL, (parentheses) SQL Server, etc.

+12
source

Based on

"I want to get all records with the Queensland status name .."

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
+1
source

More articles:


All Articles