CODEWITHSUNDEEP
mysqlcountsumdatediff
Wilf
Wilf

Reputation: 2315

MySQL : How to count multiple row where datediff() is less than 30

According to this topic (MySQL : Count row where sum of left day is less than 30) that I created before. Now I have another problem. I need those rows that going to be expired within 30 days ahead being sum. So I can tell how many product is going to be expired in a month. Here is my code:

user_db

id         name        exp_product1         exp_product2          exp_product1
1          John        2013-03-01           2013-02-28            2013-03-27
2          Alice       2013-02-25           2013-04-25            2013-10-10
3          Ken         2013-01-10           2013-01-20            2013-02-11
4          Elise       2013-04-11           2013-05-23            2013-11-11
5          Bruce       2013-03-14           2013-06-06            2013-09-10

From the table above. There're 4 products from several users are going to be expired withing the next 30 days.

  1. John = exp_product1, exp_product2
  2. Alice = exp_product1 and
  3. Bruce = exp_product1

So I wrote:

    SELECT count(*) AS exp_pax1 
FROM   user_db 
WHERE exp_product1<=timestampadd(day, 30, now())
       AND exp_product1 >= now();

SELECT count(*) AS exp_pax2 
FROM   user_db 
WHERE exp_product2<=timestampadd(day, 30, now())
       AND exp_product2 >= now();

SELECT count(*) AS exp_pax3 
FROM   user_db 
WHERE exp_product3<=timestampadd(day, 30, now())
       AND exp_product3 >= now();

Then I sum those results with PHP: (assuming the values were fetched)

$exp_pax1=exp_pax1
$exp_pax2=exp_pax2
$exp_pax3=exp_pax3

$total_exp=$exp_pax1+$exp_pax2+$exp_pax3;//4

Well, the very question is: How could I shorten these lines into a single mySQL command? Please suggest.

Regards,

Upvotes: 2

Views: 709

Answers (1)

Andrei Filonov
Andrei Filonov

Reputation: 834

You can replace count(*) with sum(YOUR CONDITION) to get count of matching rows... So, your query may be changed to 

SELECT sum(exp_product1<=timestampadd(day, 30, now()) AND exp_product1 >= now()) AS exp_pax1 
 FROM   user_db

Now you can add product 2 and 3 and get total

SELECT sum(exp_product1<=timestampadd(day, 30, now()) AND exp_product1 >= now()) + 
    sum(exp_product2<=timestampadd(day, 30, now()) AND exp_product2 >= now()) +
    sum(exp_product3<=timestampadd(day, 30, now()) AND exp_product3 >= now())
    AS exp 
FROM   user_db

Upvotes: 1

Related Questions