CODEWITHSUNDEEP
phpmysqlcountaverage
user982853
user982853

Reputation: 2488

PHP MYSQL - Count & Average In Same Query

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.

Upvotes: 3

Views: 5066

Answers (4)

LSerni
LSerni

Reputation: 57418

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.

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135838

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

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60518

You can use multiple aggregate functions in a single query:

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

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

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

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

Upvotes: 7

Related Questions