Sum of Selected rows from column using pentaho report designer

Question

I'm using Pentaho Report Designer (PRD) 3.8. My data structure is like this

id| name  |value |
1 | Jack  | 200  |
2 | Will  | 300  |
3 | Smith | 400  |
4 | Jack  | 500  |

Now the problem is that I want to get sum for column Value with only selected rows. Is there any methods like

IF([name]='Jack' then sum([value]))

so value of sum is 700.

I need the sum value in formula not in query because some name values are dummy values too.

Is that pseudo-SQL in your request or that's about SQL Server? — Alma Do, Feb 19 '14 at 10:39
how are you populating these dummy names if not from your table? — Neels, Feb 20 '14 at 05:34

score 0 · Answer 1 · answered Feb 19 '14 at 10:41

0

Try CASE

SEELCT
SUM(CASE WHEN `name` ='jack' THEN `value` ELSE 0 END)
FROM `table`

answered Feb 19 '14 at 10:41

M Khalid Junaid

63,861
10
90
118

i can't use sql cause some name values are dummy not used from table. – ANonmous Change Feb 19 '14 at 10:42

score 0 · Answer 2 · answered Feb 19 '14 at 11:00

0

In MySQL, SELECT SUM(value) FROM my_table WHERE name = 'jack' would return the correct result. I don't know Pentaho but, presumably it could access a VIEW constructed along these lines.

answered Feb 19 '14 at 11:00

Strawberry

33,750
13
40
57

some name values are dummy values too not from table – ANonmous Change Feb 19 '14 at 11:02

score 0 · Answer 3 · edited May 02 '14 at 08:44

0

How to use Select sum within a Select. For Example

SELECT 
  aa,
  bb,
  part_no
  Select sum (Unit_cost) as Cost_total 
  from COST_TABLE
  where part_no = det_table.Part_no
  Sale-TOT
FROM Det_table
WHERE status = 'o'
GROUPED BY Part_no

Note : There are Multiple Entries of Part Number in COST_TABLE

edited May 02 '14 at 08:44

Mack

2,556
1
26
44

answered May 02 '14 at 08:15

Akram Virk

1

Sum of Selected rows from column using pentaho report designer

3 Answers3