0

The query return all the data from previous months and if there is no data at all, for that time, must return zero.Instead it selects only the rows where data is set.

|---------------------|---------------------|
|          id         |     added_on        |
|---------------------|---------------------|
|          1          | 2020-02-20 20:00:00 |
|          2          | 2020-01-20 20:00:00 |
|          3          | 2019-12-20 20:00:00 |
|          4          | 2020-03-01 20:00:00 |
|---------------------|---------------------|

The query I've came up with:

SELECT
COALESCE(COUNT(id)) AS reg_users,
DATE_FORMAT(added_on, "%Y %b") AS reg_date
FROM users 
WHERE added_on
BETWEEN CURDATE() - INTERVAL 12 MONTH AND CURDATE() 
GROUP BY YEAR(added_on), MONTH(added_on)
ORDER BY YEAR(added_on) DESC, MONTH(added_on) DESC;

How could I refactor the code above so it could show zero registrations of the respective month if there is non?

The output should be:

|---------------------|---------------------|
|       reg_users     |       reg_date      |
|---------------------|---------------------|
|          1          | 2020 March          |
|          1          | 2020 February       |
|          1          | 2020 January        |
|          1          | 2019 December       |
|          0          | 2019 November       |
|          0          | 2019 October        |
|          0          | 2019 September      |
|          0          | 2019 August         |
|          0          | 2019 July           |
|          0          | 2019 June           |
|          0          | 2019 May            |
|          0          | 2019 April          |
|---------------------|---------------------|

but the output is:

|---------------------|---------------------|
|       reg_users     |       reg_date      |
|---------------------|---------------------|
|          1          | 2020 March          |
|          1          | 2020 February       |
|          1          | 2020 January        |
|          1          | 2019 December       |
Stephan882
  • 133
  • 2
  • 16
  • 1
    While I think it's generally best to handle issues of data display in application code, you might like to consider improving your question along these lines: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry Mar 05 '20 at 11:18
  • You must generate dates list and join your data to it. – Akina Mar 05 '20 at 11:27
  • What is a "date list"?What do you mean?I just want to make the code show the months without registered users on it with count of zero - "reg_users". – Stephan882 Mar 05 '20 at 12:10
  • I think you need this: https://stackoverflow.com/q/510012/2430549 "Get a list of dates between two dates" – HoldOffHunger Mar 07 '20 at 17:26

0 Answers0