Aggregated Query Without Group By

Aggregated query without GROUP BY

A change was made in version 5.7.5 where it will now, by default, reject queries in which you aggregate using a function (sum, avg, max, etc.) in the SELECT clause and fail to put the non-aggregated fields in the GROUP BY clause. This behavior is part and parcel to every other RDBMS and MySQL is finally jumping on board.

You have two options:

1. You can change the MySQL settings to default to the old behavior to allow not-so-great queries like this. Information can be found here
2. You can fix your query

Option 2 would look something like:

SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email GROUP BY id, password LIMIT 1

It's also important to note that excluding a non-aggregated column from the GROUP BY clause is permitted in 5.7.5 and newer version in the event that the unaggregated column has been limited to a single value (such as a filter in the WHERE clause). See the link above for examples of this allowed exception.

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'jquzntys.posts.id'

If you use COUNT() but don't specify a GROUP BY clause, it still qualifies as an aggregated query, but the whole result is treated as one "group." You may think you don't have any grouping because you don't have a GROUP BY clause, but it is implicit in your use of COUNT().

It seems you want to count the number of posts of each status, and also show the total count of posts (of any status), and also show the count of posts by a specific user.

I suggest doing it this way, using two separate queries:

SELECT COUNT(*) as count
FROM posts
WHERE user_id = ?;

SELECT status, COUNT(*) as count
FROM posts
GROUP BY status WITH ROLLUP;

The advantage of the second query is that if any new status values appear in the future, you don't have to rewrite the query. The WITH ROLLUP gives you the total on the last row of the result set (on that row, status will be NULL).

Not every task needs to be implemented in a single SQL query. Sometimes splitting the query into two queries allows both queries to be simpler and more clear.

ERROR 1140 (42000) In aggregated query without GROUP BY, expression #1

Don't use select distinct:

select (e.salary * e.months), count(*)
from employee e
where (e.salary * e.months) = (select max(e2.salary * e2.months)
                               from employee e2
                              )
group by (e.salary * e.months);

The message is pretty clear. You have count(*) so your query is an aggregation query. However, you have an unaggregated column, so MySQL is confused: do you want an aggregation or not? Hence the error.

You can also write this as:

select (e.salary * e.months), count(*)
from employee e
group by (e.salary * e.months)
order by (e.salary * e.months) desc
limit 1;

Is it possible to use Aggregate function in a Select statment without using Group By clause?

All columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY

Good:

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3

Also good:

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3, col5, col6

No other columns = no GROUP BY needed

SELECT MAX(col4)
...

Won't work:

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2

Pointless:

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3, MAX(col4)

Having an aggregate (MAX etc) with other columns without a GROUP BY makes no sense because the query becomes ambiguous.

In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'a.b.id';

$dn1 = mysqli_fetch_array(mysqli_query($con, 
    'select count(id) as recip, id as recipid, 
        (select count(*) from pm) as npm 
    from users 
    where username="'.$recip.'"
    group by recipid, npm'));

anyway you should not use php var in sql command .. this way you are at risk for sqlinjection.. you shoudl chek for your mysql driver for preparated statements and binding param ..

In aggregated query without GROUP BY, expression

go to variable and check for sql_mode there you will find out ONLY_FULL_GROUP_BY, remove it and save it. it will work.

Querying sum of multiple aggregate columns (without groupBy)

If I understand you correctly this might be very easy but again I might have not understood it right.

$result = DB::table('transactions')->selectRaw('type, SUM(credit_movement) as sum')->groupBy('status')->get();

This should return something like this:

---