CODEWITHSUNDEEP
mysqlcountweekdayweekend
dialogik
dialogik

Reputation: 9552

Trying to fetch COUNT() on weekday and weekend

I have a table that contains a column (created) that stores a unix timestamp when that item has been created.

Now I want to COUNT() all items that have been created on a weekday (Monday to Friday), compared to all items that have been created on the weekened (Saturday and Sunday).

My query is:

SELECT
  IF (WEEKDAY(FROM_UNIXTIME(`created`)) >= 0 AND WEEKDAY(FROM_UNIXTIME(`created`)) >= 4) THEN COUNT(*) AS `weekday`,
  IF (WEEKDAY(FROM_UNIXTIME(`created`)) <= 5) THEN COUNT(*) AS `weekend`
FROM `mytable`

But the error I get is

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') THEN COUNT(*), IF (WEEKDAY(FROM_UNIXTIME(created)) <= 5) THEN COUNT(*)' at line 3

Any help is highly appreciated.

Upvotes: 0

Views: 173

Answers (1)

conkman
conkman

Reputation: 130

You do not appear to be using the correct syntax. http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_if

Quoted from the above url,

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.".

I believe this would be the correct form for your query,

SELECT
DATE(FROM_UNIXTIME(`created`)) AS `date`,
IF (WEEKDAY(FROM_UNIXTIME(`created`)) >= 0 AND WEEKDAY(FROM_UNIXTIME(`created`)) <= 4, COUNT(*), 0), --WeekDay Count
IF (WEEKDAY(FROM_UNIXTIME(`created`)) >= 5 AND WEEKDAY(FROM_UNIXTIME(`created`)) <= 6, COUNT(*), 0) --Weekend Count

FROM mytable

I have found this link as well to the weekday function. Which leads me to believe your ranges were not correct to begin with.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_weekday

Upvotes: 0

Related Questions