CODEWITHSUNDEEP
phpmysqlsqlcount
Canser Yanbakan
Canser Yanbakan

Reputation: 3870

Mysql Count and Split to DAY, WEEK, YEAR for Statistics in same query

I want to get statistics for something.

I'm trying to get count for today, this week, this month.

My query:

"SELECT COUNT(id) FROM images i WHERE i.user_id = 3 GROUP BY DAY(i.created_at), WEEK(i.created_at), MONTH(i.created_at)"

Output:

array(7) {
  [0]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "1"
  }
  [1]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "1"
  }
  [2]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "5"
  }
  [3]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "5"
  }
  [4]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "7"
  }
  [5]=>
  array(1) {
    ["COUNT(id)"]=>
    string(2) "17"
  }
  [6]=>
  array(1) {
    ["COUNT(id)"]=>
    string(1) "1"
  }
}

But i want to split like:

day = 5,
week = 15,
month = 67

But how?

Complete Working Query:

SELECT
                COUNT(i.id) AS `all`,
                (
                    SELECT
                        COUNT(id)
                    FROM
                        images
                    WHERE
                        user_id = i.user_id AND WEEK(created_at) = WEEK(NOW())
                )
                AS `week`,
                (
                    SELECT
                        COUNT(id)
                    FROM
                        images
                    WHERE
                        user_id = i.user_id AND MONTH(created_at) = MONTH(NOW())
                )
                AS `month`,
                (
                    SELECT
                        COUNT(id)
                    FROM
                        images
                    WHERE
                        user_id = i.user_id  AND DAY(created_at) = DAY(NOW())
                )
                AS `day`
            FROM
                images i
            WHERE
                i.user_id = " . $user->getId()

Upvotes: 0

Views: 991

Answers (1)

D_R
D_R

Reputation: 4962

If I understood you right this should be what you're looking for

SELECT Count(*)                         AS day, 
       (SELECT Count(*) 
        FROM   images 
        WHERE  user_id = i.user_id 
               AND Week(i.created_at) = Week(Now()) 
        GROUP  BY Week(i.created_at))  AS week, 
       (SELECT Count(*) 
        FROM   images 
        WHERE  user_id = i.user_id 
               AND Month(i.created_at) = Month(Now()) 
        GROUP  BY Month(i.created_at)) AS month 
FROM   images i 
WHERE  i.user_id = 3 
       AND Day(i.created_at) = Day(Now()) 
GROUP  BY Day(i.created_at)

Upvotes: 1

Related Questions