SQL stament groups rows and calculate average

Question

I am stuck with the following issue. I have 1 table that looks like this:

field_number.. Value  
````````````````````````````````  
1 ......................... 1  
2 ..........................1  
3 ......................... 2  
4 ..........................2

etc.

I want to group different fieldnumbers and have an average for the value column. So the output should be:

field_number................Value

name(1,2)......................   1..............       ((1+1)/2)

name(3,4)......................   2..............       ((2+2)/2)

I have checked previous questions but cannot find any question that covers this issue (I might search on the wrong keywords though). So if this has already been covered my appologies, but any help or a point to a previous answer would be appreciated.

** =============UPDATE============= **

I went through your suggestions but did not get it right. So I am trying to be more specific. I almost have the result I want apart from the fact I want to have a fixed value in one of my columns. I have the following query:

Select 
Avg(wp_rg_lead_detail.value),
wp_rg_lead_detail.field_number,
From
wp_rg_lead_detail
Where
wp_rg_lead_detail.field_number In (15, 17, 24) A
UNION
Select
Avg(wp_rg_lead_detail.value),
wp_rg_lead_detail.field_number,
From
wp_rg_lead_detail
Where
wp_rg_lead_detail.field_number In (16, 108, 18)

etc.

This gives me a table with two columns

wp_rg_lead_detail.value................field_number
4.3 (average)..............................15 (first value of av calculation)

What I want is to change the field number (15 in this case) in a fixed value (text). What and how should I add this to the query?

please mark an answer.. – Subin Chalil Nov 13 '15 at 18:23 — Subin Chalil, Nov 13 '15 at 18:23

score 1 · Answer 1 · answered Nov 12 '15 at 10:19

1

SELECT avg(value) FROM table WHERE field_number in (1,2)
SELECT avg(value) FROM table WHERE field_number in (3,4)

answered Nov 12 '15 at 10:19

vacsora

222
1
7

Tnx for your quick reply. When I enter the first line it works. Adding the second line gives me an error: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT avg(value) FROM wp_rg_lead_detail WHERE field_number in (3,4)' at line 2. Additionally this query gives me 1 olumn. I need a second column with the name. e.g name(1,2). – Michel Nov 12 '15 at 10:52

Subin Chalil · Answer 2 · 2015-11-12T12:23:50.767

1

For a generalized answer.

SELECT CONCAT('name','(',GROUP_CONCAT(field_number),')') AS field_number,
AVG(Value) as Value
FROM table_name
group by table_name.`Value`

Hope this helps.

edited Nov 12 '15 at 12:23

answered Nov 12 '15 at 10:41

Subin Chalil

3,531
2
24
38

Tnx, I managed to get the result I want with the following query: Select Avg(wp_rg_lead_detail.value), wp_rg_lead_detail.field_number From wp_rg_lead_detail Where wp_rg_lead_detail.field_number In (15, 17, 24) UNION Select Avg(wp_rg_lead_detail.value), wp_rg_lead_detail.field_number From wp_rg_lead_detail Where wp_rg_lead_detail.field_number In (16, 108, 18) etc. . Now I get two columns, one with 1 column with the averages and one column with one of the values. What I want is instead of one of the value in the second column just a fixed name.. is this possible? – Michel Nov 12 '15 at 12:15
of course it is possible.. What is your `fixed value` – Subin Chalil Nov 12 '15 at 12:21
I have edited the query, i misunderstood the question.. pls check – Subin Chalil Nov 12 '15 at 12:27

score 1 · Answer 3 · edited May 23 '17 at 12:23

If your table is really this simple, you can also get away with:

select distinct
   Value,
   count(Value) as '#'
from table_name
group by Value

If you acctually want to group by a range, than you can put the logic of the range in your grouping clause (see this fiddle)

select distinct
    avg(Value) as average,
   floor(Value),
   count(Value) as '#'
from table_name
group by floor(Value)

In the fiddle I used grouping on whole integers, but you can make that as complex as you like (see, for instance, this example)

If you are actually also interested in your corresponding fields, use group_concat() like so

select 
    Value, 
    group_concat(
        distinct field_number 
        order by Value
    ) as fields
from table_name tn1
group by Value
order by Value

output:

Value            | fields
---------------------------------
1                | 1,2      
2                | 3,4

See this fiddle implemented from this blog post

SQL stament groups rows and calculate average

3 Answers3