BN83 BN83 - 2 months ago 7
MySQL Question

List results by "x Days Old"

I have a table with applications, when they've been contacted the "contacted" column is marked with a "1". What I want to do if possible is count how many of the results are up to 3 days old, how many of them are 4-6 days old and how many of them are 7 or more days old. Is this possible in a single query?

__________________________________
| Name | Contacted | Date |
----------------------------------
| Bob | 1 | 2016-09-16 |
| Ben | 1 | 2016-10-03 |
| Sam | 1 | 2016-10-03 |


EDIT:

Using the following:

SELECT
case
when datediff( CURDATE(), `submission_date`) = 3 then '3 Days'
when datediff( CURDATE(), `submission_date`) between 4 and 6 then '4-6 Days'
when datediff( CURDATE(), `submission_date`) > 6 then '7 or more days'
end as `days`,
sum( case
when datediff( CURDATE(), `submission_date`) = 3 then 1
when datediff( CURDATE(), `submission_date`) between 4 and 6 then 1
when datediff( CURDATE(), `submission_date`) > 6 then 1
else 0
end ) as tot
FROM my_table
GROUP BY
case
when datediff( CURDATE(), `submission_date`) = 3 then '3 Days'
when datediff( CURDATE(), `submission_date`) between 4 and 6 then '4-6 Days'
when datediff( CURDATE(),`submission_date`) > 6 then '7 or more days'
end ;


Then

echo '<p>'.$row['tot'].'</p>';


I'm getting:

0
1

Answer

You could use a select case and group by

 select 
     case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(), `date`)  > 6 then  '7 or more days'
      end  as `days`,
    sum( case  
        when datediff( CURDATE(), `date`)  <= 3 then 1 
        when datediff( CURDATE(), `date`)  between 4 and 6 then 1
        when datediff( CURDATE(), `date`)  > 6 then 1
        else 0 
    end  ) as tot 
 from my_table 
 where contacted = 1
 group by 
      case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(),`date`) > 6 then  '7 or more days'
      end ;

In the column my_result you should find the string you need

   select  concat( 'You have *** ',  sum( case  
        when datediff( CURDATE(), `date`)  <= 3 then 1 
        when datediff( CURDATE(), `date`)  between 4 and 6 then 1
        when datediff( CURDATE(), `date`)  > 6 then 1
        else 0 
    end  ), ' *** New Items more than ' , 
     case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days old'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(), `date`)  > 6 then  '7 or more days'
      end ) as my_result 
 from my_table 
 where contacted = 1
 group by 
      case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(),`date`) > 6 then  '7 or more days'
      end ;
Comments