Question: After Updating to MySQL 5.7.11 we are getting a number of errors related to the sql\_mode including only\_full\_group\_by.

Research shows we can turn this off but it would probably be better to know how to achieve the intended goal with properly structured SQL statements.

**Error:**

> 
> #1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tblslideshow.slideImage'; this is incompatible with sql\_mode=only\_full\_group\_by
> 
> 
> 

**Query:**

```
SELECT
   tblslideshow.slideImage,
   COUNT(tblslideshow.slideID) AS countVal
FROM
  tblslideshow
WHERE
  tblslideshow.parentID = 3424

```

The goal is to return a list of values but also a Count for all returned records.
What's the best way to achieve this goal without having to change the MySQL my.ini ? And why is it a Group By error is we're not even grouping? Or is Grouping part of the proper solution?

**Expected Result should be:**

```
slideImage        |      countVal

Image1.jpg        |      3

Image2.jpg        |      3 

Image3.jpg        |      3 

```

Edit: Since we are using PHP and MySQLi it may be wiser to exclude the Count() and just use "mysqli\_num\_rows"

Answer: 
When you use *Aggregate function*, you need to add non-aggregate columns on `Group by` clause.

```
SELECT
   tblslideshow.slideImage,
   COUNT(tblslideshow.slideID) AS countVal
FROM
  tblslideshow
WHERE
  tblslideshow.parentID = 3424
Group by tblslideshow.slideImage

```

**EDIT**

You can try to use subquery on `select`

```
SELECT t.slideImage,
(
    SELECT
       COUNT(tblslideshow.slideID) AS countVal
    FROM
      tblslideshow
    WHERE
      tblslideshow.parentID = 3424
) countVal
FROM tblslideshow t
WHERE t.parentID = 3424

```
What you are looking for is `COUNT OVER`, available as of MySQL 8.0:

```
SELECT
   tblslideshow.slideImage,
   COUNT(*) OVER () AS countVal
FROM
  tblslideshow
WHERE
  tblslideshow.parentID = 3424;

```

This keeps the single rows and adds the aggregate value to them.