-1

I am creating a report in iReport. I am using postgresql9.2 data base. Trying to create a summation based on another field. First i create a report group by field by vehicle no, then create a variable where I'm trying to execute sum with group by field.

My report result

vh_name    |  va_date   | trip_no |
Vehicle-01 | 2019-01-15 |   1     |
Vehicle-01 | 2019-01-15 |   1     |
Vehicle-01 | 2019-01-16 |   1     |
-----------+------------+---------+
Vehicle-02 | 2019-01-17 |   1     |
Vehicle-02 | 2019-01-18 |   1     |
Vehicle-02 | 2019-01-19 |   1     |
-----------+------------+---------+
Vehicle-03 | 2019-01-17 |   1     |
Vehicle-03 | 2019-01-17 |   1     |
Vehicle-03 | 2019-01-17 |   1     |
Vehicle-03 | 2019-01-18 |   1     |
-----------+------------+---------+

My expected result

vh_name    |  va_date   | trip_no | total_trip |
Vehicle-01 | 2019-01-15 |   1     |            |
Vehicle-01 | 2019-01-15 |   1     |     3      |
Vehicle-01 | 2019-01-16 |   1     |            |
-----------+------------+---------+-------------
Vehicle-02 | 2019-01-17 |   1     |            |
Vehicle-02 | 2019-01-18 |   1     |     3      |
Vehicle-02 | 2019-01-19 |   1     |            |
-----------+------------+---------+-------------
Vehicle-03 | 2019-01-17 |   1     |            |
Vehicle-03 | 2019-01-17 |   1     |            |
Vehicle-03 | 2019-01-17 |   1     |      4     |
Vehicle-03 | 2019-01-18 |   1     |            |
-----------+------------+---------+-------------
Zakir Hossain
  • 440
  • 5
  • 20

1 Answers1

1

Based on Using SUM() without grouping the results, your SQL should look something like that:

SELECT vh_name, va_dat, trip_no, SUM(trip_sum.amount) as trip_sum
FROM my_trips
CROSS JOIN (
    SELECT SUM(amount) as amount FROM my_trips AS amount
    GROUP BY vh_name) trip_sum

Note that unique counting may be easer (in case the trip_no is always 1):

SELECT vh_name, va_dat, trip_no, COUNT(DISTINCT vh_name) as amount
FROM my_trips
abstraktor
  • 955
  • 9
  • 20