CODEWITHSUNDEEP
mysqlsql
AlwaysStudent
AlwaysStudent

Reputation: 1374

Sql query mistake for the weekly statistics

I am trying to make a weekly statistics for user registration. It should work for weekly. There are total of seven days in a week. I want to print the number of users who are registered every day in the week on the screen.

For example:

Number of users who registered on Monday = 38

Number of users who registered on Tuesday = 11

.... .... and this list will only list the week we were

I created a sql query like the following:

SELECT WEEKDAY(registerTime)+1, count(*) 
FROM users group by WEEKDAY(registerTime) 
ORDER BY WEEKDAY(registerTime)

But the outcome of this question is very different. This total shows the number of users who registered on Monday for every year .

Upvotes: 3

Views: 103

Answers (3)

Zain Farooq
Zain Farooq

Reputation: 2964

You can also use Php code for this purpose. First make array of days and then you can get registered users for each day through that array 

function today()
{
    return date("Y-m-d");

}
function last_seventh_day()
{
    return date("Y-m-d",strtotime("-7 days"));

}

$days = ['Saturday','Sunday','Monday','Tuesday','Wednesday','Thursday','Friday'];//Array that stores all days



foreach($days as $day)
    {
        $sql = "select * FROM users where day = '$day'  and date_column between " .today() . "and" .last_seventh_day() ;
    }

I have updated my answer. I hope this will work for you!

Upvotes: 1

apokryfos
apokryfos

Reputation: 40730

You want to limit users to only this week so you don't get everything:

SELECT WEEKDAY(registerTime)+1, count(*) 
FROM users
WHERE WEEKOFYEAR(registerTime) = WEEKOFYEAR(CURDATE()) 
     AND YEAR(registerTime) = YEAR(CURDATE())
group by WEEKDAY(registerTime)
ORDER BY WEEKDAY(registerTime);

Basically you match the year and week of year with what you need. That should be enough.

Upvotes: 3

KMG
KMG

Reputation: 89

Try like this :

SELECT datepart(week, registerTime), count(*) FROM Users GROUP BY datepart(week, registerTime)

Upvotes: 1

Related Questions