m.aibin m.aibin - 6 months ago 11
SQL Question

Select count multiple fields within time range - MYSQL

I have problem with count multiple fields within time range.
I have following table:

# date, count, fb_user_count, email_user_count, reg_dt
'2015-10-27', '11', '6', '5', '2015-11-02 13:59:14'
'2015-10-26', '3', '1', '2', '2015-11-02 13:59:10'


I want to get weekly number of registration with types, like this:

# date, count, fb_user_count, email_user_count, reg_dt
'2015-11-02', '31', '16', '15', '2015-11-02 13:59:14'
'2015-11-09', '12', '6', '6', '2015-11-09 13:59:14'


And monthly:

# date, count, fb_user_count, email_user_count, reg_dt
'2015-11', '131', 'x', 'y', '2015-11-02 13:59:14' (the last value is not so important)
'2015-12', '112', 'x', 'y', '2015-12-09 13:59:14'


I tried different approaches, but I struggle to finish this task. Any help will be great. Thanks!

Answer

You can use MySQL Date and Time functions WEEK(), MONTH() and YEAR() to build your queries :

SELECT
    MIN(date),
    SUM(count),
    SUM(fb_user_count),
    SUM(email_user_count)
FROM visit
GROUP BY WEEK(date, 1); -- First day is Monday

SELECT
    CONCAT(YEAR(date), '-', MONTH(date)),
    SUM(count),
    SUM(fb_user_count),
    SUM(email_user_count)
FROM visit
GROUP BY YEAR(date), MONTH(date);