How to combine 3 SQL queries into 1?

Question

This is written to calculate how many people visited on the last day. I also want to indicate how many of them visited in the last week and year, and display it at all, without making 3 separate requests.

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

 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 

It will visit throughout the year, grouping them by day, week and total.

If you just want to select visits for the day, week and 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 2

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

Answer 3

If you want two more lines, use UNION ALL. You still have 3 queries, but it runs as one.

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

Answer 4

No need to join or select 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 

Answer 5

 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 

Do not quote my INTERVAL syntax, I did not search for it, I am TSQL myself. This could also be done with unions. You can also replace where clauses with predicates in joins.

Answer 6

What about

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

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