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 '%purple%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 274352085.508407 ALGERIA | 1997 | 459721278.974593 ALGERIA | 1996 | 457710014.150579 ALGERIA | 1995 | 463973345.952557 ALGERIA | 1994 | 465644858.353111 ALGERIA | 1993 | 461118321.923997 ALGERIA | 1992 | 468942987.741361 ARGENTINA | 1998 | 273254024.406268 ARGENTINA | 1997 | 470407922.272888 ARGENTINA | 1996 | 473991973.640537 ARGENTINA | 1995 | 469273472.447011 ARGENTINA | 1994 | 464211105.143624 ARGENTINA | 1993 | 478524247.280347 ARGENTINA | 1992 | 474536923.550659 BRAZIL | 1998 | 273954159.442897 BRAZIL | 1997 | 470112410.898282 BRAZIL | 1996 | 466167990.704576 BRAZIL | 1995 | 468509016.337907 BRAZIL | 1994 | 472899170.177408 BRAZIL | 1993 | 466442939.759884 BRAZIL | 1992 | 468711537.144662 CANADA | 1998 | 279771046.491164 CANADA | 1997 | 475635784.947677 CANADA | 1996 | 481631713.76581 CANADA | 1995 | 472683007.444461 CANADA | 1994 | 476167246.073304 CANADA | 1993 | 472423981.481053 CANADA | 1992 | 476738974.189646 CHINA | 1998 | 274928220.046279 CHINA | 1997 | 461972956.390484 CHINA | 1996 | 474710615.241044 CHINA | 1995 | 472140262.267541 CHINA | 1994 | 463515563.507376 CHINA | 1993 | 472110622.342094 CHINA | 1992 | 471662801.961605 EGYPT | 1998 | 269574959.808644 EGYPT | 1997 | 459041569.373157 EGYPT | 1996 | 459032552.408537 EGYPT | 1995 | 464085705.46696 EGYPT | 1994 | 463950443.060717 EGYPT | 1993 | 461769851.930928 EGYPT | 1992 | 467062331.760185 ETHIOPIA | 1998 | 266070772.284198 ETHIOPIA | 1997 | 452904073.765644 ETHIOPIA | 1996 | 452943228.845235 ETHIOPIA | 1995 | 454480116.15937 ETHIOPIA | 1994 | 450088303.713797 ETHIOPIA | 1993 | 458303571.943442 ETHIOPIA | 1992 | 463450739.596717 FRANCE | 1998 | 265376579.236041 FRANCE | 1997 | 456331625.180853 FRANCE | 1996 | 460949190.609035 FRANCE | 1995 | 456715800.382975 FRANCE | 1994 | 453297520.970922 FRANCE | 1993 | 461136883.455455 FRANCE | 1992 | 457177411.789992 GERMANY | 1998 | 278165607.878121 GERMANY | 1997 | 484238049.834191 GERMANY | 1996 | 475224139.224234 GERMANY | 1995 | 476943164.306053 GERMANY | 1994 | 476953345.953008 GERMANY | 1993 | 471182501.470678 GERMANY | 1992 | 481479430.862544 INDIA | 1998 | 286794603.915753 INDIA | 1997 | 481137266.633478 INDIA | 1996 | 482509349.984164 INDIA | 1995 | 483620639.925719 INDIA | 1994 | 487456112.365216 INDIA | 1993 | 478546008.221183 INDIA | 1992 | 491917658.955743 INDONESIA | 1998 | 272998272.094495 INDONESIA | 1997 | 456876235.488325 INDONESIA | 1996 | 466105012.578328 INDONESIA | 1995 | 463514576.152139 INDONESIA | 1994 | 469182352.925782 INDONESIA | 1993 | 466537506.700154 INDONESIA | 1992 | 466235222.164923 IRAN | 1998 | 275010340.468976 IRAN | 1997 | 465017126.684328 IRAN | 1996 | 452579189.553363 IRAN | 1995 | 465769053.636485 IRAN | 1994 | 461729258.485253 IRAN | 1993 | 454841939.8771 IRAN | 1992 | 462513179.214549 IRAQ | 1998 | 281236299.749247 IRAQ | 1997 | 468566616.954368 IRAQ | 1996 | 476654668.763685 IRAQ | 1995 | 471980680.891708 IRAQ | 1994 | 466411492.055702 IRAQ | 1993 | 468611437.294703 IRAQ | 1992 | 477831600.793234 JAPAN | 1998 | 273923222.308047 JAPAN | 1997 | 462742989.895203 JAPAN | 1996 | 463150633.449372 JAPAN | 1995 | 465319210.093771 JAPAN | 1994 | 461598263.503984 JAPAN | 1993 | 464687263.373246 JAPAN | 1992 | 466990407.870181 JORDAN | 1998 | 273105281.180424 JORDAN | 1997 | 463008908.130546 JORDAN | 1996 | 468188216.0433 JORDAN | 1995 | 458678085.331071 JORDAN | 1994 | 466577351.732814 JORDAN | 1993 | 472461488.187671 JORDAN | 1992 | 467210357.52658 KENYA | 1998 | 279347343.283461 KENYA | 1997 | 474266507.373843 KENYA | 1996 | 473299265.132358 KENYA | 1995 | 477020545.512275 KENYA | 1994 | 471120767.186164 KENYA | 1993 | 476945784.845775 KENYA | 1992 | 478311637.102052 MOROCCO | 1998 | 277566448.358219 MOROCCO | 1997 | 477226174.39222 MOROCCO | 1996 | 480085770.216423 MOROCCO | 1995 | 483038963.043417 MOROCCO | 1994 | 482183828.930525 MOROCCO | 1993 | 474853509.066566 MOROCCO | 1992 | 479514879.150848 MOZAMBIQUE | 1998 | 262525425.262166 MOZAMBIQUE | 1997 | 454709015.993648 MOZAMBIQUE | 1996 | 460174934.936143 MOZAMBIQUE | 1995 | 458769724.926938 MOZAMBIQUE | 1994 | 452038895.627601 MOZAMBIQUE | 1993 | 459504872.392329 MOZAMBIQUE | 1992 | 453234582.719782 PERU | 1998 | 277870724.993356 PERU | 1997 | 455902589.513491 PERU | 1996 | 469235064.462753 PERU | 1995 | 459101737.177193 PERU | 1994 | 459789190.488943 PERU | 1993 | 462275852.097213 PERU | 1992 | 467596638.297321 ROMANIA | 1998 | 276308640.821634 ROMANIA | 1997 | 471385074.960376 ROMANIA | 1996 | 474555236.67367 ROMANIA | 1995 | 473765329.326735 ROMANIA | 1994 | 469298922.574162 ROMANIA | 1993 | 472608823.110652 ROMANIA | 1992 | 471910743.523431 RUSSIA | 1998 | 279714997.999235 RUSSIA | 1997 | 466467671.841017 RUSSIA | 1996 | 473070104.00242 RUSSIA | 1995 | 473271132.298136 RUSSIA | 1994 | 472462615.176358 RUSSIA | 1993 | 471668858.453924 RUSSIA | 1992 | 470201752.999795 SAUDI ARABIA | 1998 | 276363117.069494 SAUDI ARABIA | 1997 | 469897926.560506 SAUDI ARABIA | 1996 | 467589006.546144 SAUDI ARABIA | 1995 | 465188847.077181 SAUDI ARABIA | 1994 | 468766277.731865 SAUDI ARABIA | 1993 | 472762743.265181 SAUDI ARABIA | 1992 | 470618684.961897 UNITED KINGDOM | 1998 | 269076373.170267 UNITED KINGDOM | 1997 | 467617593.817575 UNITED KINGDOM | 1996 | 467462829.413909 UNITED KINGDOM | 1995 | 466589891.843535 UNITED KINGDOM | 1994 | 458821115.472996 UNITED KINGDOM | 1993 | 468008327.061554 UNITED KINGDOM | 1992 | 463369109.248345 UNITED STATES | 1998 | 273020404.60652 UNITED STATES | 1997 | 470726965.047716 UNITED STATES | 1996 | 474357113.962359 UNITED STATES | 1995 | 463339633.39889 UNITED STATES | 1994 | 476179576.150139 UNITED STATES | 1993 | 468791582.841186 UNITED STATES | 1992 | 469319631.654467 VIETNAM | 1998 | 279546330.663487 VIETNAM | 1997 | 467298347.148135 VIETNAM | 1996 | 471735784.282754 VIETNAM | 1995 | 469885410.236263 VIETNAM | 1994 | 473673170.274695 VIETNAM | 1993 | 476714346.372735 VIETNAM | 1992 | 474047373.103041 (175 rows) COMMIT; COMMIT