I have two errors in a query PHP MySQL group by

Question

I have this basic query`

SELECT * FROM contratti   GROUP BY mail

In MySQL I have this error:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'databasesample.contratti.idcontr' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

score 0 · Answer 1 · answered Jun 30 '21 at 08:18

0

to use GROUP BY you need to use aggregate functions like COUNT, SUM and so on.

See more examples https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

answered Jun 30 '21 at 08:18

Alex Black

450
2
7

score 0 · Answer 2 · answered Jun 30 '21 at 08:19

group by is for aggregated result ..(sum, avg , min, max) and tipically a select * from .. is not a candidate for this kind of query ..

could be you are looking for an order by

SELECT * FROM contratti  ORDER BY mail

or for a valid group by function

select email, count(*)
FROM contratti  GROUP BY mail

score -2 · Answer 3 · answered Jun 30 '21 at 08:50

I didn't use Mysqli but you can do that just like this via PDO;

$db = new PDO('mysql:host='.$host.';dbname='.$db_name, $username_db, $password_db);
$sql = "SELECT * FROM contratti   GROUP BY mail";
$stmt=$db->prepare($sql);
$stmt->execute();
$result =$stmt->fetch(PDO::FETCH_ASSOC);
print_r($result);

I have two errors in a query PHP MySQL group by

3 Answers3