0
SELECT count(*) as dias FROM users WHERE DATE(created_at) = CURRENT_DATE()-0
I have this sql line that sums all records made of the current day. But I want of the 7 days in a row would have like to do the 7 in only one sql line.
1 answer
1
If your "created_at" field is a date and time type field and you want to see the number of entries in the last 7 days broken by date, one of the ways to resolve it would be like this:
SELECT DAYOFWEEK(created_at) as dia_semana, YEAR(created_at) AS ano, MONTH(created_at) as mes, DAY(created_at) as dias, count(*) as total
FROM users WHERE created_at > date_sub(NOW(), interval 7 day)
GROUP BY DAYOFWEEK(created_at), YEAR(created_at), MONTH(created_at), DAY(created_at)
ORDER BY YEAR(created_at), MONTH(created_at), DAY(created_at)
The dayofweek function returns a number for the weekday, the year, Month and day functions draw year month and day from the date/time field
https://www.w3resource.com/mysql/date-and-time-functions/mysql-dayofweek-function.php
-
look it was not well so no more help much gave to solve very forced
Browser other questions tagged php sql
You are not signed in. Login or sign up in order to post.
SELECT count(*) as dias FROM users WHERE DATE(created_at) = CURRENT_DATE()-7– Jakson Fischer
okay plus this from the total of every day I want one at a time like if you put 0 comes hj 1 tomorrow I could understand
– f3107
Your question is vague, I don’t understand... Remembering that: Count(*) will count the records returned by the query, if you want to add something, use sum(field).
– Anderson Nuernberg
I particularly see no problem in breaking sql in several lines, even the visualization and understanding of it by other people gets better.
– Anderson Nuernberg
Anderson so you surgere that make 7 sql to generate queries every day ?
– f3107
Here it worked the way I sent you
– Jakson Fischer
Jakson yes it works more it only returns me a precise date of type seg have qua qui sex Sab dom , I tried to put inside a for this query to go consulting the 7 days
– f3107