데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Database

DBMS

MySQL

PostgreSQL

Community

Admin

Oracle Q&A 41464 게시물 읽기

No. 41464

데이터 합계 처리 문의 드립니다.
작성자	김태경(cranes)	작성일	2017-09-29 14:08ⓒ 2017-09-29 14:15ⓜ	조회수	6,269

안녕하세요

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

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' , '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 '2017' , 'CX40',840 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' , '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' , 'CX40',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' , 'CX8E',1000 from dual union all

select '2014' , 'CX4C',1184 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' , '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' , 'CXAD',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' , 'CX6D',NULL from dual union all

select '2017' , 'CX94',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' , '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' , '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' , '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' , 'CX8E',2400 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' , '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' , 'CX8E',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' , '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',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' , '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' , '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' , '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',1000 from dual union all

select '2007' , 'CX47',500 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',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' , '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',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' , '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',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' , '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',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' , 'CX26',400 from dual union all

select '2016' , 'CX98',1200 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' , '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' , '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' , 'CX17',400 from dual union all

select '2015' , 'CX43',960 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' , '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' , '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',2304 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' , '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',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 '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' , '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' , '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 '2014' , 'CX8E',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',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',2715 from dual union all

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

select '2014' , 'CX8E',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',400 from dual union all

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

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

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

select '2014' , 'CX3A',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' , 'CXA9',400 from dual union all

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

) t

ORDER BY id

)

select * from t

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

도움 부탁드립니다

감사합니다.

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	5806
41466	안녕하세요... 고민 끝에 질문입니다. [6]	이호수	2017-10-11	6119
41465	gorup 질문 입니다. [1]	봉달이	2017-10-10	6024
41464	데이터 합계 처리 문의 드립니다. [4]	김태경	2017-09-29	6269
41463	redo log 관련 질문입니다	goblin	2017-09-26	5979
41462	프로시저 insert 할때 --- Cause: java.sql.SQLException: ORA-00001: 무결성 제약 조건(MELIMS.PK_DRAFT)에 위배됩니다 ORA-06512: 에러가 납니다.	김기훈	2017-09-22	6912
41461	global temp 응답없음 [1]	응답없음	2017-09-19	6068

작업시간: 0.016초, 이곳 서비스는
PostgreSQL v16.4로 자료를 관리합니다