ExpressionEngine query: count entries

count_entries.ee_snip.sql

SELECT
    SUM(entries) AS alltime,
    SUM(IF((YEAR(entrydate) = YEAR(CURDATE())),entries,0)) AS thisyear, 
    SUM(IF((MONTH(entrydate) = MONTH(CURDATE()) && YEAR(entrydate) = YEAR(CURDATE())),entries,0)) AS thismonth, 
    SUM(IF((WEEKOFYEAR(entrydate) = WEEKOFYEAR(CURDATE()) && YEAR(entrydate) = YEAR(CURDATE())),entries,0)) AS thisweek,
    SUM(IF((DAYOFYEAR(entrydate) = DAYOFYEAR(CURDATE()) && YEAR(entrydate) = YEAR(CURDATE())),entries,0)) AS thisday
FROM
    (SELECT 
            DATE(FROM_UNIXTIME(entry_date)) AS entrydate, 
            COUNT(entry_id) AS entries 
        FROM exp_channel_titles
            # You can change the following line if you need to;
            # you can also add extra conditions in the WHERE clause if you only want to count 
            # entries from certain channels, etc.
        # SafeToChange
        WHERE 
            `status` != 'closed' # backticks because "status" is actually a reserved word in MySQL, so it's best to be safe!
        # End SafeToChange!
        GROUP BY 
            entrydate
        ) mydatetable
;


/* -- Example Output: --------------------------------------------------------------------------------- **

| alltime | thisyear | thismonth | thisweek | thisday |
|   50000 |     8890 |       350 |       25 |       2 |

** ---------------------------------------------------------------------------------------------------- */