## I’m having trouble with a simple query in DB2 for AS/400

1072160 pts.
Tags:
AS/400
AS/400 queries
IBM DB2
I know I'm an amateur when it comes to AS/400. I have this "easy" query but I still can't do it. Here's what I have:
```A          B        C
1        1100       5
1        1100       5
1         500       1
2        1200       4
2        1200       4
2         600       1
3        1300       3
3        1300       3
3         700       1```
And this is how I want it returned:
```A     B    C
1   1100   10
2   1200   8
3   1300   6```
I tried this but it's only returning the total # of C, not only the ones selected.
```SELECT
t.A
,MAX(t.B)
,SUM(t.C)
FROM t
GROUP BY
t.A```
What am I doing wrong?

Thanks. We'll let you know when a new response is added.

## Discuss This Question: 8 Replies

Thanks. We'll let you know when a new response is added.
• ...not only the ones selected.

It's the total of all because the "ones selected" are all. You have no criteria to tell the query which ones to select, so it has no choice but to select all rows. You need to include criteria for selection.

This question thread is difficult because it doesn't quite seem like a real-life problem. It seems more like an academic question. Can you describe the business reason behind the question?

You've shown the work you've done, so there is at least guidance available.

Tom

report
• Try the following:

SELECT T t.A, max(t.B), (SELECT sum(t.C) from T group by T.A) from T group by T.A

Write the Select statement from the third column out.

report
• SELECT T t.A, max(t.B), (SELECT sum(t.C) from T group by T.A) from T group by T.A

This also has no selection criteria. In particular, it fails because the inner subselect returns multiple rows.

Tom

report
• Try this:-

select a,b,sum(c) from t
where b in(select max(b) from t group by a)
group by a,b

report
• select a,b,sum(c) from t
where b in(select max(b) from t group by a)

group by a,b

That's closer, and it'll work for the given data set. However, add this row to the data set:

`INSERT INTO t VALUES('4',  500, 1)`

The result then comes out wrong.

Tom

report
• Here is a rough draft of the SQL

Don't have a system available at the moment so I can't test it.

I know that:
select t.a, max(t.b) as myMax from T group by t.a
will produce a list of maxs

a  mymax
1 1100
2 1200
3 1300

so if I were to join this to t, I think I'll get the desired results

Select t1.a, t1.b, sum(t1.c) from t t1
join
( select t.a, max(t.b) as myMax from T group by t.a ) t2
on t1.a = t2.a and t1.b = t2.myMax
group by t1.a, t1.b

report
• Another possibility .. I don't think it will work but it's worth a try if the last draft doesn't work.

Select t.a, t.b, sum(t.c) from t

group by t.a, t.b

having max(t.b)

report
• Finally found a possible original source of this question from almost four years ago. If it's academic, a potential solution is already posted; and the assignment is probably long overdue.

I don't care much for that earlier posted solution though it looks reasonable. I prefer it like this:

```SELECT T1.A ,MAX(T1.B) ,SUM(T1.C) FROM t T1
WHERE T1.B=(select max(T2.B) from t T2
where T2.A = T1.A
group by T2.A)
GROUP BY T1.A```

It just reads better for me like that.

Tom