SQL & PL/SQL

Dear all,

I have created the following table named myenterprise

CITY       STOREID    MONTH_NAME TOTAL_SALES            
---------- ---------- ---------- ---------------------- 
paris      id1        January    1000                   
paris      id1        March      7000                   
paris      id1        April      2000                   
paris      id2        November   2000                   
paris      id3        January    5000                   
london     id4        Janaury    3000                   
london     id4        August     6000                   
london     id5        September  500                    
london     id5        November   1000

If I want to find What is the total sales per city? I will run the following query

SELECT city, SUM(total_sales) AS TOTAL_SALES_PER_CITY
FROM myenterprise
GROUP BY city
ORDER BY city, TOTAL_SALES_PER_CITY;

which works pretty well and produces the desired result, that is,

CITY       TOTAL_SALES_PER_CITY   
---------- ---------------------- 
london     10500                  
paris      17000            

Now in one of my SQL books (Mastering Oracle SQL) I have found another method by using SUM but this time as an analytic function. Here is what the book method suggests as an alternative solution for the problem:

SELECT city, 
       SUM(SUM(total_sales)) OVER (PARTITION BY city) AS TOTAL_SALES_PER_CITY
FROM myenterprise
GROUP BY city
ORDER BY city, TOTAL_SALES_PER_CITY;

I know that analytic functions are run after the GROUP BY clause has been processed completely and unlike ordinary aggregate functions, they return their result for each row belonging to the partitions specified in the partition clause (if there is a partition clause defined).

Now my problem is that I don't understand what we must use two SUM functions? if we use only one, that is,

SELECT city, 
       SUM(total_sales) OVER (PARTITION BY city) AS TOTAL_SALES_PER_CITY
FROM myenterprise
GROUP BY city
ORDER BY city, TOTAL_SALES_PER_CITY;

This will produce the following error:

Error starting at line 2 in command:
SELECT city, 
       SUM(total_sales) OVER (PARTITION BY city) AS TOTAL_SALES_PER_CITY
FROM myenterprise
GROUP BY city
ORDER BY city, TOTAL_SALES_PER_CITY
Error at Command Line:2 Column:11
Error report:
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:

The error is generated for the line 2 column 11 that is, for the expression SUM(total_sales), well it's true that total_sales doesn't appear in the GROUP BY clause, but this should not cause a problem, as it has been used in an analytic function, so it is evaluated after the GROUP BY clause.

So here is my question:

Why using SUM(SUM(total_sales)) instead of SUM(total_sales)?


Thanks in advance!
:)





In the case where you're interested, this is my table definition:

DROP TABLE myenterprise;
CREATE TABLE myenterprise(
city VARCHAR2(10), 
storeid VARCHAR2(10),
month_name VARCHAR2(10),
total_sales NUMBER);

INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id1', 'January', 1000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id1', 'March', 7000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id1', 'April', 2000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id2', 'November', 2000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id3', 'January', 5000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('london', 'id4', 'Janaury', 3000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('london', 'id4', 'August', 6000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('london', 'id5', 'September', 500);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('london', 'id5', 'November', 1000);

Edited by: dariyoosh on Apr 9, 2009 4:51 AM