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

352580 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?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta

    ...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

    125,585 pointsBadges:
    report
  • RossHowatson

    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.

    295 pointsBadges:
    report
  • TomLiotta

    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

    125,585 pointsBadges:
    report
  • akfoldgit

    Try this:-

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

     

    10 pointsBadges:
    report
  • TomLiotta
    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

    125,585 pointsBadges:
    report
  • philpl1jb

    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

    51,365 pointsBadges:
    report
  • philpl1jb

    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)

    51,365 pointsBadges:
    report
  • TomLiotta

    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

    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following