--- a/displayConsultancies.php +++ b/displayConsultancies.php @@ -10,10 +10,11 @@ */ include_header("Consultancies"); -$query = 'SELECT SUM(value) as value, "procurementMethod" -FROM contractnotice -GROUP BY "procurementMethod" '; - +$query = ' +SELECT SUM(value) as value, count(*), min("supplierName"), unnest(string_to_array("consultancyReason",\';\')) reason +FROM contractnotice where "consultancy" = \'Yes\' +GROUP BY "supplierABN",reason order by value desc + '; $query = $conn->prepare($query); $query->execute(); @@ -23,7 +24,7 @@ foreach ($query->fetchAll() as $row) { setlocale(LC_MONETARY, 'en_US'); $value = number_format(doubleval($row[0]),2); - echo ("<tr><td><b>{$row[1]}</b></td><td>\$$value</td></tr>"); + echo ("<tr><td><b>{$row[2]}</b></td><td>{$row[3]}</td><td>{$row[1]} contracts</td><td>\$$value</td></tr>"); } echo "</table>"; include_footer();