[Jasper] How can I sum based on another column value

I have a report that returns data using PSQL, the data returned is like a master-detail form. So I get the data returned as:

Child Name  Parent Name  Parent Value

1                    Parent 1         20

2                    Parent 2         10

3                    Parent 1         20

4                    Parent 2         10

5                    Parent 1         20

 

So, I want to sum Parent Values based on the Parent Name (which is repeated, I need it to be distinct), so the sum would be 30, when I try the variable with Sum on Parent Value column I get value 70 instead of 30.

What would be perfect here to solve this?

 

Thanks in advance for you all

baboarab's picture
Joined: Aug 4 2022 - 2:49am
Last seen: 1 month 2 days ago

1 Answer:

I would try to bring the data into the report correctly.  In this case, adding another column to represent the parent_distinct_total.

Below example from Oracle:

SELECT ChildName
     , ParentName
     , ParentValue
     , SUM(DISTINCT ParentValue) OVER () AS parent_distinct_total
FROM (
    SELECT 1 AS ChildName,'Parent1' AS ParentName,20 AS ParentValue FROM dual
    UNION ALL
    SELECT 2,'Parent2',10 FROM dual
    UNION ALL
    SELECT 3,'Parent1',20 FROM dual
    UNION ALL
    SELECT 4,'Parent2',10 FROM dual
    UNION ALL
    SELECT 5,'Parent1',20 FROM dual
) A
ORDER BY ChildName
+---------+----------+-----------+---------------------+
|CHILDNAME|PARENTNAME|PARENTVALUE|PARENT_DISTINCT_TOTAL|
+---------+----------+-----------+---------------------+
|1        |Parent1   |20         |30                   |
|2        |Parent2   |10         |30                   |
|3        |Parent1   |20         |30                   |
|4        |Parent2   |10         |30                   |
|5        |Parent1   |20         |30                   |
+---------+----------+-----------+---------------------+

jgust's picture
2680
Joined: Jun 10 2010 - 6:39am
Last seen: 22 hours 15 min ago
Feedback
randomness