BEGIN; BEGIN select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%magenta%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 263629577.408869 ALGERIA | 1997 | 459480983.653894 ALGERIA | 1996 | 466963998.749703 ALGERIA | 1995 | 464295803.280116 ALGERIA | 1994 | 460516466.271643 ALGERIA | 1993 | 460290048.366792 ALGERIA | 1992 | 452764434.441457 ARGENTINA | 1998 | 276325276.262597 ARGENTINA | 1997 | 466966314.3654 ARGENTINA | 1996 | 478563404.142693 ARGENTINA | 1995 | 471862044.404112 ARGENTINA | 1994 | 468959341.28184 ARGENTINA | 1993 | 465118867.059877 ARGENTINA | 1992 | 470474899.027232 BRAZIL | 1998 | 271343159.535918 BRAZIL | 1997 | 467413560.959044 BRAZIL | 1996 | 462190727.333705 BRAZIL | 1995 | 464882555.676142 BRAZIL | 1994 | 464115138.687988 BRAZIL | 1993 | 466623385.127706 BRAZIL | 1992 | 464559342.688346 CANADA | 1998 | 275491648.007149 CANADA | 1997 | 475837717.20042 CANADA | 1996 | 475291589.109688 CANADA | 1995 | 480577542.226866 CANADA | 1994 | 479341869.60762 CANADA | 1993 | 471403559.863865 CANADA | 1992 | 472805066.227988 CHINA | 1998 | 274452860.580888 CHINA | 1997 | 462049394.44015 CHINA | 1996 | 466659062.167305 CHINA | 1995 | 454923861.530173 CHINA | 1994 | 457756239.823639 CHINA | 1993 | 463460514.524892 CHINA | 1992 | 467879285.710407 EGYPT | 1998 | 273128917.203123 EGYPT | 1997 | 476355331.959754 EGYPT | 1996 | 471639099.707631 EGYPT | 1995 | 461569227.591533 EGYPT | 1994 | 468969553.16738 EGYPT | 1993 | 461254775.274123 EGYPT | 1992 | 466528034.622439 ETHIOPIA | 1998 | 269243284.341213 ETHIOPIA | 1997 | 453036891.088277 ETHIOPIA | 1996 | 453329375.132355 ETHIOPIA | 1995 | 459936747.353289 ETHIOPIA | 1994 | 453753722.238675 ETHIOPIA | 1993 | 458817806.329526 ETHIOPIA | 1992 | 452278925.781341 FRANCE | 1998 | 271865744.259468 FRANCE | 1997 | 458533485.872994 FRANCE | 1996 | 463025921.347178 FRANCE | 1995 | 466598762.329366 FRANCE | 1994 | 462971089.37978 FRANCE | 1993 | 461963275.415343 FRANCE | 1992 | 452999268.599888 GERMANY | 1998 | 279552179.428008 GERMANY | 1997 | 481847628.635017 GERMANY | 1996 | 478461068.658473 GERMANY | 1995 | 475095618.622259 GERMANY | 1994 | 471983590.571681 GERMANY | 1993 | 481101130.401183 GERMANY | 1992 | 479112923.406294 INDIA | 1998 | 276636146.250761 INDIA | 1997 | 473948270.807681 INDIA | 1996 | 474730825.038447 INDIA | 1995 | 474926631.072462 INDIA | 1994 | 473486115.263737 INDIA | 1993 | 475973125.680372 INDIA | 1992 | 473359022.623748 INDONESIA | 1998 | 267621676.373599 INDONESIA | 1997 | 456629934.663154 INDONESIA | 1996 | 464784634.878228 INDONESIA | 1995 | 459177206.395993 INDONESIA | 1994 | 450425114.725053 INDONESIA | 1993 | 460055146.84237 INDONESIA | 1992 | 459993467.529453 IRAN | 1998 | 266966450.803454 IRAN | 1997 | 462879048.50889 IRAN | 1996 | 465570727.642201 IRAN | 1995 | 458375881.237067 IRAN | 1994 | 467910896.411108 IRAN | 1993 | 468534313.636002 IRAN | 1992 | 455917349.811721 IRAQ | 1998 | 271514963.365874 IRAQ | 1997 | 463033545.445284 IRAQ | 1996 | 460711315.57059 IRAQ | 1995 | 468404939.786085 IRAQ | 1994 | 468030367.601387 IRAQ | 1993 | 466136766.881302 IRAQ | 1992 | 477037611.691574 JAPAN | 1998 | 274150854.737825 JAPAN | 1997 | 469188976.14149 JAPAN | 1996 | 463592423.48999 JAPAN | 1995 | 465970482.346119 JAPAN | 1994 | 473641995.56104 JAPAN | 1993 | 463855115.116196 JAPAN | 1992 | 465051328.62473 JORDAN | 1998 | 265344727.682077 JORDAN | 1997 | 451652730.418144 JORDAN | 1996 | 450001287.312761 JORDAN | 1995 | 454614801.830705 JORDAN | 1994 | 440489928.563252 JORDAN | 1993 | 458653520.348453 JORDAN | 1992 | 446815539.821738 KENYA | 1998 | 273132313.642482 KENYA | 1997 | 463400775.680968 KENYA | 1996 | 466403269.580148 KENYA | 1995 | 463405761.729861 KENYA | 1994 | 465582736.042963 KENYA | 1993 | 465195054.756949 KENYA | 1992 | 467062993.325228 MOROCCO | 1998 | 269264201.083434 MOROCCO | 1997 | 465320320.479634 MOROCCO | 1996 | 472919176.141325 MOROCCO | 1995 | 466206361.098086 MOROCCO | 1994 | 470815974.112683 MOROCCO | 1993 | 465103989.539494 MOROCCO | 1992 | 475890132.29765 MOZAMBIQUE | 1998 | 266356253.144955 MOZAMBIQUE | 1997 | 464629541.863138 MOZAMBIQUE | 1996 | 455823132.939067 MOZAMBIQUE | 1995 | 456657122.394291 MOZAMBIQUE | 1994 | 459576893.654494 MOZAMBIQUE | 1993 | 459109506.001823 MOZAMBIQUE | 1992 | 459411255.484278 PERU | 1998 | 271057141.997704 PERU | 1997 | 465801808.619022 PERU | 1996 | 455682922.416604 PERU | 1995 | 460429748.711306 PERU | 1994 | 460570387.145093 PERU | 1993 | 467949950.829857 PERU | 1992 | 465977576.654155 ROMANIA | 1998 | 269629332.341235 ROMANIA | 1997 | 468113877.121996 ROMANIA | 1996 | 475400528.077157 ROMANIA | 1995 | 463211276.013619 ROMANIA | 1994 | 470895253.127028 ROMANIA | 1993 | 465793649.038826 ROMANIA | 1992 | 479623697.494943 RUSSIA | 1998 | 278708525.45171 RUSSIA | 1997 | 469498757.147163 RUSSIA | 1996 | 472317026.449 RUSSIA | 1995 | 461285740.198576 RUSSIA | 1994 | 470759382.4008 RUSSIA | 1993 | 467575956.519611 RUSSIA | 1992 | 474072454.527175 SAUDI ARABIA | 1998 | 271957795.778068 SAUDI ARABIA | 1997 | 470557263.520988 SAUDI ARABIA | 1996 | 470855988.724892 SAUDI ARABIA | 1995 | 475883005.678234 SAUDI ARABIA | 1994 | 463271962.587573 SAUDI ARABIA | 1993 | 471750984.016962 SAUDI ARABIA | 1992 | 472814703.063572 UNITED KINGDOM | 1998 | 264242401.921536 UNITED KINGDOM | 1997 | 463879930.283113 UNITED KINGDOM | 1996 | 454924294.089576 UNITED KINGDOM | 1995 | 453323114.067097 UNITED KINGDOM | 1994 | 453491381.930814 UNITED KINGDOM | 1993 | 464403206.326708 UNITED KINGDOM | 1992 | 462307802.433765 UNITED STATES | 1998 | 276894245.016812 UNITED STATES | 1997 | 473256075.48957 UNITED STATES | 1996 | 472311250.320763 UNITED STATES | 1995 | 471712221.766131 UNITED STATES | 1994 | 469115612.462382 UNITED STATES | 1993 | 471009786.85168 UNITED STATES | 1992 | 473466124.81413 VIETNAM | 1998 | 275358107.225901 VIETNAM | 1997 | 464024255.434521 VIETNAM | 1996 | 466297059.228234 VIETNAM | 1995 | 467424811.960784 VIETNAM | 1994 | 465994458.781455 VIETNAM | 1993 | 469724739.245873 VIETNAM | 1992 | 469089036.162071 (175 rows) COMMIT; COMMIT