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
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