hello.
i need count rows of week and tried this query with correct output:
mysql> SELECT
zn,
COUNT(*) AS q,
CURRENT_DATE () AS today,
DATE_SUB(
CURRENT_DATE (),
INTERVAL 1 WEEK
) AS WEEK
FROM
tbl_cc80
WHERE
1
AND Day_of_the_event_s BETWEEN DATE_SUB(
CURRENT_DATE (),
INTERVAL 1 WEEK
)
AND CURRENT_DATE ()
GROUP BY
Zn,
DATE_SUB(
CURRENT_DATE (),
INTERVAL 1 WEEK
)
ORDER BY
Day_of_the_event_s DESC;
+------------------+----+------------+------------+
| zn | q | today | WEEK |
+------------------+----+------------+------------+
| LIXXX | 31 | 2013-01-10 | 2013-01-03 |
+------------------+----+------------+------------+
1 rows in set
Now i tried this other query version, but the output is wrong: in my table i have 31 rows for Zn equal to LIXXX, why this query extract 51 rows?
Can you help me?
thank you.
mysql> SELECT
zn,
CURRENT_DATE () AS today,
DATE_SUB(
CURRENT_DATE (),
INTERVAL 1 WEEK
) AS WEEK,
COUNT(
IF (
Day_of_the_event_s BETWEEN DATE_SUB(
CURRENT_DATE (),
INTERVAL 1 WEEK
)
AND CURRENT_DATE (),
1,
0
)
) AS `output`
FROM
tbl_cc80
WHERE
1
GROUP BY
Zn,
DATE_SUB(
CURRENT_DATE (),
INTERVAL 1 WEEK
)
ORDER BY
Day_of_the_event_s DESC;
+------------------+------------+------------+--------+
| zn | today | WEEK | output |
+------------------+------------+------------+--------+
| LIXXX | 2013-01-10 | 2013-01-03 | 51 |
+------------------+------------+------------+--------+
1 rows in set