CODEWITHSUNDEEP
phpmysql
cantaffordavan
cantaffordavan

Reputation: 1447

MySQL - count users WHERE (last 30 days, last one year, all time)

I am trying to make a query that checks the clients table and returns a count of users with a specific value for the last 30 days, 365 days and All Time.

The all time count is easy:

$stmt = $conn->prepare("SELECT count(id) AS count FROM clients WHERE referred =  :refid");
$stmt->bindParam(':refid', $refid);
$stmt->execute();
$totalreferrals = $stmt->fetchAll();

Table:

id |   signup   | reffered |
----------------------------
2  | 2012-08-24 |   14     |
----------------------------
3  | 2011-10-13 |   14     |

I am not sure if I can combine the query using a UNION or if I should just make three different queries. Any help?

Upvotes: 0

Views: 1217

Answers (2)

ClearLogic
ClearLogic

Reputation: 3682

i think you want this in columns and not in rows if so here it is 

SELECT  COUNT(CASE WHEN DATEDIFF(CURDATE(),signup) <= 30 THEN id
                   ELSE NULL
              END) AS Last30days ,
        COUNT(CASE WHEN DATEDIFF(CURDATE(), signup) <= 365 THEN id
                   ELSE NULL
              END) AS Last365Days ,
        COUNT(*) AS Alltime
FROM    Table1
WHERE   reffered = 14

SQLFiddle http://sqlfiddle.com/#!2/6e6ce/2

Upvotes: 6

Brunno Benatti
Brunno Benatti

Reputation: 14

Maybe this could solve the problem:

SELECT count(id) AS count FROM clients WHERE referred = :refid AND BETWEEN ADDDATE(NOW(), INTERVAL -1 MONTH) AND NOW();

SELECT count(id) AS count FROM clients WHERE referred = :refid AND BETWEEN ADDDATE(NOW(), INTERVAL -1 YEAR) AND NOW();

Upvotes: 0

Related Questions