Getting "ORA-00979: not a GROUP BY expression" in a PHP app

Question

I test with mysql it work, but when I used Oracle, this error appear:

ORA-00979: not a GROUP BY expression

Here is my code:


    <?php
$sqlSelect = "SELECT * FROM policy order by policyprogress_id asc";
if (isset($_GET['batch']) && !empty($_GET['batch'])) {
  $batch = $_GET['batch'];
  $sqlSelect = "SELECT * FROM policy where batch = '".$batch."' group by type order by policyprogress_id asc";
  if (isset($_GET['type']) && !empty($_GET['type'])) {
    $type = $_GET['type'];
    $sqlSelect = "SELECT * FROM policy where batch = '".$batch."' and type = '".$type."' order by policyprogress_id asc";
  }
}

Warning: your code has serious SQL injection vulnerabilities. Please research this and fix it before going live with this code - currently this code is not safe to run, and could get your website hacked. That means people accessing systems they should not, or people stealing data that you have a responsibility to protect, or destroying data that will stop your system working. — halfer
@beginner . . . .Oracle is correct. SELECT * is broken with GROUP BY. If you want an idea of how to fix this problem, then ask a new question. Provide sample data, desired results, and an explanation of the logic you want to implement. I doubt you need three queries for this. One would be more efficient. — Gordon Linoff

Popeye Popeye · Accepted Answer · 2020-03-24T05:08:33

You have a total of 3 queries

-- 1
SELECT * FROM policy order by policyprogress_id asc ;

-- 2
SELECT * FROM policy where batch = '".$batch."' 
group by type order by policyprogress_id asc;

 -- 3
SELECT * FROM policy where batch = '".$batch."' and type = '".$type."' 
order by policyprogress_id asc;

Your second query will throw an error as You are doing SELECT * but also using GROUP BY TYPE.

In GROUP BY queries, SELECT clause can only include the grouped columns and aggregate functions. If you try to use other than grouped columns in the SELECT clause then Oracle will throw ORA-00979: not a GROUP BY expression error.

Getting "ORA-00979: not a GROUP BY expression" in a PHP app

1 Answers