1

I am doing a migration task, migrating a java application from Oracle Datastore to MySql 5.

You can imagine that some queries need to be rewritten. everything goes well until I came to Reporting module. Hope you guys could give me some hints.

Shortly I have a table: (say table name "Foo")

+-------------+-------------+
|             |             |
| d (datetime)| v (number)  |
+-------------+-------------+
| 2000-01-01  |     3       |
| 2000-01-11  |     2       |
| 2002-07-01  |     1       |
+-------------+-------------+

What i have done so far:

when user gives startdate, enddate as parameters, i generate sum-report by Day and by month:

e.g. startDate 2000-01-01, endDate: 2003-01-01

+-------------+-------------+
| day         | Sum         |
+-------------+-------------+
| 2000-01-01  |     3       |
| 2000-01-11  |     2       |
| 2002-07-01  |     1       |
+-------------+-------------+


+-------------+-------------+
| Month       | Sum         |
+-------------+-------------+
| 2000-01     |     5       |
| 2002-07     |     1       |
+-------------+-------------+

but the requirement is, if the date is not in table, list the Day/month anyway, with sum=0. like:

+-------------+-------------+
| Month       | Sum         |
+-------------+-------------+
| 2000-01     |     5       |
| 2000-02     |     0       |
| 2000-03     |     0       |
| 2000-04     |     0       |
| 2000-05     |     0       |
|  ...        |    ...      |
| 2002-07     |     1       |
|  ...        |     0       |
+-------------+-------------+

This was achieved with Oracle, but I am stuck with Mysql. Can anyboy give some hints, better with example codes, how to achieve it with mySql?

thank you.

Kent
  • 189,393
  • 32
  • 233
  • 301
  • http://stackoverflow.com/questions/7252460/mysql-group-by-and-fill-empty-rows should give you some tricks. How was that done in Oracle, by the way ? – Raphaël Althaus Jul 27 '12 at 10:34
  • How was it achieved with Oracle? – stracktracer Jul 27 '12 at 10:34
  • @RaphaëlAlthaus I would take a look that link. with Oracle, I generate the month str: SELECT DISTINCT TO_CHAR(add_months(to_date(?,'yyyy/mm'),ROWNUM-1) ,'yyyy/mm') m FROM DUAL CONNECT BY LEVEL <=months_between( to_date(?,'yyyy/mm'),to_date(?,'yyyy/mm'))+1 ) months then left join the Foo table. – Kent Jul 27 '12 at 10:42

0 Answers0