database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41464 게시물 읽기
No. 41464
데이터 합계 처리 문의 드립니다.
작성자
김태경(cranes)
작성일
2017-09-29 14:08ⓒ
2017-09-29 14:15ⓜ
조회수
5,183

 안녕하세요 

원하는 대로 데이타가 나오지 않아 고수님의 도움을 받고자 글을 남깁니다.

 

 

 

 

 

 

SELECT * 

  FROM 

       (

       select '2016' year , 'CXA2' id ,280 amount from dual union all 

select '2016' , 'CX23',280 from dual union all 

select '2016' , 'CX58',280 from dual union all 

select '2016' , 'CX40',280 from dual union all 

select '2016' , 'CX9D',280 from dual union all 

select '2016' , 'CX24',280 from dual union all 

select '2016' , 'CX60',280 from dual union all 

select '2016' , 'CX6F',280 from dual union all 

select '2016' , 'CX8E',280 from dual union all 

select '2016' , 'CX5B',400 from dual union all 

select '2016' , 'CX42',400 from dual union all 

select '2016' , 'CX48',400 from dual union all 

select '2016' , 'CX2B',400 from dual union all 

select '2016' , 'CX5C',400 from dual union all 

select '2016' , 'CX63',280 from dual union all 

select '2016' , 'CX94',280 from dual union all 

select '2016' , 'CX8E',280 from dual union all 

select '2016' , 'CX2B',400 from dual union all 

select '2016' , 'CX68',400 from dual union all 

select '2016' , 'CX40',280 from dual union all 

select '2016' , 'CX1',400 from dual union all 

select '2016' , 'CX26',280 from dual union all 

select '2016' , 'CX68',280 from dual union all 

select '2016' , 'CX68',280 from dual union all 

select '2016' , 'CX5C',280 from dual union all 

select '2016' , 'CX43',400 from dual union all 

select '2016' , 'CXA7',280 from dual union all 

select '2016' , 'CXA7',400 from dual union all 

select '2016' , 'CX98',400 from dual union all 

select '2016' , 'CX8E',280 from dual union all 

select '2016' , 'CX8E',280 from dual union all 

select '2017' , 'CX40',840 from dual union all 

select '2017' , 'CX44',280 from dual union all 

select '2017' , 'CX44',280 from dual union all 

select '2017' , 'CX63',280 from dual union all 

select '2017' , 'CX2B',400 from dual union all 

select '2017' , 'CX2B',280 from dual union all 

select '2017' , 'CX98',400 from dual union all 

select '2017' , 'CX8E',NULL from dual union all 

select '2017' , 'CX8E',NULL from dual union all 

select '2017' , 'CX98',NULL from dual union all 

select '2017' , 'CX98',NULL from dual union all 

select '2017' , 'CX91',NULL from dual union all 

select '2017' , 'CX9D',NULL from dual union all 

select '2017' , 'CX9C',NULL from dual union all 

select '2017' , 'CX44',NULL from dual union all 

select '2017' , 'CX44',NULL from dual union all 

select '2017' , 'CX40',NULL from dual union all 

select '2017' , 'CX26',NULL from dual union all 

select '2017' , 'CX26',NULL from dual union all 

select '2017' , 'CX86',NULL from dual union all 

select '2014' , 'CX40',2000 from dual union all 

select '2014' , 'CX40',400 from dual union all 

select '2014' , 'CX26',400 from dual union all 

select '2014' , 'CX2B',1360 from dual union all 

select '2014' , 'CX2B',400 from dual union all 

select '2014' , 'CXB2',400 from dual union all 

select '2014' , 'CXA4',400 from dual union all 

select '2014' , 'CX43',400 from dual union all 

select '2014' , 'CX43',400 from dual union all 

select '2014' , 'CX43',400 from dual union all 

select '2014' , 'CX43',400 from dual union all 

select '2014' , 'CX43',400 from dual union all 

select '2014' , 'CX43',400 from dual union all 

select '2014' , 'CX43',400 from dual union all 

select '2014' , 'CX43',400 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX8E',1000 from dual union all 

select '2014' , 'CX4C',1184 from dual union all 

select '2014' , 'CX4C',1184 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CXAC',400 from dual union all 

select '2014' , 'CX26',2400 from dual union all 

select '2014' , 'CX85',400 from dual union all 

select '2014' , 'CX8E',1536 from dual union all 

select '2014' , 'CX8E',400 from dual union all 

select '2014' , 'CX8F',400 from dual union all 

select '2014' , 'CX2B',400 from dual union all 

select '2014' , 'CX4C',400 from dual union all 

select '2014' , 'CX2B',1360 from dual union all 

select '2014' , 'CX34',800 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CXAD',400 from dual union all 

select '2014' , 'CXAD',400 from dual union all 

select '2014' , 'CX85',1360 from dual union all 

select '2014' , 'CX81',400 from dual union all 

select '2014' , 'CX8E',400 from dual union all 

select '2014' , 'CX6F',400 from dual union all 

select '2014' , 'CX66',400 from dual union all 

select '2014' , 'CX83',1160 from dual union all 

select '2017' , 'CX2B',NULL from dual union all 

select '2017' , 'CX68',NULL from dual union all 

select '2017' , 'CX26',NULL from dual union all 

select '2017' , 'CX6',NULL from dual union all 

select '2017' , 'CX3F',NULL from dual union all 

select '2017' , 'CX3F',NULL from dual union all 

select '2017' , 'CX3F',NULL from dual union all 

select '2017' , 'CX3F',NULL from dual union all 

select '2017' , 'CX3F',NULL from dual union all 

select '2017' , 'CX3F',NULL from dual union all 

select '2017' , 'CX3F',NULL from dual union all 

select '2017' , 'CX3F',NULL from dual union all 

select '2017' , 'CXAD',NULL from dual union all 

select '2017' , 'CXAD',NULL from dual union all 

select '2017' , 'CX8E',NULL from dual union all 

select '2017' , 'CX8E',NULL from dual union all 

select '2017' , 'CX93',NULL from dual union all 

select '2017' , 'CX66',NULL from dual union all 

select '2017' , 'CX8E',NULL from dual union all 

select '2017' , 'CX8E',NULL from dual union all 

select '2017' , 'CX8E',NULL from dual union all 

select '2017' , 'CX8E',NULL from dual union all 

select '2017' , 'CX6D',NULL from dual union all 

select '2017' , 'CX94',NULL from dual union all 

select '2017' , 'CXAD',NULL from dual union all 

select '2017' , 'CXAD',NULL from dual union all 

select '2017' , 'CX3F',NULL from dual union all 

select '2015' , 'CX8E',400 from dual union all 

select '2015' , 'CX56',400 from dual union all 

select '2016' , 'CX3D',280 from dual union all 

select '2016' , 'CX68',280 from dual union all 

select '2007' , 'CX27',750 from dual union all 

select '2007' , 'CXA5',1500 from dual union all 

select '2007' , 'CX54',500 from dual union all 

select '2008' , 'CX3B',3000 from dual union all 

select '2008' , 'CX3B',3000 from dual union all 

select '2008' , 'CX51',3000 from dual union all 

select '2008' , 'CX51',3000 from dual union all 

select '2008' , 'CX51',3000 from dual union all 

select '2008' , 'CX31',1000 from dual union all 

select '2008' , 'CX4A',1000 from dual union all 

select '2008' , 'CX4B',500 from dual union all 

select '2008' , 'CX51',1000 from dual union all 

select '2008' , 'CX3B',500 from dual union all 

select '2008' , 'CX95',500 from dual union all 

select '2009' , 'CX89',3000 from dual union all 

select '2009' , 'CX7',2000 from dual union all 

select '2009' , 'CX39',500 from dual union all 

select '2009' , 'CXB',500 from dual union all 

select '2009' , 'CXA',3000 from dual union all 

select '2009' , 'CXF',1000 from dual union all 

select '2009' , 'CXC',500 from dual union all 

select '2009' , 'CX14',500 from dual union all 

select '2009' , 'CX10',500 from dual union all 

select '2010' , 'CX28',1800 from dual union all 

select '2010' , 'CX1A',2400 from dual union all 

select '2010' , 'CX3A',2000 from dual union all 

select '2010' , 'CX3A',2400 from dual union all 

select '2010' , 'CX7F',400 from dual union all 

select '2010' , 'CX33',1200 from dual union all 

select '2010' , 'CXA0',1600 from dual union all 

select '2010' , 'CX39',1800 from dual union all 

select '2010' , 'CX9A',1800 from dual union all 

select '2010' , 'CX54',1600 from dual union all 

select '2010' , 'CX3A',500 from dual union all 

select '2010' , 'CX28',500 from dual union all 

select '2010' , 'CX51',2000 from dual union all 

select '2010' , 'CX51',2000 from dual union all 

select '2010' , 'CXA4',1000 from dual union all 

select '2010' , 'CX4E',1000 from dual union all 

select '2010' , 'CX1A',500 from dual union all 

select '2010' , 'CX51',500 from dual union all 

select '2010' , 'CX5D',500 from dual union all 

select '2010' , 'CXA5',500 from dual union all 

select '2010' , 'CX1A',2000 from dual union all 

select '2010' , 'CX7B',500 from dual union all 

select '2010' , 'CX39',500 from dual union all 

select '2010' , 'CX65',1000 from dual union all 

select '2010' , 'CXA4',500 from dual union all 

select '2010' , 'CX68',500 from dual union all 

select '2010' , 'CX51',3000 from dual union all 

select '2010' , 'CX51',3000 from dual union all 

select '2010' , 'CX4E',2000 from dual union all 

select '2010' , 'CX6B',1250 from dual union all 

select '2010' , 'CX13',1750 from dual union all 

select '2010' , 'CXD',1500 from dual union all 

select '2010' , 'CX1F',1750 from dual union all 

select '2010' , 'CX22',2000 from dual union all 

select '2010' , 'CX1A',3000 from dual union all 

select '2010' , 'CX51',3000 from dual union all 

select '2010' , 'CX51',2000 from dual union all 

select '2010' , 'CX51',2750 from dual union all 

select '2010' , 'CX9',1250 from dual union all 

select '2010' , 'CX60',1250 from dual union all 

select '2010' , 'CX3A',2000 from dual union all 

select '2010' , 'CX96',1500 from dual union all 

select '2010' , 'CX9B',1500 from dual union all 

select '2010' , 'CX3A',2000 from dual union all 

select '2010' , 'CX16',1500 from dual union all 

select '2010' , 'CX15',1750 from dual union all 

select '2011' , 'CX36',1250 from dual union all 

select '2011' , 'CX37',1250 from dual union all 

select '2011' , 'CX5F',2000 from dual union all 

select '2011' , 'CX18',500 from dual union all 

select '2011' , 'CX33',2080 from dual union all 

select '2011' , 'CX1A',1600 from dual union all 

select '2011' , 'CX1A',2400 from dual union all 

select '2011' , 'CX8E',400 from dual union all 

select '2014' , 'CX40',2250 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX2F',1750 from dual union all 

select '2015' , 'CX38',400 from dual union all 

select '2015' , 'CX83',400 from dual union all 

select '2015' , 'CX34',1000 from dual union all 

select '2015' , 'CX66',800 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX8E',2400 from dual union all 

select '2015' , 'CX94',400 from dual union all 

select '2015' , 'CX94',400 from dual union all 

select '2011' , 'CX8E',400 from dual union all 

select '2011' , 'CX39',2320 from dual union all 

select '2011' , 'CX3A',1800 from dual union all 

select '2011' , 'CX3A',2160 from dual union all 

select '2011' , 'CX8E',400 from dual union all 

select '2011' , 'CX8E',1600 from dual union all 

select '2011' , 'CX8E',400 from dual union all 

select '2011' , 'CX33',2790 from dual union all 

select '2011' , 'CX1A',3000 from dual union all 

select '2011' , 'CX2A',400 from dual union all 

select '2011' , 'CX1A',3000 from dual union all 

select '2011' , 'CX8E',1760 from dual union all 

select '2011' , 'CX8E',1760 from dual union all 

select '2011' , 'CXAE',500 from dual union all 

select '2011' , 'CX8E',400 from dual union all 

select '2011' , 'CXA5',500 from dual union all 

select '2011' , 'CX97',500 from dual union all 

select '2011' , 'CX26',400 from dual union all 

select '2011' , 'CX40',400 from dual union all 

select '2012' , 'CXAD',400 from dual union all 

select '2012' , 'CX40',400 from dual union all 

select '2012' , 'CX50',500 from dual union all 

select '2012' , 'CX2F',500 from dual union all 

select '2012' , 'CX8E',2880 from dual union all 

select '2012' , 'CX8E',400 from dual union all 

select '2012' , 'CX8E',1600 from dual union all 

select '2012' , 'CX32',400 from dual union all 

select '2012' , 'CX97',400 from dual union all 

select '2012' , 'CX68',400 from dual union all 

select '2012' , 'CX43',400 from dual union all 

select '2012' , 'CX8E',400 from dual union all 

select '2012' , 'CX1A',3000 from dual union all 

select '2012' , 'CX6C',400 from dual union all 

select '2012' , 'CX8E',400 from dual union all 

select '2012' , 'CX8D',400 from dual union all 

select '2012' , 'CX8E',2240 from dual union all 

select '2012' , 'CX8E',400 from dual union all 

select '2012' , 'CX68',400 from dual union all 

select '2012' , 'CX97',600 from dual union all 

select '2012' , 'CX40',560 from dual union all 

select '2012' , 'CX51',1000 from dual union all 

select '2012' , 'CX51',1400 from dual union all 

select '2012' , 'CXA1',400 from dual union all 

select '2012' , 'CX3A',1600 from dual union all 

select '2012' , 'CX8E',2400 from dual union all 

select '2012' , 'CX3A',1000 from dual union all 

select '2012' , 'CX29',400 from dual union all 

select '2012' , 'CX51',1200 from dual union all 

select '2012' , 'CX8E',1600 from dual union all 

select '2012' , 'CX40',1160 from dual union all 

select '2012' , 'CX8E',400 from dual union all 

select '2012' , 'CX51',400 from dual union all 

select '2012' , 'CX8E',400 from dual union all 

select '2012' , 'CX8D',960 from dual union all 

select '2012' , 'CX51',400 from dual union all 

select '2012' , 'CX51',400 from dual union all 

select '2012' , 'CX8E',400 from dual union all 

select '2012' , 'CX6D',400 from dual union all 

select '2012' , 'CX6D',400 from dual union all 

select '2012' , 'CX8E',400 from dual union all 

select '2012' , 'CX68',1760 from dual union all 

select '2012' , 'CX8',1760 from dual union all 

select '2013' , 'CX43',500 from dual union all 

select '2013' , 'CX44',500 from dual union all 

select '2013' , 'CX1A',3000 from dual union all 

select '2013' , 'CX41',2280 from dual union all 

select '2013' , 'CX97',2300 from dual union all 

select '2013' , 'CX41',2280 from dual union all 

select '2013' , 'CX4C',400 from dual union all 

select '2013' , 'CX8E',1360 from dual union all 

select '2013' , 'CX8E',400 from dual union all 

select '2013' , 'CX8E',400 from dual union all 

select '2013' , 'CX51',2700 from dual union all 

select '2013' , 'CX39',1600 from dual union all 

select '2013' , 'CX3A',1800 from dual union all 

select '2013' , 'CX1A',3000 from dual union all 

select '2013' , 'CX3A',2240 from dual union all 

select '2013' , 'CX8E',1400 from dual union all 

select '2013' , 'CX6E',400 from dual union all 

select '2013' , 'CX51',1950 from dual union all 

select '2013' , 'CX8E',1000 from dual union all 

select '2013' , 'CX28',1600 from dual union all 

select '2013' , 'CX8E',1750 from dual union all 

select '2013' , 'CX51',2200 from dual union all 

select '2013' , 'CX6D',400 from dual union all 

select '2013' , 'CX8E',400 from dual union all 

select '2013' , 'CX34',400 from dual union all 

select '2013' , 'CX51',400 from dual union all 

select '2013' , 'CXA4',400 from dual union all 

select '2013' , 'CX8E',960 from dual union all 

select '2013' , 'CX51',400 from dual union all 

select '2013' , 'CX8E',960 from dual union all 

select '2013' , 'CX8E',960 from dual union all 

select '2013' , 'CX8E',800 from dual union all 

select '2013' , 'CX65',400 from dual union all 

select '2013' , 'CXA1',400 from dual union all 

select '2013' , 'CX4C',400 from dual union all 

select '2013' , 'CX8E',2200 from dual union all 

select '2013' , 'CX62',400 from dual union all 

select '2013' , 'CX64',400 from dual union all 

select '2013' , 'CX41',3000 from dual union all 

select '2013' , 'CX41',3000 from dual union all 

select '2013' , 'CX41',3000 from dual union all 

select '2013' , 'CX97',960 from dual union all 

select '2004' , 'CX4A',500 from dual union all 

select '2004' , 'CX92',250 from dual union all 

select '2004' , 'CX80',500 from dual union all 

select '2004' , 'CX8B',250 from dual union all 

select '2004' , 'CXA6',250 from dual union all 

select '2004' , 'CXAB',250 from dual union all 

select '2004' , 'CXB0',250 from dual union all 

select '2004' , 'CXB1',250 from dual union all 

select '2004' , 'CX88',250 from dual union all 

select '2004' , 'CX59',250 from dual union all 

select '2004' , 'CX9F',250 from dual union all 

select '2004' , 'CXAF',250 from dual union all 

select '2004' , 'CX3B',250 from dual union all 

select '2005' , 'CX1B',500 from dual union all 

select '2005' , 'CX28',500 from dual union all 

select '2005' , 'CX88',625 from dual union all 

select '2005' , 'CX51',750 from dual union all 

select '2005' , 'CX57',625 from dual union all 

select '2005' , 'CX55',500 from dual union all 

select '2005' , 'CX60',500 from dual union all 

select '2005' , 'CX67',500 from dual union all 

select '2005' , 'CX51',1000 from dual union all 

select '2005' , 'CX51',1000 from dual union all 

select '2005' , 'CX92',625 from dual union all 

select '2005' , 'CX92',875 from dual union all 

select '2005' , 'CX39',500 from dual union all 

select '2006' , 'CX45',500 from dual union all 

select '2006' , 'CX4A',1000 from dual union all 

select '2006' , 'CX51',750 from dual union all 

select '2006' , 'CXA4',500 from dual union all 

select '2006' , 'CX92',2000 from dual union all 

select '2006' , 'CX51',1500 from dual union all 

select '2006' , 'CX90',500 from dual union all 

select '2006' , 'CX82',625 from dual union all 

select '2006' , 'CXAA',625 from dual union all 

select '2006' , 'CX1C',500 from dual union all 

select '2006' , 'CX51',2500 from dual union all 

select '2006' , 'CX3B',1875 from dual union all 

select '2006' , 'CX31',1000 from dual union all 

select '2006' , 'CX3B',1250 from dual union all 

select '2006' , 'CX51',2500 from dual union all 

select '2006' , 'CX51',1000 from dual union all 

select '2006' , 'CX71',500 from dual union all 

select '2006' , 'CX2E',500 from dual union all 

select '2006' , 'CX33',500 from dual union all 

select '2006' , 'CX7E',1250 from dual union all 

select '2006' , 'CX27',500 from dual union all 

select '2006' , 'CX21',1000 from dual union all 

select '2006' , 'CXA5',1000 from dual union all 

select '2006' , 'CX20',750 from dual union all 

select '2006' , 'CXA5',500 from dual union all 

select '2006' , 'CX19',500 from dual union all 

select '2006' , 'CX5E',500 from dual union all 

select '2006' , 'CX7D',500 from dual union all 

select '2006' , 'CX7A',625 from dual union all 

select '2006' , 'CX59',1000 from dual union all 

select '2006' , 'CX22',1000 from dual union all 

select '2006' , 'CX4A',750 from dual union all 

select '2006' , 'CX77',500 from dual union all 

select '2006' , 'CX78',500 from dual union all 

select '2006' , 'CX3B',1000 from dual union all 

select '2006' , 'CX31',500 from dual union all 

select '2006' , 'CX51',1500 from dual union all 

select '2006' , 'CX3B',625 from dual union all 

select '2006' , 'CX51',500 from dual union all 

select '2006' , 'CX7D',1000 from dual union all 

select '2006' , 'CX72',500 from dual union all 

select '2006' , 'CX51',2000 from dual union all 

select '2006' , 'CX23',500 from dual union all 

select '2006' , 'CX39',500 from dual union all 

select '2006' , 'CX21',500 from dual union all 

select '2006' , 'CX33',500 from dual union all 

select '2006' , 'CXAD',500 from dual union all 

select '2006' , 'CX26',500 from dual union all 

select '2006' , 'CX2',500 from dual union all 

select '2007' , 'CX77',1250 from dual union all 

select '2007' , 'CX51',2500 from dual union all 

select '2007' , 'CX51',2000 from dual union all 

select '2007' , 'CX51',1500 from dual union all 

select '2007' , 'CX3B',1500 from dual union all 

select '2007' , 'CX3B',2500 from dual union all 

select '2007' , 'CX7D',750 from dual union all 

select '2007' , 'CX1C',1250 from dual union all 

select '2007' , 'CX2E',1000 from dual union all 

select '2007' , 'CX33',750 from dual union all 

select '2007' , 'CX72',1500 from dual union all 

select '2007' , 'CX21',1500 from dual union all 

select '2007' , 'CX20',1000 from dual union all 

select '2007' , 'CX3B',3000 from dual union all 

select '2007' , 'CX3B',2250 from dual union all 

select '2007' , 'CX3B',500 from dual union all 

select '2007' , 'CX3B',500 from dual union all 

select '2007' , 'CX51',3000 from dual union all 

select '2007' , 'CX51',3000 from dual union all 

select '2007' , 'CX51',2250 from dual union all 

select '2007' , 'CX57',1000 from dual union all 

select '2007' , 'CX5A',1500 from dual union all 

select '2007' , 'CX7D',2000 from dual union all 

select '2007' , 'CXA4',1000 from dual union all 

select '2007' , 'CX33',2000 from dual union all 

select '2007' , 'CX6C',500 from dual union all 

select '2007' , 'CX3B',3000 from dual union all 

select '2007' , 'CX3B',3000 from dual union all 

select '2007' , 'CX3B',3000 from dual union all 

select '2007' , 'CX3B',1000 from dual union all 

select '2007' , 'CX3B',1000 from dual union all 

select '2007' , 'CX47',500 from dual union all 

select '2007' , 'CX51',3000 from dual union all 

select '2007' , 'CX51',3000 from dual union all 

select '2007' , 'CX51',3000 from dual union all 

select '2007' , 'CX51',3000 from dual union all 

select '2007' , 'CX57',2000 from dual union all 

select '2007' , 'CX5A',2250 from dual union all 

select '2007' , 'CXA4',2000 from dual union all 

select '2007' , 'CX1A',500 from dual union all 

select '2007' , 'CX79',500 from dual union all 

select '2007' , 'CX7E',1875 from dual union all 

select '2007' , 'CX4E',500 from dual union all 

select '2015' , 'CX94',400 from dual union all 

select '2015' , 'CX3A',400 from dual union all 

select '2015' , 'CX3A',400 from dual union all 

select '2015' , 'CX3A',400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX3A',2400 from dual union all 

select '2015' , 'CX4D',400 from dual union all 

select '2015' , 'CX84',400 from dual union all 

select '2015' , 'CX1A',400 from dual union all 

select '2015' , 'CX1A',400 from dual union all 

select '2015' , 'CX93',400 from dual union all 

select '2015' , 'CX64',400 from dual union all 

select '2015' , 'CX40',2400 from dual union all 

select '2015' , 'CX40',2400 from dual union all 

select '2015' , 'CX40',960 from dual union all 

select '2015' , 'CX68',400 from dual union all 

select '2015' , 'CX4C',1274 from dual union all 

select '2015' , 'CX4',400 from dual union all 

select '2015' , 'CX5',400 from dual union all 

select '2015' , 'CX11',400 from dual union all 

select '2015' , 'CX4F',400 from dual union all 

select '2015' , 'CX4D',400 from dual union all 

select '2015' , 'CX56',400 from dual union all 

select '2015' , 'CX56',400 from dual union all 

select '2015' , 'CX26',1760 from dual union all 

select '2015' , 'CX26',1760 from dual union all 

select '2015' , 'CX26',1760 from dual union all 

select '2016' , 'CX2D',400 from dual union all 

select '2016' , 'CX3F',400 from dual union all 

select '2016' , 'CX99',400 from dual union all 

select '2016' , 'CX99',400 from dual union all 

select '2016' , 'CX99',2129 from dual union all 

select '2016' , 'CX52',400 from dual union all 

select '2016' , 'CX66',400 from dual union all 

select '2016' , 'CX68',960 from dual union all 

select '2016' , 'CX7C',400 from dual union all 

select '2016' , 'CX3',1000 from dual union all 

select '2016' , 'CX7B',1200 from dual union all 

select '2016' , 'CX35',600 from dual union all 

select '2016' , 'CX64',400 from dual union all 

select '2016' , 'CX64',400 from dual union all 

select '2016' , 'CX6D',400 from dual union all 

select '2016' , 'CX2D',400 from dual union all 

select '2016' , 'CX2C',720 from dual union all 

select '2016' , 'CX74',1000 from dual union all 

select '2016' , 'CX3C',600 from dual union all 

select '2016' , 'CX56',400 from dual union all 

select '2016' , 'CX1F',400 from dual union all 

select '2016' , 'CX99',960 from dual union all 

select '2016' , 'CX99',960 from dual union all 

select '2016' , 'CX99',2134 from dual union all 

select '2016' , 'CX2D',400 from dual union all 

select '2016' , 'CX30',200 from dual union all 

select '2016' , 'CX69',400 from dual union all 

select '2016' , 'CX3F',400 from dual union all 

select '2016' , 'CX4F',400 from dual union all 

select '2016' , 'CX3F',800 from dual union all 

select '2016' , 'CX3F',400 from dual union all 

select '2016' , 'CX99',960 from dual union all 

select '2016' , 'CX8E',400 from dual union all 

select '2016' , 'CX8E',400 from dual union all 

select '2016' , 'CX8E',400 from dual union all 

select '2016' , 'CX8E',400 from dual union all 

select '2016' , 'CX8E',400 from dual union all 

select '2016' , 'CX26',400 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',1200 from dual union all 

select '2016' , 'CX98',400 from dual union all 

select '2016' , 'CX98',400 from dual union all 

select '2016' , 'CX98',400 from dual union all 

select '2016' , 'CX98',400 from dual union all 

select '2016' , 'CX98',400 from dual union all 

select '2016' , 'CX68',960 from dual union all 

select '2016' , 'CX68',1200 from dual union all 

select '2017' , 'CX46',NULL from dual union all 

select '2017' , 'CX99',NULL from dual union all 

select '2017' , 'CX99',NULL from dual union all 

select '2017' , 'CX26',NULL from dual union all 

select '2017' , 'CX6D',NULL from dual union all 

select '2017' , 'CX7C',NULL from dual union all 

select '2017' , 'CX6A',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX64',NULL from dual union all 

select '2017' , 'CX26',NULL from dual union all 

select '2017' , 'CX2D',NULL from dual union all 

select '2017' , 'CX5C',NULL from dual union all 

select '2017' , 'CX7B',NULL from dual union all 

select '2015' , 'CX40',2400 from dual union all 

select '2015' , 'CX40',1984 from dual union all 

select '2015' , 'CXAD',960 from dual union all 

select '2015' , 'CX1E',400 from dual union all 

select '2015' , 'CX7C',400 from dual union all 

select '2015' , 'CX7C',400 from dual union all 

select '2015' , 'CX17',400 from dual union all 

select '2015' , 'CX43',960 from dual union all 

select '2015' , 'CX43',960 from dual union all 

select '2015' , 'CXE',400 from dual union all 

select '2015' , 'CXE',400 from dual union all 

select '2015' , 'CX2C',400 from dual union all 

select '2015' , 'CX2C',800 from dual union all 

select '2015' , 'CX94',800 from dual union all 

select '2015' , 'CX88',400 from dual union all 

select '2015' , 'CX17',800 from dual union all 

select '2015' , 'CX3E',500 from dual union all 

select '2015' , 'CX3E',500 from dual union all 

select '2015' , 'CX25',500 from dual union all 

select '2015' , 'CX25',1500 from dual union all 

select '2015' , 'CX5',400 from dual union all 

select '2015' , 'CX49',400 from dual union all 

select '2015' , 'CXAD',1384 from dual union all 

select '2015' , 'CX8E',1360 from dual union all 

select '2015' , 'CX8E',400 from dual union all 

select '2015' , 'CX8E',400 from dual union all 

select '2015' , 'CXAC',1400 from dual union all 

select '2015' , 'CXAD',1354 from dual union all 

select '2015' , 'CX26',1904 from dual union all 

select '2015' , 'CX26',1904 from dual union all 

select '2015' , 'CX26',2304 from dual union all 

select '2015' , 'CX26',1104 from dual union all 

select '2015' , 'CX26',1104 from dual union all 

select '2015' , 'CX26',1104 from dual union all 

select '2015' , 'CX26',1104 from dual union all 

select '2015' , 'CX26',1104 from dual union all 

select '2015' , 'CX26',1104 from dual union all 

select '2015' , 'CX26',1104 from dual union all 

select '2015' , 'CX26',1104 from dual union all 

select '2015' , 'CX9E',800 from dual union all 

select '2015' , 'CX40',2160 from dual union all 

select '2015' , 'CX40',1960 from dual union all 

select '2015' , 'CX8E',500 from dual union all 

select '2015' , 'CX8E',500 from dual union all 

select '2015' , 'CX8E',500 from dual union all 

select '2015' , 'CXA4',400 from dual union all 

select '2015' , 'CXA4',400 from dual union all 

select '2015' , 'CX8C',400 from dual union all 

select '2015' , 'CX4C',960 from dual union all 

select '2015' , 'CX4C',960 from dual union all 

select '2015' , 'CX4C',1310 from dual union all 

select '2015' , 'CX4C',960 from dual union all 

select '2015' , 'CX68',400 from dual union all 

select '2015' , 'CX34',1400 from dual union all 

select '2015' , 'CX23',400 from dual union all 

select '2015' , 'CX23',400 from dual union all 

select '2016' , 'CX1E',400 from dual union all 

select '2016' , 'CX83',800 from dual union all 

select '2016' , 'CX99',1323 from dual union all 

select '2016' , 'CX62',400 from dual union all 

select '2016' , 'CX1F',400 from dual union all 

select '2016' , 'CX8A',600 from dual union all 

select '2016' , 'CXA8',400 from dual union all 

select '2016' , 'CX8E',400 from dual union all 

select '2016' , 'CX62',600 from dual union all 

select '2016' , 'CX62',600 from dual union all 

select '2016' , 'CX46',400 from dual union all 

select '2016' , 'CX40',1193 from dual union all 

select '2016' , 'CX63',1120 from dual union all 

select '2016' , 'CX70',400 from dual union all 

select '2016' , 'CX6D',400 from dual union all 

select '2016' , 'CXAD',400 from dual union all 

select '2016' , 'CX4',400 from dual union all 

select '2016' , 'CXA2',400 from dual union all 

select '2016' , 'CX99',400 from dual union all 

select '2016' , 'CX73',600 from dual union all 

select '2016' , 'CX76',800 from dual union all 

select '2016' , 'CX38',400 from dual union all 

select '2016' , 'CX40',1277 from dual union all 

select '2016' , 'CX61',1000 from dual union all 

select '2016' , 'CX1D',400 from dual union all 

select '2016' , 'CX1D',400 from dual union all 

select '2016' , 'CX53',1000 from dual union all 

select '2016' , 'CX6A',1200 from dual union all 

select '2016' , 'CX75',400 from dual union all 

select '2016' , 'CX75',1000 from dual union all 

select '2016' , 'CX62',400 from dual union all 

select '2016' , 'CX2C',720 from dual union all 

select '2013' , 'CX51',2000 from dual union all 

select '2013' , 'CX8E',2000 from dual union all 

select '2013' , 'CX51',2480 from dual union all 

select '2013' , 'CX51',2975 from dual union all 

select '2013' , 'CX8E',2400 from dual union all 

select '2013' , 'CX3A',2900 from dual union all 

select '2013' , 'CX22',2250 from dual union all 

select '2013' , 'CX3A',3000 from dual union all 

select '2013' , 'CX51',3000 from dual union all 

select '2013' , 'CX8E',1200 from dual union all 

select '2013' , 'CX1A',3000 from dual union all 

select '2013' , 'CX8E',1840 from dual union all 

select '2013' , 'CX8E',2875 from dual union all 

select '2013' , 'CX41',2000 from dual union all 

select '2013' , 'CX1A',3000 from dual union all 

select '2013' , 'CX51',2738 from dual union all 

select '2013' , 'CX8E',3000 from dual union all 

select '2013' , 'CX3A',3000 from dual union all 

select '2013' , 'CX26',400 from dual union all 

select '2013' , 'CX6D',3000 from dual union all 

select '2013' , 'CX8E',3000 from dual union all 

select '2013' , 'CX26',2304 from dual union all 

select '2013' , 'CX12',500 from dual union all 

select '2013' , 'CX22',1250 from dual union all 

select '2013' , 'CX62',500 from dual union all 

select '2013' , 'CX62',500 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX1A',3000 from dual union all 

select '2014' , 'CX8E',1480 from dual union all 

select '2014' , 'CX2B',400 from dual union all 

select '2014' , 'CX26',960 from dual union all 

select '2014' , 'CXAD',400 from dual union all 

select '2014' , 'CX8E',500 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',2750 from dual union all 

select '2014' , 'CX8E',2430 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',500 from dual union all 

select '2014' , 'CX8E',500 from dual union all 

select '2014' , 'CX68',500 from dual union all 

select '2014' , 'CX68',400 from dual union all 

select '2014' , 'CX8E',1600 from dual union all 

select '2014' , 'CX8E',1600 from dual union all 

select '2014' , 'CX8E',1600 from dual union all 

select '2014' , 'CX8E',1600 from dual union all 

select '2014' , 'CX8E',1600 from dual union all 

select '2014' , 'CX8E',1600 from dual union all 

select '2014' , 'CX8E',2715 from dual union all 

select '2014' , 'CX8E',2965 from dual union all 

select '2014' , 'CX8E',2965 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX8E',400 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',400 from dual union all 

select '2014' , 'CX8E',400 from dual union all 

select '2014' , 'CX8E',1600 from dual union all 

select '2014' , 'CX8E',400 from dual union all 

select '2014' , 'CX8E',400 from dual union all 

select '2014' , 'CX8E',400 from dual union all 

select '2014' , 'CX8E',400 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX8E',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX3A',3000 from dual union all 

select '2014' , 'CX1A',3000 from dual union all 

select '2014' , 'CX1A',3000 from dual union all 

select '2014' , 'CX1A',3000 from dual union all 

select '2014' , 'CX83',400 from dual union all 

select '2014' , 'CX87',500 from dual union all 

select '2014' , 'CX2B',400 from dual union all 

select '2014' , 'CX2B',400 from dual union all 

select '2014' , 'CXA9',400 from dual union all 

select '2014' , 'CXA3',400 from dual

        ) t 

        

        ORDER BY id 

        )

 select * from t        

 

 

