BN83 - 1 year ago 39

MySQL Question

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 Source

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 ;
```