Why doesn't this query work?

Question

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?

Answer 1

Count is often paired with GROUP BY. Did you try GROUP BY date_id?

Answer 2

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

Answer 3

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.