MySQL error "Expression #1 of ORDER BY clause is not in GROUP BY clause"

Question

I am trying to fetch total number of records from a table and using the following MySQL query:

SELECT COUNT(*) AS cnt FROM `info` WHERE 1 GROUP BY FROM_UNIXTIME(signup_date, '%Y-%m-%d') ORDER BY signup_date DESC

but it is resulting in the following error:

SQL Error(1055): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'rentown.info.signup_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.[SELECT COUNT() AS cnt FROM info WHERE 1 GROUP BY FROM_UNIXTIME(signup_date, '%Y-%m-%d') ORDER BY signup_date DESC]

and here is my table format:

+ Options
    id  email   signup_date     ip  city_name   firstname   address     state   lastname    city    zipcode     phonenumber     current_url     creditscore
    4   kfct@yahoo.com  1388525440  108.200.78.136  Philadelphia, PA    Kathy   1915 Apex Ave #1/4  California  Yeung   Los Angeles     90039   310 890 3338    NULL    NULL
    10  mlh@gmail.com   1388884727  98.199.141.66   Dickinson, TX   Mackenzie   102 strand  Texas   Helms   Galveston   77550   409 599 8024    NULL    NULL
    11  mjma@yahoo.com  1388889053  99.190.210.155  Grand Prairie, TX   samathiis   1701 towne crossing blvd #731   Texas   ashley  mansfield   76063   817 210     NULL    NULL

@GurwinderSingh http://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause — Tim Biegeleisen, Dec 19 '16 at 12:38
@GurwinderSingh: haven't you tried running a query in sql in phpmyadmin? Check that query. ;) — VishalParkash, Dec 19 '16 at 13:11

score 3 · Accepted Answer · answered Dec 19 '16 at 12:34

The error message is pretty self-explanatory. You can only order by columns appearing in the GROUP BY clause or aggregates. For a quick fix, just order by the same term you used when grouping.

SELECT COUNT(*) AS cnt
FROM `info`
GROUP BY FROM_UNIXTIME(signup_date, '%Y-%m-%d')
ORDER BY FROM_UNIXTIME(signup_date, '%Y-%m-%d') DESC

score 0 · Answer 2 · answered Dec 19 '16 at 12:37

0

For a total number of records, this should be enough:

SELECT COUNT (*) AS cnt
FROM 'info'
ORDER BY signup_date DESC

answered Dec 19 '16 at 12:37

DCZ

1,584
1
12
18

MySQL error "Expression #1 of ORDER BY clause is not in GROUP BY clause"

2 Answers2