Solved

Do max and sum together in one query..

Posted on 2009-03-30
3
1,860 Views
Last Modified: 2013-11-25
I have a column in oracle that I am using to create a query and this column has value
sum(user_count + data_count)/300 when application reads this query it will use to create the following query

select sum(user_count + data_count) as count_per_sec,datetime from tableA group by datetime
where datetime >='2009-01-00.00.00' and datetime < '2009-02-01-00.00.00'
Now I need to get the max value from the datetime period and I dont want to change the query that reads the custom column from oracle what value then should I need to change the column that will reflect the max value ?
I tried to change the column as max(sum(user_count + data_count)/300 ) as max_value but I got the error when I ran the query in db2
SQL0112N  The operand of the column function "MAX" includes a column function,
a scalar fullselect, or a subquery.  SQLSTATE=42607

Let me know how to resolve this?
0
Comment
Question by:mahjag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 60 total points
ID: 24023608
you need a subquery
selct max(count_per_sec) from (
select sum(user_count + data_count) as count_per_sec,datetime from tableA group by datetime
where datetime >='2009-01-00.00.00' and datetime < '2009-02-01-00.00.00'
) sq

Open in new window

0
 
LVL 18

Accepted Solution

by:
daveslash earned 65 total points
ID: 24023784

You may want to checkt the datatype on your datetime column since I believe Oracle stores its dates and timestamps differently than DB2.

Otherwise, the angelic one's solution looks good.

HTH,
DaveSlash


 
although I might use a WITH clause to make it clearer:
 
e.g.
with TheSummary as (
  select sum(user_count + data_count) as count_per_sec,
         datetime 
  from   tableA 
  where  datetime >='2009-01-00.00.00'
    and  datetime < '2009-02-01-00.00.00'
  group by datetime
)
selct max(count_per_sec) 
from  TheSummary

Open in new window

0
 

Author Comment

by:mahjag
ID: 24028930
custom column in oracle can take only column_names and db2 query that is written from this custom column in oracle already does sum (custom_column from oracle), I dont think I can write a full query and db2 can consume and change that data..

I need to structure this as

select max(a.conn) from
( select sum(user_count + data_count) as conn from.....

I cannot do all this in custom column where it can take only the column that needs to be summed up.. let me know any altaernatives..
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION Subversion is a fantastic version management and software configuration management tool for teams and individuals to manage software applications. It offers many advantages over it's CVS ancestor such as it's built-in compatibility w…
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question