Interval day and week

Databases
1

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:

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 …

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

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

3

Thank you for help.

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

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 ! :slight_smile:
Thanks a lot!