Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message

New User

Joined: 09 Dec 2008
Posts: 23
Location: india

 Posted: Wed Jul 22, 2009 3:23 pm    Post subject: Want to find out Sum of two rows Hi, We have two tables with following data: table 1 col1 col2 col3 col4 1069 5 AC Active 1069 6 AC Active 1070 5 AC Active 1070 6 AC Active 1070 6 AC Inactive table 2 col1 col2 col5 1069 5 112.00 1069 5 114.00 1069 6 124.00 Col1 & Col2 are common in both the tables so can be used in Joins. Now we need to write a query and select data from both the tables with following conditions: -->table 1 having Col1 = 1069 -->table 1 having Col4 as Active -->sum of Col5 -->Maximum of Col2

expat

Global Moderator

Joined: 14 Mar 2007
Posts: 8657
Location: Back in jolly old England

Posted: Wed Jul 22, 2009 3:31 pm    Post subject:

 Quote: Now we need to write a query and select data from both the tables with following conditions:

Please show us what you have coded along with what your problems are

New User

Joined: 09 Dec 2008
Posts: 23
Location: india

 Posted: Wed Jul 22, 2009 3:38 pm    Post subject: SELECT SUM(B.COL5) FROM TABLE1 A INNER JOIN TABLE2 B ON A.COL1 = B.COL1 AND A.COL2 = B.COL2 WHERE A.COL1 = 1069 AND A.COL4 = 'ACTIVE' GROUP BY B.COL2 I Have coded this query. It is giving individual sum of the groups. But the need is to find the sum of max of all the groups selected by join and where condtion.
ashimer

Active Member

Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

 Posted: Wed Jul 22, 2009 7:49 pm    Post subject: group by col1 and not col2 ...
ashimer

Active Member

Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

Posted: Wed Jul 22, 2009 7:52 pm    Post subject:

 Quote: -->sum of Col5 -->Maximum of Col2

 Quote: But the need is to find the sum of max of all the groups selected by join and where condtion.

New User

Joined: 09 Dec 2008
Posts: 23
Location: india

 Posted: Wed Jul 22, 2009 9:06 pm    Post subject: Yeah i know it seems contradiction Need to find out maximum value from a group and finally need to do a summation.
ashimer

Active Member

Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

 Posted: Wed Jul 22, 2009 9:10 pm    Post subject: did you try your query with change in group by ?
knn9413

New User

Joined: 23 Jul 2009
Posts: 17
Location: US

 Posted: Thu Jul 23, 2009 7:57 pm    Post subject: try this select b.col1, max(b.col2), sum(b.col5) from table1 as a, table2 as b where a.col1 = b.col1 and a.col1 = 1069 and a.col4 = 'ACTIVE' group by b.col1
Raghu navaikulam

Active User

Joined: 27 Sep 2008
Posts: 193
Location: chennai

 Posted: Fri Jul 24, 2009 2:31 pm    Post subject: Reply to: Want to find out Sum of two rows Hi replytovishu Post your expected result in the forum. Regards Raghu
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Find & Replace string in CA-SORT mrgnndhmk CA Products 1 Fri Mar 30, 2018 12:58 am help to replace columns by rows Vikas Maharnawar DFSORT/ICETOOL 8 Tue Mar 06, 2018 3:27 pm Find a string in PS file opened in br... mukkas CLIST & REXX 3 Sat Feb 24, 2018 2:15 pm Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am Can we combine the rows with same key... V S Amarendra Reddy DB2 14 Fri Dec 01, 2017 10:29 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us