Limit count in sql

Question

I have a query that looks like the below

SELECT
venueid as VENUES, venue2.venue AS LOCATION,
(SELECT COUNT(*) FROM events WHERE (VENUES = venueid) AND eventdate < CURDATE()) AS number
FROM events
    INNER JOIN venues as venue2 ON events.venueid=venue2.id
GROUP BY VENUES
ORDER BY number DESC

I want to limit the count to count the last 5 rows in the table (sorting by id) however when I add a limt 0,5 the results don't seem to change. When counting where do you add in the limit to limit the amount of rows that are being counted?

    CREATE TABLE venues (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        venue VARCHAR(255)
    ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

    CREATE TABLE categories (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        category VARCHAR(255)
    ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

    CREATE TABLE events (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        eventdate DATE NOT NULL,
        title VARCHAR(255),
        venueid INT,
        categoryid INT
    ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

    INSERT INTO venues (id, venue) VALUES
    (1, 'USA'),
    (2, 'UK'),
    (3, 'Japan');

    INSERT INTO categories (id, category) VALUES
    (1, 'Jazz'),
    (2, 'Rock'),
    (3, 'Pop');

    INSERT INTO events (id, eventdate, title, venueid, categoryid) VALUES
    (1,20121003,'Title number 1',1,3),
    (2,20121010,'Title number 2',2,1),
    (3,20121015,'Title number 3',3,2),
    (4,20121020,'Title number 4',1,3),
    (5,20121022,'Title number 5',2,1),
    (6,20121025,'Title number 6',3,2),
    (7,20121030,'Title number 7',1,3),
    (8,20121130,'Title number 8',1,1),
    (9,20121230,'Title number 9',1,2),
    (10,20130130,'Title number 10',1,3);

The expected result should look like the below

    |VENUES |LOCATION   |NUMBER |
    |1      |   USA     |   3   |
    |2      |   UK      |   1   |
    |3      |   Japan   |   1   |

As of the time of posting id 9,8,7,6,5 are the last 5 events before the current date.

See SQL Fiddle link below for full table details.

http://sqlfiddle.com/#!2/21ad85/32

Answer 1

This query gives you the five rows that you are trying to group and count:

SELECT *
FROM events
WHERE eventdate < CURDATE()
ORDER BY eventdate DESC
LIMIT 5

Now you can use this query as a subquery. You can join with the result of a subquery just as if it were an ordinary table:

SELECT
    venueid as VENUES,
    venue2.venue AS LOCATION,
    COUNT(*) AS number
FROM
(
    SELECT *
    FROM events
    WHERE eventdate < CURDATE()
    ORDER BY eventdate DESC
    LIMIT 5
) AS events
INNER JOIN venues as venue2 ON events.venueid=venue2.id
GROUP BY VENUES
ORDER BY number DESC

http://sqlfiddle.com/#!2/21ad85/37