Create mySQL query in PHP to generate JSON array of column averages

Question

I'm trying to create a PHP script that will echo the average value of each of my columns and array them all in a single JSON array.

I know that this is how I can get the average for a column.

select avg(`sales`) as sales  from `mytable`

But I'm not sure how to string this together

select avg(`sales`) as sales  from `mytable`
select avg(`profit`) as profit  from `mytable`
select avg(`costs`) as costs  from `mytable`

To get something like this echo'd from PHP:

[
{
    "sales": 56812
},
{
    "profit": 2312
},
{
    "costs": 324
}
]

json_encode() - never form the JSON by hand. You could use a UNION query or JOIN to get all of the data in one shot. — Jay Blanchard
– Jay Blanchard, Commented Feb 4, 2015 at 17:12

Andy Lester · Accepted Answer · 2015-02-04 17:14:44Z

0

You're close.

select
    avg(sales) as sales,
    avg(profit) as profit,
    avg(costs) as costs
from mytable;

Note that I removed the backticks around your column and table names because they are not necessary and are just visual noise and are one other thing to make mistakes about.

answered Feb 4, 2015 at 17:14

Andy Lester

94.2k16 gold badges106 silver badges162 bronze badges

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

5 Comments

Devon Bessemer Over a year ago

Then fetch it into an associative array and use json_encode. I think is what the OP was getting at.

jonmrich Over a year ago

I tried this, but getting 500 error:

$dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);  $sql= ("select     avg(sales) as sales,     avg(profit) as profit,     avg(costs) as costs from mytable;");  $result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC); header('Content-type: application/json'); echo json_encode($result);

Andy Lester Over a year ago

"500 error" isn't enough to know what the problem is. There's an actual useful error message in your web server's error log.

jonmrich Over a year ago

Sorry...thought I put that in there. The error message: Call to a member function fetchAll() on a non-object in /averages.php

Andy Lester Over a year ago

This is more debugging than can be done in comments. The SQL I gave you should work, but there is apparently something else wrong with your code.

Collectives™ on Stack Overflow

Create mySQL query in PHP to generate JSON array of column averages

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related