Posted: Mon Dec 25, 2006 5:27 pm Post subject: How can the result set be used as input table
Hi All,
Assume I have a table Dept_Stores in denormalized format :
Code:
Dept_Stores:
Dept_Name Money
========== ======
Marketing 2500
Production 3500
Sales 2500
Sales 4500
Marketing 2500
Production 1500
Sales 3500
Now my question is, if I want to know which department is having the maximum money on the whole, what needs to be done. I was trying to use a query
Code:
Select Dept_Name, Sum(Money) as Total_Mny from Dept_Stores
Group by Dept_Name;
The above query would result me
Code:
Dept_Name Total_Mny
=========== =========
Production 5000
Marketing 5000
Sales 10500
among the three departments "Sales" is standing on top with the maximum money.. How can I achieve this result , using the query listed above . Is there any way I can apply functions like Max, Min, avg on the resulted column Total_Mny.
Thanks for your answer. Your reply would surely result in the required answer. Actually I'm thinking if there could be any way where we can use the result set of query "SELECT DEPT_NAME, SUM(MONEY) AS TOTAL_MNY FROM DEPT_STORES GROUP BY DEPT_NAME" as table to be searched for Max of Total_Mny. My idea is to find a generic way of solving a similar kind of req. Please let me know, in case my question is not clear.
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Tue Dec 26, 2006 5:06 pm Post subject:
Cravikota,
You can nest built-in column and scalar functions within other functions in the following ways:
Nest scalar functions within other scalar functions
Nest scalar functions within column functions
Nest column functions within scalar functions
However you canNOT nest column functions within other column functions. Here max and Sum are both column functions. However if you still insist on getting the results try this
Code:
SELECT A.*
FROM (SELECT DEPT_NAME
,SUM(MONEY) AS MONEY
FROM Dept_Stores
GROUP BY DEPT_NAME) A
WHERE A.MONEY = (SELECT MAX(A.MONEY)
FROM (SELECT DEPT_NAME
,SUM(MONEY) AS MONEY
FROM Dept_Stores
GROUP BY DEPT_NAME ) A)
;
You are really genius. Thanks for your solution. Now actually I tried to solve this using Common Table Expressions.. but end up in an error. Can you tell me where I could have gone wrong or can't we use the way I used.
Code:
WITH DUMMY_TABLE (DEPT_STORES, TOTAL_MNY) AS
(
SELECT DEPT_NAME, SUM(MONEY) AS TOTAL_MNY
FROM DEPT_STORES
GROUP BY DEPT_NAME
)
SELECT DEPT_NAME, MAX(TOTAL_MNY) FROM DUMMY_TABLE
When I tried to run this, I'm getting error with sqlcode - 119 .
Code:
------------------------------ Commands Entered ------------------------------
WITH DUMMY_TABLE (DEPT_NAME, TOTAL_MNY) AS
(SELECT DEPT_NAME , SUM (MONEY) AS TOTAL_MNY FROM DEPT_STORES
GROUP BY DEPT_NAME)
SELECT DEPT_NAME, MAX(TOTAL_MNY) FROM DUMMY_TABLE;
------------------------------------------------------------------------------
WITH DUMMY_TABLE (DEPT_NAME, TOTAL_MNY) AS (SELECT DEPT_NAME , SUM (MONEY) AS TOTAL_MNY FROM DEPT_STORES GROUP BY DEPT_NAME) SELECT DEPT_NAME, MAX(TOTAL_MNY) FROM DUMMY_TABLE
Code:
SQL0119N An expression starting with "DEPT_NAME" specified in a SELECT clause,
HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or
it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column
function and no GROUP BY clause is specified. SQLSTATE=42803
SQL0119N An expression starting with "DEPT_NAME " specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.
Explanation:
The SELECT statement has one of the following errors:
o The identified expression and a column function are contained
in the SELECT clause, HAVING clause, or ORDER BY clause but
there is no GROUP BY clause
o The identified expression is contained in the SELECT clause,
HAVING CLAUSE, or ORDER BY clause but is not in the GROUP BY
clause.
The identified expression is an expression that starts with
"<expression-start>". The expression may be a single column
name.
If the NODENUMBER or PARTITION functions are specified in the
HAVING clause, then all partitioning key columns of the
underlying table are considered to be in the HAVING clause.
The statement cannot be processed.
User Response:
Correct the statement by including the expression in the GROUP BY
clause that are in the SELECT clause, HAVING clause, or ORDER BY
clause or by removing the column function from the SELECT
statement.
sqlcode : -119
sqlstate : 42803
Where did I make a mistake in writing this query ?
I guess, I got the culprit here. I used a Column function in an SQL SELECT with out using a GROUP BY clause. This might be the reason for my error. Actually I was trying to use Common Table Expressions to solve my problem, but didn't realize that I'm not using GROUP BY when Column function is used .
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
.
.