Solved

Do max and sum together in one query..

Posted on 2009-03-30
3
1,808 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
3 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 60 total points
Comment Utility
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
Comment Utility

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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now