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: 10439
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 Creation of Datasets dynamically on t... Raghu M N DFSORT/ICETOOL 8 Fri Mar 16, 2018 12:56 am Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am How to replace the below query? pkmurali DB2 12 Tue Feb 27, 2018 9:51 pm eliminate dups in VB file anatol DFSORT/ICETOOL 6 Tue Feb 13, 2018 2:59 am Need Guidance regarding UPDATE query Poha Eater DB2 3 Mon Feb 12, 2018 11:35 pm

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