CREATE VIEW IF NOT EXISTS aggregated_stats AS SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id; CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color FROM users LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now') LEFT JOIN preferences ON users.id = preferences.user_id GROUP BY users.name; CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS WITH DateSequence(Dates) AS ( SELECT Date(CURRENT_DATE, '-6 day') UNION ALL SELECT Date(Dates, '+1 day') FROM DateSequence WHERE Date(Dates, '+1 day') <= Date(CURRENT_DATE) ) SELECT DateSequence.Dates as 'date', IFNULL(SUM(statistics.quantity), 0) AS 'total' FROM DateSequence LEFT JOIN statistics ON Date(statistics.date) = DateSequence.Dates GROUP BY DateSequence.Dates ORDER BY DateSequence.Dates;