위의 데이타를 이용해   밑의 표와 같이 나오게 하고 싶은데  rollup에 pivot 을 해도 데이타가 나오질 않습니다.

도움 부탁드립니다 

 

감사합니다.

 

 

id

2010

2011

2012

2013

2014

2015

2016

 2017

합계

 

개수

합계

개수

합계

개수

합계

개수

합계

개수

합계

개수

합계

개수

합계

개수

합계

개수

합계

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

합계

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

이 글에 대한 댓글이 총 4건 있습니다.

SELECT NVL(id, '합계') id
     , COUNT(DECODE(year, '2010',      1)) cnt_2010
     , SUM  (DECODE(year, '2010', amount)) amt_2010
     , COUNT(DECODE(year, '2011',      1)) cnt_2011
     , SUM  (DECODE(year, '2011', amount)) amt_2011
     , COUNT(DECODE(year, '2012',      1)) cnt_2012
     , SUM  (DECODE(year, '2012', amount)) amt_2012
     , COUNT(DECODE(year, '2013',      1)) cnt_2013
     , SUM  (DECODE(year, '2013', amount)) amt_2013
     , COUNT(DECODE(year, '2014',      1)) cnt_2014
     , SUM  (DECODE(year, '2014', amount)) amt_2014
     , COUNT(DECODE(year, '2015',      1)) cnt_2015
     , SUM  (DECODE(year, '2015', amount)) amt_2015
     , COUNT(DECODE(year, '2016',      1)) cnt_2016
     , SUM  (DECODE(year, '2016', amount)) amt_2016
     , COUNT(DECODE(year, '2017',      1)) cnt_2017
     , SUM  (DECODE(year, '2017', amount)) amt_2017
     , COUNT(*)    cnt_tot
     , SUM(amount) amt_tot
  FROM t
 WHERE year BETWEEN '2010' AND '2017'
 GROUP BY ROLLUP(id)
 ORDER BY id
