CODEWITHSUNDEEP
sqlselectaverage
Mischa Milivoje Bakic
Mischa Milivoje Bakic

Reputation: 25

Combine SQL Select and AVG in one query

I try to combine a SELECT and an AVG query into one. My query is working but the AVG result is not shown as expected. Error is:

Undefined index

I thought I can combine AVG and SELECT like this?

the result will be a list and should also show an average progress value. If I put this AVG query separately and inside the "fetch" it works but shows only the first result. So it is not a solution for me because I will have much more rows.

I hope someone can help me to rebuild this miracle :)

<?php

  $statement = $pdo->prepare("        
    SELECT
      audit.id as audit_id, 
      audit.uid, 
      audit.assigned_auditor, 
      audit.audit_req_comment, 
      audit.audit_req_date, 
      audit.audit_date_start, 
      audit.general_audit_status, 
      audit.audit_request_date,
      audit.audit_type, 
      audit.audit_date_start,
      questionaire.quest_name, 
      users.nachname,    
      suppliers.supplier_name, 
      suppliers.supplier_city,
      suppliers.supplier_country,
      (SELECT AVG(progress) AS progress FROM answers WHERE relevant = '1' AND audit_id = :audit_id AND rating != required_answer)
    FROM audit 
    JOIN users ON audit.uid = users.id 
    JOIN suppliers ON audit.supplier_id = suppliers.id 
    JOIN questionaire ON audit.questionaire_id = questionaire.id 
    WHERE 
      audit.cid = :cid 
      AND audit.general_audit_status = 'Maßnahmenplan'
      AND audit.assigned_auditor = :assigned_auditor"
  );

  $result = $statement->execute(array(':cid' => $cid, ':assigned_auditor' => $user['id'], ':audit_id' => 4));
  $count = 1;
  while ($row = $statement->fetch()) {

    // Datum umwandeln
    $original_date = $row['audit_date_start'];
    // Creating timestamp from given date
    $timestamp = strtotime($original_date);
    // Creating new date format from that timestamp
    $new_date = date("d.m.Y", $timestamp);

    // Audit Typ Namensgebung
    if ($row['audit_type'] == "AR") {
      $audit_type = "Externes Audit";
    }
    if ($row['audit_type'] == "RA") {
      $audit_type = "Remote Audit";
    }
    if ($row['audit_type'] == "IA") {
      $audit_type = "Internes Audit";
    }
    if ($row['audit_type'] == "SAA") {
      $audit_type = "Self Assessment Audit";
    }

    //Berechne die durchschnittliche Abarbeitung aller Maßnahmen dieses Audits
    //$statement = $pdo->prepare("SELECT AVG(progress) AS progress FROM answers WHERE relevant = '1' AND    audit_id = :audit_id AND rating != required_answer");
    //$statement->execute(array(':audit_id' => $row['audit_id']));
    //$total_progress = $statement->fetch();

    echo "<tr>";

    echo "<td>" . $row['audit_id'] . "</td>";
    echo "<td>" . $new_date . "</td>";
    echo "<td>" . $row['supplier_name'] . "</td>";
    echo "<td>" . $row['supplier_city'] . " (" . $row['supplier_country'] . ")</td>";
    echo "<td>" . $row['quest_name'] . "</td>";
    echo "<td>" . $audit_type . "</td>";
    echo "<td>" . round($row['progress'], 0) . " %</td>";
    echo '<td align="center"><a href="audit_edit.php?id=' . $row['audit_id'] . '"><i class="fas fa-edit"></i></a></td>';

    echo "</tr>";
  }
?>

Upvotes: 1

Views: 88

Answers (1)

GMB
GMB

Reputation: 222432

As I understand your question, you just need to alias the results of the subquery in the outer query (rather than in the inner query itself):

SELECT
    audit.id as audit_id, 
    audit.uid, 
    audit.assigned_auditor, 
    audit.audit_req_comment, 
    audit.audit_req_date, 
    audit.audit_date_start, 
    audit.general_audit_status, 
    audit.audit_request_date,
    audit.audit_type, 
    audit.audit_date_start,
    questionaire.quest_name, 
    users.nachname,    
    suppliers.supplier_name, 
    suppliers.supplier_city,
    suppliers.supplier_country,
    (
        SELECT AVG(progress) 
        FROM answers 
        WHERE relevant = 1 AND audit_id = :audit_id AND rating != required_answer
    ) AS progress   --> here
FROM audit 
JOIN users ON audit.uid = users.id 
JOIN suppliers ON audit.supplier_id = suppliers.id 
JOIN questionaire ON audit.questionaire_id = questionaire.id 
WHERE 
    audit.cid = :cid 
    AND audit.general_audit_status = 'Maßnahmenplan'
    AND audit.assigned_auditor = :assigned_auditor"

Upvotes: 1

Related Questions