Geek asks and answers

MySQL is summed by months with a total

Possible duplicate:
Calculate the current amount in MySQL

I track the number of users created since 2011 in the application by month for a chart using MySQL and PHP. As part of the request, I would also like to include the current final version.

SELECT
  DATE_FORMAT(created,'%Y%m%d') as 'Date',
  COUNT(item_id) as 'NewUsers'
FROM AP_user
WHERE YEAR(created) > 2011
  AND user_groups = '63655'
  AND user_active = 1
  AND userID NOT IN $excludedUsers
GROUP BY MONTH(created) ASC

I can return the "users by month", but how to include the current summary part as part of this request?

+5
source share
1 answer

Unfortunately, MySQL does not provide analytic functions such as Oracle and SQL Server.

One way to get the “total” is to use a custom variable, something like this:

  SELECT t.Date
       , t.NewUsers
       , @rt := @rt + t.NewUsers AS `Running Total`
    FROM (SELECT @rt := 0) i
    JOIN (
           SELECT DATE_FORMAT(created,'%Y%m%d') AS `Date`
                , COUNT(item_id) as `NewUsers`
             FROM AP_user
            WHERE YEAR(created) > 2011
              AND user_groups = '63655'
              AND user_active = 1
              AND userID NOT IN $excludedUsers
            GROUP BY DATE_FORMAT(created,'%Y-%m')
            ORDER BY DATE_FORMAT(created,'%Y-%m') ASC
         ) t

. , , . , , SELECT. , .

. ( MySQL " " ). , GROUP BY 2012 2013 , . ORDER BY.

+6

More articles:


All Articles