Total records per day in the last 30 days

Asked

Viewed 144 times

0

Galley,

I just made this consultation, works very well until today(day 20) minus 19, only I need to show the last 30 days.

That’s how it works

SELECT ID, DAY( created_at ) AS DIA, SUM( 1 ) AS HORAS
FROM registration
WHERE created_at
BETWEEN CURRENT_DATE( ) -19
AND CURRENT_DATE( ) 
GROUP BY DAY( created_at ) 
ORDER BY DAY( created_at )

So returns the amount all wrong

SELECT ID, DAY( created_at ) AS DIA, SUM( 1 ) AS HORAS
FROM registration
WHERE created_at
BETWEEN CURRENT_DATE( ) -30
AND CURRENT_DATE( ) 
GROUP BY DAY( created_at ) 
ORDER BY DAY( created_at )
php mysql

1 answer

2


Opa, check if this SQL works:

SELECT ID, DAY( created_at ) AS DIA, SUM( 1 ) AS HORAS
FROM registration
WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY DAY( created_at ) 
ORDER BY DAY( created_at )

  • Rhadamez, perfect! Thanks! To put the day and month concatenated could help me? I did so but it sums -> DAY( created_at ) + '/' + MONTH( created_at ) AS DIA

  • I got so DATE_FORMAT( created_at, '%m-%d' ) AS DATA

  • Ball show, look if this formatting works: SELECT ID, DAY( date_format(created_at, '%Y/%m') ) AS DIA, SUM( 1 ) AS HORAS
FROM registration
WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY DAY( created_at ) 
ORDER BY DAY( created_at )

  • Okay, solved! Thank you

  • Glad you made it, hug.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.