0

I am trying to modify a view that has been created for my mysql database. I would like to count the total of subscribers and subscribers per month per country. currently the view that I have (see below) only shows the total subscribers and unsubscribers per month. The MySQL version is 5.7.24

Table Name: Subscriber

+----+----------------+-------------+-------------------------+
| ID | Subscriber Name| Country ID  | Start                   |
+----+----------------+-------------+-------------------------+
|  1 |              X |          11 | 1/1/2019                |
|  2 |              Y |          22 | 1/2/2017                |
|  3 |              Z |          33 | 1/3/2019                |
+----+----------------+-------------+-------------------------+

Table Name: Subscriber_removed

+----+-----------------+-------------+-------------------------+
| ID | subscriber ID   | Country ID  | End                     |
+----+-----------------+-------------+-------------------------+
|  1 | 1               |          11 | 1/1/2011                |
|  2 | 2               |          22 | 1/2/2012                |
|  3 | 3               |          33 | 1/3/2013                |
+----+-----------------+-------------+-------------------------+

Table Name: Country

+----+---------------+
| ID | Country Name  |
+----+---------------+
|  11 | U.S          |
|  22 | U.K          |
|  33 | UAE          |
+----+---------------+

Current View:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `db`@`%` 
    SQL SECURITY DEFINER
VIEW `v_subscription_stats` AS
    SELECT 
        `a`.`my` AS `Month-Year`,
        `a`.`subs` AS `Current Subscribed`,
        `b`.`subs` AS `Current Unsubscribed`
    FROM
        (((SELECT 
            DATE_FORMAT(`m`.`start`, '%d-%m-%Y') AS `my`,
                COUNT(`m`.`id`) AS `subs`
        FROM
            `productiondb`.`subscriber` `m`
        GROUP BY YEAR(`m`.`start`) , MONTH(`m`.`start`))) `a`
        LEFT JOIN (SELECT 
            DATE_FORMAT(`m`.`end`, '%d-%m-%Y') AS `my`,
                COUNT(`m`.`id`) AS `subs`
        FROM
            `productiondb`.`subscriber_removed` `m`
        GROUP BY YEAR(`m`.`end`) , MONTH(`m`.`end`)) `b` ON ((`a`.`my` = `b`.`my`)))

Desired Output

+---------+------------+----------+-------------------+---------------------+
| Country | country_id |   Date   | Total Subscribers | Total Unsubscribers |
+---------+------------+----------+-------------------+---------------------+
| U.S     |          1 | 1/1/2019 |                 1 |                   0 |
| U.K     |          2 | 1/2/2018 |                 0 |                   0 |
| UAE     |          3 | 1/3/2013 |                 0 |                   1 |
+---------+------------+----------+-------------------+---------------------+
3
  • Please explain where the "total=9" comes from. – Rick James Jul 10 '19 at 3:05
  • When you show some sample source data than the desured output MUST match to this source data. Edit your samples. And specify your MySQL version additionally. – Akina Jul 10 '19 at 4:30
  • I've edited the output and wrote the MySQL version – Shayan Jul 10 '19 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.