CODEWITHSUNDEEP
mysqlsqlcount
Ronald Dregan
Ronald Dregan

Reputation: 183

Count Events/year in SQL

I have a list of events that have a date. I'm trying to count how many events take place in the current year, and 5 years on either side (regardless of whether any events took place) in mySQL using simple joins, selects, etc (no subqueries) in a single statement.

I have a table that produces the years and the number of events in that year, but am having problems when the year has no events taking place

Upvotes: 0

Views: 1559

Answers (3)

Rich O'Kelly
Rich O'Kelly

Reputation: 41757

To select a count of events that happened between two years, grouped by years, the following sql should suffice:

select year(event.date), count(*) from event where event.date >= '2006' and event.date <= '2016' group by year(event.date);

However, if no events occurred in a year, no result will be returned for it. Databases are not really designed for such dynamic things and I'd suggest such logic should be put in a business (or possibly data-access) layer.

Upvotes: 0

Alexey Gerasimov
Alexey Gerasimov

Reputation: 2141

Look into date functions on mysql http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff

You can use datediff which will give you difference in days. Ex;

WHERE abs(datediff(now(), event_date)) < 365*5

or dateadd(), if your event dates are timestamps, use timestampdiff()

Sample query

SELECT count(*) FROM mytable
WHERE abs(datediff(now(), event_date)) < 365*5

UPDATE

based on some of the comments I've read here, here's a query for you

SELECT year(event_date) as event_year, count(event_date)
FROM mytable
WHERE
abs(datediff(now(), event_date)) < 365*5
GROUP by year(event_date)

Feel free to adjust 5 in (365 * 5) for different range

UPDATE 2

This is NOT very pretty but you can try this with pure mysql. You can also modify this to be a stored proc if necessary:

SET @y6 = year(now());
SET @y5 = @y6-1;
SET @y4 = @y5-1;
SET @y3 = @y4-1;
SET @y2 = @y3-1;
SET @y1 = @y2-1;

SET @y7 = @y6+1;
SET @y8 = @y7+1;
SET @y9 = @y8+1;
SET @y10 = @y9+1;
SET @y11 = @y10+1;

CREATE TEMPORARY TABLE event_years (event_year int not null);
INSERT INTO event_years SELECT @y1;
INSERT INTO event_years SELECT @y2;
INSERT INTO event_years SELECT @y3;
INSERT INTO event_years SELECT @y4;
INSERT INTO event_years SELECT @y5;
INSERT INTO event_years SELECT @y6;
INSERT INTO event_years SELECT @y7;
INSERT INTO event_years SELECT @y8;
INSERT INTO event_years SELECT @y9;
INSERT INTO event_years SELECT @y10;
INSERT INTO event_years SELECT @y11;

SELECT ey.event_year , (SELECT count(event_date) from mytable where year(event_date) = ey.event_year)
from event_years ey;

temporary table will get dropped by itself after your connection is closed. If you add DROP TABLE after SELECT, you might not get your results back.

Upvotes: 2

user491135
user491135

Reputation:

did you try to use join left?

MODIFIED:

SELECT tleft.YEARS, COUNT(tright.EVENTS) 
FROM ONLY_YEARS tleft LEFT JOIN TABLE1 tright 
ON (tleft.YEARS = tright.YEARS)
GROUP BY tleft.YEARS;

With that modification, you need to point to a table that holds all the years (ONLY_YEARS), maybe a dummy table with one column that goes from 1990 to 2020...

Left join optimization for MySQL link

Upvotes: 0

Related Questions