Interval day and week

Hello there, hope in your help.

I need to extract from my mysql table:

1. All events with date between -1 DAY and today until six;
2. Count the same name event -1 WEEK and -180 DAY, than today.

I tried this select query and I've in output only row. :confused:

Code:

---

SELECT
        Name_of_event,
        CONCAT(
                DATE_FORMAT(Day_of_event, '%d/%m/%Y'),
                ' ',
                Hour_of_event
        ) AS Day_of_event,
        COUNT(
                CASE
                WHEN Day_of_event BETWEEN DATE_SUB(
                        CURRENT_DATE (),
                        INTERVAL 1 WEEK
                )
                AND CURRENT_DATE () THEN
                        1
                ELSE
                        NULL
                END
        ) AS Last_week,
        COUNT(
                CASE
                WHEN Day_of_event BETWEEN DATE_SUB(
                        CURRENT_DATE (),
                        INTERVAL 180 DAY
                )
                AND CURRENT_DATE () THEN
                        1
                ELSE
                        NULL
                END
        ) AS Last_180_days
FROM
        tbl_20
WHERE 1
AND Name_of_event = 'M2N'
AND Day_of_event = DATE_SUB(
                CURRENT_DATE (),
                INTERVAL 1 DAY
        )
OR (
        Day_of_event = CURRENT_DATE ()
        AND Hour_of_event < '06.00'
);

---

If instead tried this simple select query, I've 63 rows and the output is correct... Please tell me how to resolve this problem ...

Code:

---

SELECT
        *
FROM
        `tbl_20`
WHERE
        Day_of_event = '2013-02-06'
AND Name_of_event = 'M2N';

---

in your query which you say returns only one row, you're using the COUNT() aggregate function, but you seem to have forgotten the GROUP BY clause

Thank you for help.

I've added in my first query the clause GROUP BY:

Code:

---

SELECT
        Name_of_event,
        CONCAT(
                DATE_FORMAT(Day_of_event, '%d/%m/%Y'),
                ' ',
                Hour_of_event
        ) AS Day_of_event,
        COUNT(
                CASE
                WHEN Day_of_event BETWEEN DATE_SUB(
                        CURRENT_DATE (),
                        INTERVAL 1 WEEK
                )
                AND CURRENT_DATE () THEN
                        1
                ELSE
                        NULL
                END
        ) AS Last_week,
        COUNT(
                CASE
                WHEN Day_of_event BETWEEN DATE_SUB(
                        CURRENT_DATE (),
                        INTERVAL 180 DAY
                )
                AND CURRENT_DATE () THEN
                        1
                ELSE
                        NULL
                END
        ) AS Last_180_days
FROM
        tbl_20
WHERE 1
AND Name_of_event = 'M2N'
AND Day_of_event = DATE_SUB(
                CURRENT_DATE (),
                INTERVAL 1 DAY
        )
OR (
        Day_of_event = CURRENT_DATE ()
        AND Hour_of_event < '06.00'
)
GROUP BY Day_of_event, Name_of_event;

---

Now I've the 63 rows and the output correct ! :)
Thanks a lot!