SQL & PL/SQL :: Sum And GroupBy
Oct 25, 2012
I'm trying to a sum on a group by. However if you notice there are not data in year 1997 for id 20 and not data in year 1999 for id 21. I would like the sum to come back as empty since there are missing data within the group.
Currently:
GRP CNTY YR SUM
AGER1996150000
AGER1997130000 =====> Should be null
AGER1998170000
AGER199940000 =====> Should be null
AGER2000190000
AUSA1996150000
AUSA1997160000
AUSA1998170000
AUSA1999180000
AUSA2000190000
create table test (id integer, wage number, grp varchar2(5), cnty varchar2(5),yr integer);
[Code]....
View 6 Replies
Jun 25, 2013
I have a table with data as follows
Source name address city
File Y N N
File N N Y
DB Y N Y
DB N Y N
XML Y Y N
I am trying to get output as follows
Source Y/N CountName CountAddress
File Y 1 0
File N 1 2
DB Y 1 1
DB N 1 1
XML Y 1 1
XML N 0 0
View 2 Replies
View Related
Jul 8, 2011
i have a problem in the following query. i need to fetch the rows such that i want to fetch all the records keeping "segment1" column as distinct and sum all of the corresponding "quantities" column.
select prha.segment1 --as requisition_no
,prha.creation_date
,sum(prla.quantity)
,prha.description
[code]...
i tried to use the partition technique. using partition solved the problem apperently. the sum function worked but redundancy in "segment1" column still persists. i used the sum function only to extract the distinct "segment1" column and summing its corresponding "quantity" column (only quantity column differs in the redundant rows...)
the second query was like:
SELECT prha.segment1,
prha.creation_date,
SUM(prla.quantity) OVER(PARTITION BY prha.segment1) AS qty,
prha.DESCRIPTION,
[code]...
View 1 Replies
View Related
