PHP MYSQL - Count & Average In Same Query

Question

This may be very simple but I am trying to do a query that will return the average of the results but I also want it to count the number of rows it used to get the average. For example:

Row 1 = 5
Row 2 = 2
Row 3 = 9
Row 4 = 1

Average = 4.25
Count = 4

Is there a way to do this with one query apposed to two. When i used the avg function is always just returns one row so my count is 1 instead of 4. I need a query that will average but also tell me how many records it went through.

I am trying to avoid using two queries. Thank you for your help.

Mike Brant · Accepted Answer · 2012-09-05 20:25:13Z

7

This is pretty basic and should have been discoverable via search.

SELECT COUNT(field) as `count`, AVG(field) as `average` FROM table_name

answered Sep 5, 2012 at 20:25

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LSerni · Accepted Answer · 2012-09-05 20:27:35Z

1

In the terms you have stated - if you aren't GROUPing or things like that - you'd just write

SELECT COUNT(col) AS cnt, AVG(col) AS avg FROM tbl;

and you ought to have no problems. You get one row, with the fields cnt and col containing what you need.

The problem you're having is probably due to the use of mysql_num_rows to get the count, which is not correct. forgot to add: or to the fact that you did not supplied your whole problem.

answered Sep 5, 2012 at 20:27

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

Comments

Eric Petroelje · Accepted Answer · 2012-09-05 20:25:21Z

0

You can use multiple aggregate functions in a single query:

SELECT COUNT(*), AVG(field) FROM mytable

answered Sep 5, 2012 at 20:25

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Comments

Joe Stefanelli · Accepted Answer · 2012-09-05 20:25:43Z

0

SELECT COUNT(*), AVG(YourColumn)
    FROM YourTable

answered Sep 5, 2012 at 20:25

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL - Count & Average In Same Query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related