MYSQL: QUERY TOTAL NUMBERS OF SUBSCRIBERS DAILY, WITH COUNT() AND AT THE SAME QUERY SUM() THE RESULT OF COUNT(), DATE TYPE VARCHAR.

“mem_date” IS THE DATE OF REGISTRATION in VARCHAR TYPE. ex. 2014-06-01

SELECT COUNT(mem_date) AS ENTRIES, DATE(mem_date) AS REGISTERED
FROM users WHERE mem_date LIKE '%2014-06%'
GROUP BY DATE(mem_date)
UNION ALL
SELECT 'SUM' mem_date, COUNT(mem_date) FROM users WHERE mem_date LIKE '%2014-06%'

QUERY RESULTS: MEMBERSHIP FOR THE MONTH OF JUNE.

6 2014-06-01
5 2014-06-02
2 2014-06-03
2 2014-06-05
1 2014-06-06
3 2014-06-07
1 2014-06-08
4 2014-06-09
1 2014-06-11
3 2014-06-12
1 2014-06-14
1 2014-06-15
1 2014-06-16
3 2014-06-17
1 2014-06-18
9 2014-06-20
2 2014-06-22
5 2014-06-23
3 2014-06-24
1 2014-06-26
5 2014-06-27
5 2014-06-28
3 2014-06-29
2 2014-06-30
SUM 70

SELECTING THE PREVIOUS MOUNT TOTAL AND THE NEXT MONTH TOTAL. QUERY FOR JUNE THEN JULY.

SELECT ‘SUM_PREVIOUS’ mem_date, COUNT(mem_date) FROM users WHERE mem_date LIKE ‘%2014-06%’
UNION ALL
SELECT COUNT(mem_date) AS ENTRIES, DATE(mem_date) AS REGISTERED
FROM users WHERE mem_date LIKE ‘%2014-07%’
GROUP BY DATE(mem_date)
UNION ALL
SELECT ‘SUM_CURRENT’ mem_date, COUNT(mem_date) FROM users WHERE mem_date LIKE ‘%2014-07%’

Advertisements
This entry was posted in MYSQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s