--- a/backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionAggregateValue.java	Tue Jun 19 14:23:15 2018 +0200
+++ b/backend/src/main/java/org/dive4elements/river/model/sinfo/CollisionAggregateValue.java	Tue Jun 19 14:24:41 2018 +0200
@@ -16,6 +16,8 @@
 import java.util.List;
 
 import org.dive4elements.river.backend.SessionHolder;
+import org.dive4elements.river.backend.utils.DateUtil;
+import org.dive4elements.river.model.River;
 import org.hibernate.SQLQuery;
 import org.hibernate.Session;
 import org.hibernate.type.StandardBasicTypes;
@@ -96,7 +98,7 @@
     }
 
     /**
-     * Selects the collision aggregate values of a data series in a km range from the database
+     * Selects from the database the collision aggregate values of a data series in a km range
      */
     public static List<CollisionAggregateValue> getValuesByKm(final Collision parent, final double kmLo, final double kmHi) {
         final Session session = SessionHolder.HOLDER.get();
@@ -106,9 +108,11 @@
                 + " FROM collision_values v INNER JOIN collision s ON v.collision_id=s.id"
                 + " WHERE (collision_id=:parent)"
                 + " AND (station BETWEEN (:kmLo - 0.0001) AND (:kmHi + 0.0001))"
-                + " GROUP BY FLOOR(station+0.4999)").addScalar("station", StandardBasicTypes.DOUBLE)
-                .addScalar("count", StandardBasicTypes.INTEGER).addScalar("date_min", StandardBasicTypes.DATE)
-                .addScalar("date_max", StandardBasicTypes.DATE).addScalar("gauge_w", StandardBasicTypes.DOUBLE);
+                + " GROUP BY FLOOR(station+0.4999)"
+                + " ORDER BY FLOOR(station+0.4999)")
+                .addScalar("station", StandardBasicTypes.DOUBLE).addScalar("count", StandardBasicTypes.INTEGER)
+                .addScalar("date_min", StandardBasicTypes.DATE).addScalar("date_max", StandardBasicTypes.DATE)
+                .addScalar("gauge_w", StandardBasicTypes.DOUBLE);
         query.setInteger("parent", parent.getId());
         query.setDouble("kmLo", new Double(kmLo));
         query.setDouble("kmHi", new Double(kmHi));
@@ -122,4 +126,37 @@
         }
         return values;
     }
+    
+    /**
+     * Selects from the database the collision aggregate values of a km range of a river and a range of years
+     */
+    public static List<CollisionAggregateValue> getValuesByKm(final River river, final double kmLo, final double kmHi, final int fromYear, final int toYear) {
+        final Session session = SessionHolder.HOLDER.get();
+        final SQLQuery query = session.createSQLQuery("SELECT FLOOR(station+0.4999) AS station, COUNT(*) AS count,"
+                + " MIN(event_date) AS date_min, MAX(event_date) AS date_max,"
+                + "  AVG(gauge_w) AS gauge_w"
+                + " FROM collision_values v INNER JOIN collision s ON v.collision_id=s.id"
+                + " WHERE (s.river_id=:river_id)"
+                + " AND (station BETWEEN (:kmLo - 0.0001) AND (:kmHi + 0.0001))"
+                + " AND (event_date BETWEEN :fromDate AND :toDate)"
+                + " GROUP BY FLOOR(station+0.4999)"
+                + " ORDER BY FLOOR(station+0.4999)")
+                .addScalar("station", StandardBasicTypes.DOUBLE).addScalar("count", StandardBasicTypes.INTEGER)
+                .addScalar("date_min", StandardBasicTypes.DATE).addScalar("date_max", StandardBasicTypes.DATE)
+                .addScalar("gauge_w", StandardBasicTypes.DOUBLE);
+        query.setInteger("river_id", river.getId());
+        query.setDouble("kmLo", new Double(kmLo));
+        query.setDouble("kmHi", new Double(kmHi));
+        query.setDate("fromDate", DateUtil.getStartDateFromYear(fromYear));
+        query.setDate("toDate", DateUtil.getEndDateFromYear(toYear));
+        final List<CollisionAggregateValue> values = new ArrayList();
+        final List<Object[]> rows = query.list();
+        if (rows != null) {
+            for (int i = 0; i <= rows.size() - 1; i++) {
+                values.add(new CollisionAggregateValue(null, (double) rows.get(i)[0], (Date) rows.get(i)[2], (Date) rows.get(i)[3],
+                        (int) rows.get(i)[1], (double) rows.get(i)[4]));
+            }
+        }
+        return values;
+    }
 }