Summing multiple columns

[_tina_]

Hi,

 

I have a query:

 

SELECT ROUND(SUM(rating)/COUNT(*)*2)/2 as rating FROM table WHERE id = 5

 

I need to do the same query but this time with multiple columns.

I have a table with 9 columns which hold ratings (integers 1 - 10).  I need to get the average and divide by two.  i.e:

Average = (6+8+4+9+3+9+5+4+7)/9 = 6.111  / 2

 

Do you know how I can do this in one query?

 

Thanks in advance.

[_tina_]

Sorry to reply to myself but I think I may have figured it out but am not quite sure if I'm getting the correct answer.

 

SELECT ROUND(SUM( col1 + col 2 .... + col9 )/COUNT(*)*2)/2 as rating FROM table WHERE id = 5

 

Does this look correct?

 

Thank you again

 

Hi,

 

I have a query:

 

SELECT ROUND(SUM(rating)/COUNT(*)*2)/2 as rating FROM table WHERE id = 5

 

I need to do the same query but this time with multiple columns.

I have a table with 9 columns which hold ratings (integers 1 - 10).  I need to get the average and divide by two.  i.e:

Average = (6+8+4+9+3+9+5+4+7)/9 = 6.111  / 2

 

Do you know how I can do this in one query?

 

Thanks in advance.

[_tina_]

Does anyone have any idea on this?

 

I'm trying to get the Average = (6+8+4+9+3+9+5+4+7)/9 = 6.111  / 2 and keep the result a number under 5.  The user chooses 9 values (1-10) and I just want to add them all up, get the average and output the result as a number in the range of 1 to 5.

 

Thank you

[cags]

It doesn't sound like your obvjective should be that difficult, I think nobody has replied because to myself at least I really don't understand what your description. Perhaps it would help if you clue'd us in to what exactly your database table looks like?

[akitchin]

do you have multiple rows per ID, or only one row per ID? and is it a static number of columns?

[_tina_]

My table looks like:

 

id, t_id, row1, row2, row3, row4, row5, row6, row7, row8, row9

 

id is the primary key

t_id is a foreign key

row1-9 ints

 

There are multiple t-id's.  This seems to be where the problem is.

 

Thanks for the reply.

 

It doesn't sound like your obvjective should be that difficult, I think nobody has replied because to myself at least I really don't understand what your description. Perhaps it would help if you clue'd us in to what exactly your database table looks like?

[cags]

SELECT (`col1` + `col2` + `col3` + `col4` + `col5` + `col6` + `col7` + `col8` + `col9`) /18 AS `average`
FROM `table`
WHERE `id`=5

[_tina_]

Thank you for this.  It doesn't seem quite right though.  It just returns all 1's which is incorrect.

Why are you dividing by 18?

 

Thanks

 

 

SELECT (`col1` + `col2` + `col3` + `col4` + `col5` + `col6` + `col7` + `col8` + `col9`) /18 AS `average`
FROM `table`
WHERE `id`=5

[_tina_]

Also, I appear to get the correct results when i:

 

SELECT table1.id, table1.firstname, table1.lastname,

(col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9)/18 as average

FROM table2 LEFT JOIN table1 on table2.broker_id = table1.id GROUP BY id ORDER BY average DESC LIMIT 10

 

But, if there is a column with the same value, in this case a foreign key.  It just returns NULL for those rows.

 

Thank you for this.  It doesn't seem quite right though.  It just returns all 1's which is incorrect.

Why are you dividing by 18?

 

Thanks

 

 

SELECT (`col1` + `col2` + `col3` + `col4` + `col5` + `col6` + `col7` + `col8` + `col9`) /18 AS `average`
FROM `table`
WHERE `id`=5

[cags]

I divide by 18 because thats the same as dividing by 9 then dividing by two. It obviously assumes that you will always have 9 items, but since this is a table which should have a fixed structure this should always be the case.

 

LEFT JOIN will return all rows from the left table, even if there are no matching values in the right table. That's the whole point of a left join, if you only want rows where they match, omit the LEFT keyword.

[_tina_]

Thank you cags for your reply.

 

In the database there is:

 

id user_id value

1 2 10

2 2 20

3 2 10

4 3 15

5 4 30

6 5 45

 

I need to get each value.  For where the user_id is the same, i.e. (user_id = 2 ), these should be summed and the average calculated.  The problem is that these are just being ignored.

 

 

 

 

I divide by 18 because thats the same as dividing by 9 then dividing by two. It obviously assumes that you will always have 9 items, but since this is a table which should have a fixed structure this should always be the case.

 

LEFT JOIN will return all rows from the left table, even if there are no matching values in the right table. That's the whole point of a left join, if you only want rows where they match, omit the LEFT keyword.

[cags]

Your going to have to give more information. The latest table information displayed doesn't seem to have any relevance to your question you originally posed. Is this a seperate table that in some way relates to the orginal?

[_tina_]

I'm sorry, I'll try to explain better.

 

id    user_id  row1  row2  row3  row4 .. row9

1    2            10        5        4        6        5

2    2            20

3    2            10

4    3            15

5    4            30

6    5            45

 

id is the PK, user_id is a FK and value is a float. 

Row1 to row9 are summed and the average is calculated.  There can also be many of the same user_id's.  These need to be grouped together and the average calculated. 

 

The output has to be in the range of 1.0 to 5.0

 

I hope that seems a little clearer.

 

Thanks again.

 

 

Your going to have to give more information. The latest table information displayed doesn't seem to have any relevance to your question you originally posed. Is this a seperate table that in some way relates to the orginal?

[cags]

That makes a lot more sense, I'm no expert at MySQL, but the problem intruiges me, I'll have a look see what I can come up with.

[_tina_]

Thanks very much cags

 

That makes a lot more sense, I'm no expert at MySQL, but the problem intruiges me, I'll have a look see what I can come up with.

[cags]

After a bit (read lot, lol) of messing about and learning some new bits I came up with this...

 

SELECT SUM((col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9) /18) / COUNT(id) AS average
FROM table1
WHERE user_id =$user_id

 

You may need GROUP BY user_id at the end, but I'm not sure, it didn't seem to make any difference when I tried.

[_tina_]

Thank you for this cags.  I had this before but it only returns one result.  Also, I don't know the user_id before the query is run. 

 

After a bit (read lot, lol) of messing about and learning some new bits I came up with this...

 

SELECT SUM((col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9) /18) / COUNT(id) AS average
FROM table1
WHERE user_id =$user_id

 

You may need GROUP BY user_id at the end, but I'm not sure, it didn't seem to make any difference when I tried.

[cags]

If you want the average for everybody then simply remove the WHERE and add in the GROUP BY

 

SELECT SUM((col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9) /18) / COUNT(id) AS average
FROM table1
GROUP BY user_id

[_tina_]

Okay, thanks Cags for all the help 

 

If you want the average for everybody then simply remove the WHERE and add in the GROUP BY

 

SELECT SUM((col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9) /18) / COUNT(id) AS average
FROM table1
GROUP BY user_id