How do I combine 3 SQL queries into 1?

Question

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;

Answer 1

The SQL UNION Operator http://www.w3schools.com/sql/sql_union.asp

Answer 2

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

Answer 3

SELECT COUNT(updated_at) AS 'TODAY'
FROM parts_development.page_views day
    INNER JOIN (SELECT COUNT(updated_at) AS 'WEEK', parts_user_id as userid 
                FROM parts_development.page_views p
                WHERE updated_at >= DATE_SUB(NOW(),INTERVAL 1 week) 
                GROUP BY parts_user_id) week
        ON day.parts_user_id = week.userid
     INNER JOIN (SELECT COUNT(updated_at) AS 'YEAR', parts_user_id as userweek 
                FROM parts_development.page_views p
                WHERE updated_at >= DATE_SUB(NOW(),INTERVAL 1 year) 
                GROUP BY parts_user_id) year
        ON day.parts_user_id = year.userid
WHERE day.updated_at >= DATE_SUB(NOW(),INTERVAL 1 day)
GROUP BY day.parts_user_id

Don't quote my on the "INTERVAL" syntax, I didn't look it up, I'm a TSQL guy myself. This could also be accomplished with unions. You could also replace the where clauses with predicates in the joins.

Answer 4

If you want two more rows, then use UNION ALL. You still kind of have 3 queries but executed as one.

If you want two more columns, then use SUM(CASE(...)). Basically you more your WHERE clause to the CASE clause 3 times each with own condition.

Answer 5

how about

SELECT count(*), IsToday(), IsThisWeek() 
   FROM whatever 
   WHERE IsThisYear() 
   GROUP BY IsToday(), IsThisWeek()

where the Is*() functions are boolean functions (or expressions)

Answer 6

No need to join or subselect from the table more than once.

    SELECT parts_user_id,
           SUM( IF( updated_at >= DATE_SUB( NOW(), INTERVAL 1 DAY ), 1, 0 ) )
            as day_visits,
           SUM( IF( updated_at >= DATE_SUB( NOW(), INTERVAL 7 DAY ), 1, 0 ) )
            as week_visits,
           count(*) as year_visits
      FROM parts_development.page_views
     WHERE updated_at >= DATE_SUB( NOW(),INTERVAL 1 year )
  GROUP BY parts_user_id