MySQL single query to count rows in multiple date ranges

Question

I have to count the rows in several date ranges (today, week, month) from a mysql table.

Currently I am running through a loop in my PHP script and doing an individual query for each date range, which I know is a bad idea.

<?php
$now = "2016-04-21";
$today = $now;
$week = date( 'Y-m-d', strtotime( '-7 day', strtotime( $now ) ) );
$month = substr( $now, 0, 7 );

$res1 = mysql_query("SELECT SUM(pageview) FROM statistics WHERE user = '$id' AND DATE_FORMAT( date, '%Y-%m-%d' ) = '$today'" );
$res2 = mysql_query("SELECT SUM(pageview) FROM statistics WHERE user = '$id' AND ( t.date BETWEEN ( '$week' ) AND ( '$today' ) )");
$res3 = mysql_query("SELECT SUM(pageview) FROM statistics WHERE user = '$id' AND DATE_FORMAT( t.date, '%Y-%m' ) = '$month'" );

So I would like to use a SINGLE QUERY to do so.

I have found count rows in multiple date ranges and query for grabbing multiple date ranges but I'm not sure which is the best, and how to do in my case

can you explain the structure of the table a bit? what columns you have? etc, so that I can have a better idea of what you are doing. — Webeng
– Webeng, Commented Apr 21, 2016 at 8:12
Table has 4 columns "id int(10) autoincrement primary", "date date", "user varchar(32) index" and "pageview int(10)". In pageview are stored the total number of daily pageview — ipel
– ipel, Commented Apr 21, 2016 at 8:20
alright, so each row is a user with a number of page views, and you'll probably have a lot of those rows, and then you want to count how many total page views you had in the past month, past week, and past day, is that correct? Also, you mentioned you are doing a loop currently. Did you mean you are adding user's page views 1 by 1 together? — Webeng
– Webeng, Commented Apr 21, 2016 at 8:26

Jester · Accepted Answer · 2016-04-21 08:29:30Z

1

If you really want to retrieve the three values using one query:

$res = mysql_query("
SELECT sum1, sum2, sum3 
FROM (SELECT SUM(pageview) AS sum1 FROM statistics WHERE user = '$id' AND DATE_FORMAT( date, '%Y-%m-%d' ) = '$today') AS T1
JOIN (SELECT SUM(pageview) AS sum2 FROM statistics WHERE user = '$id' AND ( t.date BETWEEN ( '$week' ) AND ( '$today' ) )) AS T2
JOIN (SELECT SUM(pageview) AS sum3 FROM statistics WHERE user = '$id' AND DATE_FORMAT( t.date, '%Y-%m' ) = '$month') AS T3");

Although this is not much better performance wise, i don't really see a reason to do this.

P.S. it's a bad habit to insert variables into queries like this. In this case your safe but if you use user inputted variables you're open to SQL injection. Try learning prepared statements

edited Apr 21, 2016 at 8:29

answered Apr 21, 2016 at 8:19

Jester

1,4081 gold badge9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ipel Over a year ago

no because I need three separate results: today, week and month.

Jester Over a year ago

in that case, you can do it in one query but might as well do it in three. that is much cleaner codewise i think and you don't win much efficiency by making it one query

Andrew · Accepted Answer · 2016-04-21 08:57:06Z

You can combine all your conditions by OR to select all data in one query. And this construction can be used in SELECT to sum rows that meet some condition

SUM(IF(condition, sum_column_name, 0))

Your query can look like this if you want to return one row with three sums

SELECT
  SUM(IF(t.date = '$today', pageview, 0)) AS pageview_today,
  SUM(IF(t.date BETWEEN '$week' AND '$today', pageview, 0)) AS pageview_week,
  SUM(IF(DATE_FORMAT(t.date, '%Y-%m') = '$month', pageview, 0)) AS pageview_month
FROM statistics t
WHERE user = '$id' AND (
  t.date = '$today'
  OR (t.date BETWEEN '$week' AND '$today')
  OR DATE_FORMAT(t.date, '%Y-%m') = '$month'
)

Or you can make 3 queries and combine them with UNION as was mentioned by @quazardous. And this method will give you 3 different rows.

quazardous · Accepted Answer · 2016-04-21 08:09:13Z

0

Use Union ?

Select a from B
Union
Select B from C
...

answered Apr 21, 2016 at 8:09

quazardous

88610 silver badges16 bronze badges

Collectives™ on Stack Overflow

MySQL single query to count rows in multiple date ranges

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related