Adding Values Within a Date Range in MySQL

Vote count:

0

I have an online cinema ticketing system that needs to display its monthly total sales from all registered users.

Below are the tables for the movie schedule and transaction of users. A row is inserted to the transaction table every time a user purchases an eTicket.

MySQL:

CREATE TABLE IF NOT EXISTS schedule(
    schedule_id TINYINT(3) UNSIGNED AUTO_INCREMENT,
    movie_id TINYINT(3) UNSIGNED NOT NULL,
    mall_id VARCHAR(255) NOT NULL,
    schedule_cinema TINYINT(2) UNSIGNED NOT NULL,
    schedule_price DECIMAL(5, 2) NOT NULL,
    schedule_date DATE NOT NULL,
    schedule_time TIME NOT NULL,
    schedule_seats TINYINT(2) UNSIGNED NOT NULL,
    PRIMARY KEY(schedule_id),
    FOREIGN KEY(movie_id) REFERENCES movie(movie_id),
    FOREIGN KEY(mall_id) REFERENCES mall(mall_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS transaction(
    transaction_id SMALLINT(5) UNSIGNED AUTO_INCREMENT,
    user_id VARCHAR(255) NOT NULL,
    schedule_id TINYINT(3) UNSIGNED NOT NULL,   
    transaction_date DATE NOT NULL, /* 'YYYY-MM-DD' */
    PRIMARY KEY(transaction_id),
    FOREIGN KEY(user_id) REFERENCES user(user_id),
    FOREIGN KEY(schedule_id) REFERENCES schedule(schedule_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)ENGINE=INNODB;

Q U E S T I O N:

How do I add all schedule_price's from a range of transaction_date's (i.e. all dates that fall in a specific month)?

asked 2 mins ago

ohtph

145

Adding Values Within a Date Range in MySQL