CODEWITHSUNDEEP
mysql
developer
developer

Reputation: 2050

Why doesn't a query with COUNT execute?

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?

Upvotes: 0

Views: 146

Answers (3)

arnsholt
arnsholt

Reputation: 871

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425371

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

Upvotes: 1

S.Lott
S.Lott

Reputation: 391854

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

Upvotes: 5

Related Questions