I have this query:
select count(id) AS num,date_id from table
where FROM_UNIXTIME(date_id,'%d-%m-%Y')='17-08-2009' order by date_id DESC;
This should result in all the id's posted on today's date, but it is giving only one id (the highest one). When I remove count(id) from this query, it runs fine.
Why doesn't this count work with this query?
Count is often paired with GROUP BY. Did you try GROUP BY date_id?
If you want to select total count for the date along with each date_id:
SELECT date_id, num
FROM (
SELECT COUNT(id) AS num
FROM table
WHERE date_id >= UNIX_TIMESTAMP('17-08-2009')
AND date_id < UNIX_TIMESTAMP('18-08-2009')
)
CROSS JOIN
table
WHERE date_id >= UNIX_TIMESTAMP('17-08-2009')
AND date_id < UNIX_TIMESTAMP('18-08-2009')
ORDER BY
date_id DESC
When you have an aggregate function like COUNT in your query any columns that are not aggregated have to be mentioned in a group by clause. The reason for this is that as your query stands it doesn't quite make sense with SQL's execution semantics.
This should work for you:
select count(id) AS num,date_id
from table
where FROM_UNIXTIME(date_id,'%d-%m-%Y')='17-08-2009'
group by date_id
order by date_id DESC;
Also, since you're just getting from one date, you don't need the order by clause (since the query will only return one row anyways.
