Solved

Do max and sum together in one query..

Posted on 2009-03-30
3
1,845 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
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
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 Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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