Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Timo Bontenbal
Timo Bontenbal
9,582 Points
Posted by Timo Bontenbal
Timo Bontenbal
9,582 Points

Change SQL statement

Hi,

I need to modify a SQL statement that it would work so as the client wants. But i don't know how to change it.

My SQL statement:

$query = "SELECT opisto As O, AVG(pisteet_1) As P, ka.kaupunki_id As K FROM ". "oppilaitokset op ". "LEFT JOIN pisteet pi ". "ON op.opisto_id = pi.opisto_id ". "LEFT JOIN kaupungit ka ". "ON op.kaupunki_id = ka.kaupunki_id ". "GROUP BY opisto, ka.kaupunki_id ". "ORDER BY K, P, O ASC";

At the moment it calculates all the points(see picture). But the clients wants that it only should calculate the latest two. and then give the average.

Each Team gets assigned to a school and the teams compete for points. The team points are calculated and shown in School average points.

SQL

Image link: https://www.dropbox.com/s/kbpcxdhzm35z5ya/SQL-problem.jpg?dl=0

3 Answers

thomascawthorn
thomascawthorn
22,986 Points
thomascawthorn
thomascawthorn
22,986 Points

To solve this, you will need to make sure there's a timestamp field for "time/date added" in your table.

You can then add:

  • ORDER BY time added (Add this to your current ORDER BY so it is the primary order)
  • LIMIT 2 (Limit commands normally go at the end of the statement)

However I'm unsure if the average will be worked out before or after limiting the results. Some trial and error will tell you :-) If it fails, could you pull out the latest two results and then use code to find the average?

Timo Bontenbal
Timo Bontenbal
9,582 Points
Timo Bontenbal
Timo Bontenbal
9,582 Points

Tom Cawthorn I tried to edit the query to this: 

$query = $db->prepare("SELECT (SELECT AVG(pisteet_1) AS PI FROM pisteet E RIGHT OUTER JOIN oppilaitokset O ON E.opisto_id = O.opisto_id LIMIT 2) kaupunki_id, opisto AS I FROM oppilaitokset G GROUP BY opisto, G.kaupunki_id");

But now it gives all the arrays the same AVERAGE Example:

array(35) {
  [0]=>
  array(2) {
    ["kaupunki_id"]=>
    string(17) "4.931137724550898"
    ["I"]=>
    string(17) "Espoolahden lukio"
  }
  [1]=>
  array(2) {
    ["kaupunki_id"]=>
    string(17) "4.931137724550898"
    ["I"]=>
    string(21) "Etelä-Tapiolan lukio"
  }
  [2]=>
  array(2) {
    ["kaupunki_id"]=>
    string(17) "4.931137724550898"
    ["I"]=>
    string(19) "Etu-Töölön lukio"
  }
  [3]=>
  array(2) {
    ["kaupunki_id"]=>
    string(17) "4.931137724550898"
    ["I"]=>
    string(13) "Halikon lukio"
  }

And if i write the Query like this: 

$query = $db->prepare("SELECT AVG(pisteet_1) AS PI, opisto AS I, O.kaupunki_id AS KAD FROM pisteet E RIGHT OUTER JOIN oppilaitokset O ON E.opisto_id = O.opisto_id GROUP BY opisto LIMIT 2");

It only returns two records:

array(2) {
  [0]=>
  array(3) {
    ["PI"]=>
    NULL
    ["I"]=>
    string(17) "Espoolahden lukio"
    ["KAD"]=>
    string(1) "4"
  }
  [1]=>
  array(3) {
    ["PI"]=>
    string(17) "4.666666666666667"
    ["I"]=>
    string(21) "Etelä-Tapiolan lukio"
    ["KAD"]=>
    string(1) "4"
  }
}
thomascawthorn
thomascawthorn
22,986 Points

Ahhh, yes it will do that. With one SQL statement, you're looking to return the last two results for each school and average them, and it's currently limiting to two results overall. You're looking to limit by group - you might fine this article helpful.

Timo Bontenbal
Timo Bontenbal
9,582 Points
Timo Bontenbal
Timo Bontenbal
9,582 Points

Tom Cawthorn Now i am getting the right rows. But now i need to calculate the Average.

My code:

$query = $db->prepare("SELECT pisteet_1 AS pisteet, opisto AS opisto, nimi AS nimi, t.kaupunki_id AS kaupunki
                            FROM pisteet t
                            INNER JOIN oppilaitokset op ON op.opisto_id = t.opisto_id
                            INNER JOIN joukkueet jo ON jo.id = t.team_id
                            WHERE (select count(*)
                                   from pisteet t2
                                   where t2.team_id = t.team_id and
                                         t2.pisteet_1 >= t.pisteet_1
                                  ) <= 2");

$query->execute();
$testquery = $query->fetchAll(PDO::FETCH_NAMED);

If i change "SELECT pisteet_1 AS pisteet, To "SELECT IFNULL(AVG(pisteet_1), 0) AS pisteet It does not calculate the average correct.