How to group dates into month and add a new column after grouping

Question

It's my day 7 with SQL. I've a list of dates, I want to group them according to the month. Here's the table. It has only one column i.e. dateOfService. The format is YYYY-MM-DD.

+---------------+
| dateOfService |
+---------------+
| 2020-05-28    |
| 2020-05-29    |
| 2020-05-30    |
| 2020-06-03    |
| 2020-06-05    |
| 2020-07-21    |
| 2020-07-23    |
| 2020-07-25    |
| 2020-07-28    |
+---------------+

Ignore the DD part, i want to group these dates as follows. Something like:

+---------------+---------------+
| monthOfService| dateOfService |
+---------------+---------------+
|    2020-05    |  2020-05-28   |
|               |  2020-05-29   |
|               |  2020-05-30   |
|---------------|---------------|
|    2020-06    |  2020-06-03   |
|               |  2020-06-05   |
|---------------|---------------|
|    2020-07    |  2020-07-21   |
|               |  2020-07-23   |
|               |  2020-07-25   |
|               |  2020-07-28   |
+---------------+---------------+

I've to create monthOfService from dateOfService itself. It's ok if the rows in monthOfService repeats. Please tell me approach.

Seriously consider handling issues of data display in application code. — Strawberry, Sep 20 '20 at 15:11

forpas · Accepted Answer · 2020-09-20T14:48:49.763

1

With ROW_NUMBER() window function:

select case when rn = 1 then date_format(dateOfService, '%Y-%m') end monthOfService,
       dateOfService
from (       
  select *, 
    row_number() over (partition by date_format(dateOfService, '%Y-%m') order by dateOfService) rn
  from tablename
) t
order by dateOfService

See the demo.
Results:

> monthOfService | dateOfService
> :------------- | :------------
> 2020-05        | 2020-05-28   
> null           | 2020-05-29   
> null           | 2020-05-30   
> 2020-06        | 2020-06-03   
> null           | 2020-06-05   
> 2020-07        | 2020-07-21   
> null           | 2020-07-23   
> null           | 2020-07-25   
> null           | 2020-07-28

If you want the values of monthOfService repeated then:

select date_format(dateOfService, '%Y-%m') monthOfService,
       dateOfService
from tablename
order by dateOfService

See the demo.
Results:

> monthOfService | dateOfService
> :------------- | :------------
> 2020-05        | 2020-05-28   
> 2020-05        | 2020-05-29   
> 2020-05        | 2020-05-30   
> 2020-06        | 2020-06-03   
> 2020-06        | 2020-06-05   
> 2020-07        | 2020-07-21   
> 2020-07        | 2020-07-23   
> 2020-07        | 2020-07-25   
> 2020-07        | 2020-07-28

edited Sep 20 '20 at 14:48

answered Sep 20 '20 at 14:43

forpas

160,666
10
38
76

Instead of `null`. Can we just repeat the above _YYYY-MM_ ? That will be good. – Tanzeel Sep 20 '20 at 14:46
See my 2nd query. – forpas Sep 20 '20 at 14:47
Awesome. The second query is what I'm looking for. :-) Please elaborate a little bit. – Tanzeel Sep 20 '20 at 14:48
The 2nd query is very simple: the function `date_format(dateOfService, '%Y-%m')` returns only the year-month part of each date. – forpas Sep 20 '20 at 14:49
1

Check here some examples: https://www.w3schools.com/sql/func_mysql_date_format.asp – forpas Sep 20 '20 at 14:50
Can you please look into this: https://stackoverflow.com/questions/63986019/how-to-calculate-running-sum-for-each-group-in-mysql – Tanzeel Sep 21 '20 at 04:12
Hi, can you please look into this also https://stackoverflow.com/questions/64004031/what-is-the-correct-syntax-for-partitioning-in-sql I'm facing some version issues i think. – Tanzeel Sep 22 '20 at 06:30
@Tanzeel I saw your question. You can't do it in versions of MySql prior to 8.0. You have to upgrade. – forpas Sep 22 '20 at 06:39
So, is the syntax different or partitioning doesn't works at all in 8.0 ? – Tanzeel Sep 22 '20 at 06:42
It does not work at all in 5.7. It was introduced in version 8.0 – forpas Sep 22 '20 at 06:43
Ok. So what is the best alternative? – Tanzeel Sep 22 '20 at 06:45
@Tanzeel all your other questions were about version 8.0 Why do you use a prior version now? There is no *best* alternative. The only way that you could do something like that involves using variables that are now deprecated and will not work in the future or joins and aggregation that are very complicated. – forpas Sep 22 '20 at 06:50
I was running queries on my local (v.8.0) but today i came to know that my company is using v.5.7. I told them to upgrade. But my boss says no because then other things will start falling apart. Now i've to refactor all the queries. :-( – Tanzeel Sep 22 '20 at 06:55
Check this fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=5e56c1456b1c0ceed1cef0546745cf63 – Tanzeel Sep 22 '20 at 06:56
This is what I meant, in this fiddle you use variables to calculate the running sum. – forpas Sep 22 '20 at 06:58
Now again I'm facing the same problem. That Reward1 and Reward2 'Y' - 'N' values if you rememeber, where sum>=10 and sum>20 respectively. Can you edit the above given fiddle accordingly. I'm blocked. Seriously need your help. – Tanzeel Sep 22 '20 at 07:37
@Tanzeel I would not even have posted an answer for this requirement, if I knew that I couldn't use window functions like row_number(). – forpas Sep 22 '20 at 07:47
Even I didn't know. Just now She (my boss) told me to query things for V5.7. Please help. I'm also trying in parallel. – Tanzeel Sep 22 '20 at 07:49
I have not written code for such requirements without the use of window functions for years now, because such code is deprecated and (tell your boss) it may not work when you will (eventually) upgrade to the latest version of MySql. What you can do is ask a new question where you mention that you want a solution without window functions. For this, I can't help you. – forpas Sep 22 '20 at 07:54
Oh. That's a bad new for me. :-( – Tanzeel Sep 22 '20 at 07:54
1

But thanks for your time. :-) Can you please ask this to your friends: https://stackoverflow.com/questions/64005360/how-to-achieve-partition-by-kind-of-functionality-using-variables-in-mysql-5-7 – Tanzeel Sep 22 '20 at 07:57

score 0 · Answer 2 · answered Sep 20 '20 at 14:38

0

You can use group_concat():

select extract(year_month from date) as yyyymm, group_concat(date)
from t
group by yyyymm;

This uses the default commas separator for the list. That is more common than using a new line in a SQL query.

answered Sep 20 '20 at 14:38

Gordon Linoff

1,242,037
58
646
786

Excellent, but i'm getting commas. `2020-05 | 2020-05-28,2020-05-29,2020-05-30`. Table name is `mytable` – Tanzeel Sep 20 '20 at 14:44
I want them in sepaarte rows. even if monthOfservice repeats – Tanzeel Sep 20 '20 at 14:44
Can you please look into this: https://stackoverflow.com/questions/64025684/how-to-use-group-by-in-a-query-while-using-variables – Tanzeel Sep 23 '20 at 11:02

How to group dates into month and add a new column after grouping

2 Answers2