;

마농(manon94)님이 2017-09-29 15:54에 작성한 댓글입니다.
이 댓글은 2017-10-12 17:09에 마지막으로 수정되었습니다.

감사합니다. 

역시 마농님 이시네요

추석지나고  잠시 잊고 있다가  이제서야 글 남깁니다.

pivot 으로는 불가능한가 보네요 

 

 

너무 감사합니다.

 

 

김태경(cranes)님이 2017-10-12 15:39에 작성한 댓글입니다.

SELECT *
  FROM (SELECT NVL(year, 'TOT') year
             , NVL(id, '합계') id
             , COUNT(*) cnt
             , SUM(amount) amt
          FROM t
         WHERE year BETWEEN '2010' AND '2017'
         GROUP BY CUBE(year, id)
        )
 PIVOT ( SUM(cnt) cnt
       , SUM(amt) amt
         FOR year IN ( '2010' "2010"
                     , '2011' "2011"
                     , '2012' "2012"
                     , '2013' "2013"
                     , '2014' "2014"
                     , '2015' "2015"
                     , '2016' "2016"
                     , '2017' "2017"
                     , 'TOT'  "TOT"
                     ) )
 ORDER BY id
;

마농(manon94)님이 2017-10-12 17:19에 작성한 댓글입니다.

 우와 마농님 존경합니다.

대단하시네요.

 

무어라 형언할수 없네요

너무 감사합니다.

 

 

김태경(cranes)님이 2017-10-16 10:30에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41467정렬안의 정렬쿼리 [2]
gksalgml
2017-10-12
4860
41466안녕하세요... 고민 끝에 질문입니다. [6]
이호수
2017-10-11
4890
41465gorup 질문 입니다. [1]
봉달이
2017-10-10
4754
41464데이터 합계 처리 문의 드립니다. [4]
김태경
2017-09-29
5183
41463redo log 관련 질문입니다
goblin
2017-09-26
4789
41462프로시저 insert 할때 --- Cause: java.sql.SQLException: ORA-00001: 무결성 제약 조건(MELIMS.PK_DRAFT)에 위배됩니다 ORA-06512: 에러가 납니다.
김기훈
2017-09-22
5505
41461global temp 응답없음 [1]
응답없음
2017-09-19
4963
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.059초, 이곳 서비스는
	PostgreSQL v14.2로 자료를 관리합니다