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

Author Message
Sudhir KL

New User

Joined: 15 Feb 2008
Posts: 17
Location: Safe Planet

 Posted: Mon May 31, 2010 2:39 pm    Post subject: SQL Query to eliminate lowest value and get count How to exclude a minimum value while calculating sum in a SQL query. The values are grouped by an ID column. ( Decimal column) EX. If there are 3 values 50,100,200 While calculating sum we should exclude the lowest one i.e 50 and result should be 300.. If there are duplicates, 50,50, 100,200 ...then the count should be 350( excluding the latest duplicate value) How do u write sql query for it.

Escapa

Senior Member

Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

Posted: Mon May 31, 2010 2:51 pm    Post subject:

 Code: SELECT COL1,                                               (SUM(VAL_COL) - MIN(VAL_COL))    FROM tabname GROUP BY COL1;
GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

 Posted: Mon May 31, 2010 2:56 pm    Post subject: select sum(col1) - min(col1) from table1 (oops : too late)
Sudhir KL

New User

Joined: 15 Feb 2008
Posts: 17
Location: Safe Planet

 Posted: Mon May 31, 2010 3:05 pm    Post subject: thank you very much
Escapa

Senior Member

Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

Posted: Mon May 31, 2010 3:12 pm    Post subject:

 Sudhir KL wrote: thank you very much

Alrite but I still couldn't get matter of count in subject part...

SQL Query to eliminate lowest value and get count
enrico-sorichetti

Global Moderator

Joined: 14 Mar 2007
Posts: 10364
Location: italy

 Posted: Mon May 31, 2010 5:39 pm    Post subject: Reply to: SQL Query to eliminate lowest value and get count count ... just the wrong word choice!
Sudhir KL

New User

Joined: 15 Feb 2008
Posts: 17
Location: Safe Planet

Posted: Mon May 31, 2010 5:47 pm    Post subject: Reply to: SQL Query to eliminate lowest value and get count

 Quote: SQL Query to eliminate lowest value and get count

 Quote: count ... just the wrong word choice!

It should have been something like below.

SQL Query to substract the lowest value in the column and get the SUM total
Escapa

Senior Member

Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

Posted: Mon May 31, 2010 10:01 pm    Post subject: Re: Reply to: SQL Query to eliminate lowest value and get co

Sudhir KL wrote:
 Quote: SQL Query to eliminate lowest value and get count

 Quote: count ... just the wrong word choice!

It should have been something like below.

SQL Query to substract the lowest value in the column and get the SUM total

Ohh.. ok... I thought you needed count of records for each group by records or something...
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 0 Wed Nov 22, 2017 10:23 pm Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 3 Tue Nov 07, 2017 8:34 pm Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am

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