--- a/displayConfidentialities.php +++ b/displayConfidentialities.php @@ -10,9 +10,14 @@ */ include_header("Confidentialities"); -$query = "SELECT value, procurementMethod -FROM `contractnotice` -GROUP BY procurementMethod "; +$query = ' +SELECT SUM(value) as value,count(*), reason +FROM +(select value, unnest(string_to_array(replace("confidentialityContractReason",\'Other - \',\'\'),\';\')) reason from contractnotice where "confidentialityContractReason" != \'\' and "confidentialityContractReason" != "confidentialityOutputsReason" and '.$yearQ.' '.$standardQ.' +union select value, unnest(string_to_array(replace("confidentialityOutputsReason",\'Other - \',\'\'),\';\')) reason from contractnotice where "confidentialityOutputsReason" != \'\' and "confidentialityContractReason" != "confidentialityOutputsReason" and '.$yearQ.' '.$standardQ.' +union select value, unnest(string_to_array(replace("confidentialityOutputsReason",\'Other - \',\'\'),\';\')) reason from contractnotice where "confidentialityOutputsReason" != \'\' and "confidentialityContractReason" = "confidentialityOutputsReason" and '.$yearQ.' '.$standardQ.') a +GROUP BY reason ORDER BY value desc +'; $query = $conn->prepare($query); $query->execute(); @@ -22,7 +27,7 @@ foreach ($query->fetchAll() as $row) { setlocale(LC_MONETARY, 'en_US'); $value = number_format(doubleval($row[0]),2); - echo ("