nested select - SQL-query

Question

In my MySQL DB I have a table like this

| - value - | - date -   |
|------------------------|
| 1         | 01.01.2016 |
| 2         | 02.01.2016 |
| 3         | 04.02.2016 |
| 5         | 01.01.2017 |
| 1         | 02.01.2017 |
| 5         | 04.02.2017 |

As result, I would like to have a value differences of rows with equal dates (regarding only the day and the month).

So basically I want this as result:

| - value - | - date -   |
|------------------------|
| 1 - 4     | 01.01      |
| 2 - 1     | 02.01      |
| 3 - 5     | 04.02      |

How can I do it in SQL? I can use a nested SELECT to get data of one year like this:

SELECT col FROM (
SELECT value,date FROM Table 
WHERE date BETWEEN '2016-01-01' AND '2016-12-31'
) tab1

Now I would have to add the 2017 year data - but if I just write FROM (SELECT ...) tab1, (SELECT ...) tab2 It will be a "cross prodcut" - And the numbers won't add up.

Mureinik · Accepted Answer · 2017-06-14 06:09:12Z

1

I'd create two subqueries, one for 2016 and one for 2017, and then join them:

SELECT CONCAT(v16, ' - ', v17)
FROM   (SELECT value AS v16, date AS d16
        FROM   mytable
        WHERE  YEAR(date) = 2016) a
JOIN   (SELECT value AS v17, date AS d17
        FROM   mytable
        WHERE  YEAR(date) = 2017) b ON MONTH(d16) = MONTH(d17) AND
                                       DAY(d16) = DAY(d17)

answered Jun 14, 2017 at 6:09

Mureinik

316k54 gold badges398 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kosha Misa Over a year ago

So do I understand it right, that one can only JOIN...? It seems logical that you join them by the day/month-date.

SimarjeetSingh Panghlia · Accepted Answer · 2017-06-14 06:18:46Z

1

try this

SELECT  GROUP_CONCAT(value SEPARATOR '-'),DATE_FORMAT(date,'%m-%d')as date 
FROM table GROUP BY DATE_FORMAT(date,'%m-%d');

edited Jun 14, 2017 at 6:18

answered Jun 14, 2017 at 6:10

SimarjeetSingh Panghlia

2,21016 silver badges14 bronze badges

1 Comment

Kosha Misa Over a year ago

I don't quite get the GROUP BY DATE_FORMAT('2000-07-10','%m-%d') part. Shouldn't it be GROUP BY DATE_FORMAT(date,'%m-%d') ? this would mean, that you group by the day/month and then you can do stuff with this - I could also use SUM instead of GROUP_CONCAT I suppose

Collectives™ on Stack Overflow

nested select - SQL-query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related