Home » SQL & PL/SQL » SQL & PL/SQL » Problem with SUM function query (Oracle 10g, Windows)
Problem with SUM function query [message #582470] Thu, 18 April 2013 11:27 Go to next message
hamb
Messages: 5
Registered: December 2012
Junior Member
Query:

select  b.loc , b.week, b.vvalue2, b.sum_ships, nvl(a.up_date,'None') as datee , nvl( a.ships_rel_total,0) as Total_released
from (
      SELECT l.loc  , sr1.vvalue1 as Week, sr.vvalue2 , to_char(ss.udate, 'YYYY-MM-DD') as up_date ,  count(distinct s.ship_id ) as ships_rel_total
      FROM ship s, loct l,
         ship_num sr1,  ship_num sr, ship_stat ss, ship_stat ss1
      WHERE ......
 
      Group by l.loc , sr1.vvalue1, sr.vvalue2 , to_char(ss.udate, 'YYYY-MM-DD')
) A, 
 
( Select  c.loc, c.week , c.vvalue2 ,  sum(c.total_ships) sum_ships  
from 
    ( SELECT distinct l.loc  , sr1.vvalue1 as Week, sr.vvalue2 , count(s.ship_id ) as Total_Ships
      FROM ship s, loct l,
          ship_num sr1,  ship_num sr, ship_stat ss, ship_stat ss1
      WHERE ......
 
      Group by l.loc , sr1.vvalue1 , sr.vvalue2  ) C
      
Group by c.loc , c.week , c.vvalue2  ) B
  
where a.loc (+) = b.loc
and a.vvalue2  (+) = b.vvalue2 
order by b.loc, b.week , b.vvalue2,a.up_date;



My query is giving me this ;

Query Output:


LOC         WEEK          VALUE2        *SUM_SHIPS*       DATEE         TOtAL_SHIPS_RELEASED
 AA          111                BB             (12) 26                 10-05-12            2
 AA          111                BB              (12)26                 11-05-12            4
 AA          111                CC              (2)26                 14-05-12            1


It is calculating right sum but I dont want to add BB two times. I need 14 instead of 26.

[Updated on: Thu, 18 April 2013 11:33]

Report message to a moderator

Re: Problem with SUM function query [message #582474 is a reply to message #582470] Thu, 18 April 2013 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59095
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your query is wrong.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Problem with SUM function query [message #582487 is a reply to message #582470] Thu, 18 April 2013 14:08 Go to previous message
LKBrwn_DBA
Messages: 433
Registered: July 2003
Location: WPB, FL
Senior Member
Your issue is in the "up_date" column, if you choose MAX(up_date) it will fix it:
SELECT b.loc, b.week, b.vvalue2, b.sum_ships, NVL (a.up_date, 'None') AS datee, NVL (a.ships_rel_total, 0) AS total_released
  FROM (  SELECT l.loc, sr1.vvalue1 AS week, sr.vvalue2
               , TO_CHAR ( MAX(ss.udate), 'YYYY-MM-DD') AS up_date
               , COUNT (DISTINCT s.ship_id) AS ships_rel_total
            FROM ship s, loct l, ship_num sr1, ship_num sr, ship_stat ss, ship_stat ss1
           WHERE 1 = 1
        GROUP BY l.loc, sr1.vvalue1, sr.vvalue2
) a
. . .   E t c   . . .

[Updated on: Thu, 18 April 2013 14:17] by Moderator

Report message to a moderator

Previous Topic: tuning package
Next Topic: cursor using subquery
Goto Forum:
  


Current Time: Wed Sep 17 02:52:05 CDT 2014

Total time taken to generate the page: 0.11157 seconds