How do I combine 3 SQL queries into 1?

date, mysql, sql

Solution

SELECT  DAY(updated_at), WEEK(updated_at), COUNT(*) AS visits
FROM    parts_development.page_views p
WHERE   updated_at >= DATE_SUB(NOW(),INTERVAL 1 year)
GROUP BY
        DAY(updated_at), WEEK(updated_at) WITH ROLLUP

This will count visits within a year, grouping them by day, week, and total.

If you just want to select visits for a day, week and a year in three columns, use this:

SELECT  (
        SELECT  COUNT(*)
        FROM    parts_development.page_views p
        WHERE   updated_at >= DATE_SUB(NOW(),INTERVAL 1 DAY)
        ) AS last_day,
        (
        SELECT  COUNT(*)
        FROM    parts_development.page_views p
        WHERE   updated_at >= DATE_SUB(NOW(),INTERVAL 7 DAY)
        ) AS last_week,
        (
        SELECT  COUNT(*)
        FROM    parts_development.page_views p
        WHERE   updated_at >= DATE_SUB(NOW(),INTERVAL 1 YEAR)
        ) AS last_year

Problem

This is written to count how many people have visited within the last day. I want to also include how many have visited in the last week and year and have it output altogether without doing 3 separate queries. ``` SELECT COUNT(updated_at) AS 'TODAY' FROM parts_development.page_views p WHERE updated_at >= DATE_SUB(NOW(),INTERVAL 1 day) GROUP BY parts_user_id; ```

Original source