?
Solved

Do max and sum together in one query..

Posted on 2009-03-30
3
Medium Priority
?
2,099 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 180 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:
Dave Ford earned 195 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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…
Article by: gr8gonzo
Git can be a complicated version control system for beginners, but it definitely is one of the best ones out there. Since this article assumes that you're starting at square one, it will skip over things that Git -can- do and will focus on the typic…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview
Suggested Courses

850 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