CODEWITHSUNDEEP
phpmysqldate
ipel
ipel

Reputation: 1348

MySQL single query to count rows in multiple date ranges

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

Upvotes: 0

Views: 1376

Answers (3)

Andrew
Andrew

Reputation: 1866

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.

Upvotes: 1

Jester
Jester

Reputation: 1408

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

Upvotes: 1

quazardous
quazardous

Reputation: 876

Use Union ?

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

Upvotes: 0

Related Questions