CODEWITHSUNDEEP
phpmysqlgroup-by
Crash Override
Crash Override

Reputation: 461

MySQL query the same table with two date fields and group by same ID

I'm having a hard time getting results from a DB the way I need them, this is my table:

+----+---------------------+---------------------+-----------+------------+--------+
| id |      opendate       |      closedate      | openprice | closeprice | sameid |
+----+---------------------+---------------------+-----------+------------+--------+
|  1 | 2015-09-20 19:17:52 | NULL                | 10        | NULL       |     20 |
|  2 | NULL                | 2015-09-20 20:17:00 | NULL      | 35         |     20 |
|  3 | 2015-09-15 19:17:52 | NULL                | 15        | NULL       |     10 |
|  4 | NULL                | 2015-09-16 20:17:00 | NULL      | 25         |     10 |
+----+---------------------+---------------------+-----------+------------+--------+

I need to get all the rows grouped by the column sameid like this:

+----+---------------------+---------------------+-----------+------------+--------+
| id |      opendate       |      closedate      | openprice | closeprice | sameid |
+----+---------------------+---------------------+-----------+------------+--------+
|  1 | 2015-09-20 19:17:52 | 2015-09-20 20:17:00 |        10 |         35 |     20 |
|  3 | 2015-09-15 19:17:52 | 2015-09-16 20:17:00 |        15 |         25 |     10 |
+----+---------------------+---------------------+-----------+------------+--------+

And this is what I have tried so far:

(SELECT * FROM table WHERE opendate >= '2015-08-08 00:00:01') UNION (SELECT * FROM table WHERE closedate <= '2015-10-15 23:59:59')

I can get all the rows but I can not fin a way to group them by sameid, I tried using GROUP BY sameid without success.

Hope you guys can help me

Thank you

UPDATE The table was designed that way long ago, (not by me) and there is too much information stored, I'm not allowed to redesign the DB schema either.

Upvotes: 0

Views: 188

Answers (3)

bingo
bingo

Reputation: 2308

This will produce your exact output.

    SELECT
        MIN(id) as id,
        MAX(open_date) as open_date,
        MAX(close_date) as close_date,
        MAX(open_price) as open_price,
        MAX(close_price) as close_price,
        sameid
    FROM
        `table`
    GROUP BY
        sameid
    ORDER BY id ASC

Upvotes: 0

Dat Pham
Dat Pham

Reputation: 1865

If your table format exactly what you describe, then this should work:

SELECT id, MAX(opendate), MAX(closedate), MAX(openprice), MAX(closeprice), sameid FROM table GROUP BY sameid;

However, I think you should redesign your database schema, seperate open info and close info into 2 rows (they could be in 2 tables or in same table). It's would be better for you to work with rather than trying some workaround.

Regards,

Upvotes: 2

Malitta N
Malitta N

Reputation: 3423

Try this

SELECT t1.opendate, t2.closedate, t1.openprice, t2.closeprice FROM `table` t1 JOIN `table` t2 ON t2.sameid = t1.sameid WHERE t1.opendate >= '2015-08-08 00:00:01' AND t2.closedate <= '2015-10-15 23:59:59' AND t1.opendate IS NOT NULL AND t2.closedate IS NOT NULL

Upvotes: 0

Related Questions