?
Solved

Do max and sum together in one query..

Posted on 2009-03-30
3
Medium Priority
?
1,954 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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

777 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