CODEWITHSUNDEEP
mysqlsqlselect
Kosha Misa
Kosha Misa

Reputation: 351

nested select - SQL-query

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.

Upvotes: 1

Views: 61

Answers (2)

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

try this

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

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

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)

Upvotes: 1

Related Questions