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 '%turquoise%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 260302644.722204 ALGERIA | 1997 | 456351813.467148 ALGERIA | 1996 | 452828574.915422 ALGERIA | 1995 | 445891516.514915 ALGERIA | 1994 | 452811490.840097 ALGERIA | 1993 | 441766520.936311 ALGERIA | 1992 | 447672715.986302 ARGENTINA | 1998 | 266953955.526619 ARGENTINA | 1997 | 459063172.358568 ARGENTINA | 1996 | 469590967.014211 ARGENTINA | 1995 | 473876294.187299 ARGENTINA | 1994 | 467592313.225316 ARGENTINA | 1993 | 460530039.417427 ARGENTINA | 1992 | 464218537.084271 BRAZIL | 1998 | 266018813.4591 BRAZIL | 1997 | 464279183.396271 BRAZIL | 1996 | 465080546.790912 BRAZIL | 1995 | 459772396.490739 BRAZIL | 1994 | 460936078.015017 BRAZIL | 1993 | 455307870.301753 BRAZIL | 1992 | 466379079.258172 CANADA | 1998 | 278192480.722364 CANADA | 1997 | 481348432.586376 CANADA | 1996 | 485825485.474436 CANADA | 1995 | 480095811.438139 CANADA | 1994 | 484452309.744891 CANADA | 1993 | 486455121.846015 CANADA | 1992 | 488848881.775899 CHINA | 1998 | 269614335.712397 CHINA | 1997 | 443720758.348697 CHINA | 1996 | 450961861.644013 CHINA | 1995 | 448118909.540502 CHINA | 1994 | 444771757.652586 CHINA | 1993 | 454391861.166062 CHINA | 1992 | 450093297.998057 EGYPT | 1998 | 269128429.627193 EGYPT | 1997 | 465923269.107587 EGYPT | 1996 | 468736361.422677 EGYPT | 1995 | 461783365.823849 EGYPT | 1994 | 457998343.090171 EGYPT | 1993 | 469525897.428038 EGYPT | 1992 | 463899370.815884 ETHIOPIA | 1998 | 263828056.47162 ETHIOPIA | 1997 | 452204712.830246 ETHIOPIA | 1996 | 455888943.207996 ETHIOPIA | 1995 | 456066138.308619 ETHIOPIA | 1994 | 452871031.005496 ETHIOPIA | 1993 | 454318731.934557 ETHIOPIA | 1992 | 459881600.205517 FRANCE | 1998 | 267374744.429016 FRANCE | 1997 | 458705157.018916 FRANCE | 1996 | 463901818.571105 FRANCE | 1995 | 460284716.98888 FRANCE | 1994 | 456354061.37682 FRANCE | 1993 | 461685873.291633 FRANCE | 1992 | 456056482.363513 GERMANY | 1998 | 276545703.510189 GERMANY | 1997 | 473973859.736174 GERMANY | 1996 | 477984612.003923 GERMANY | 1995 | 475813195.258183 GERMANY | 1994 | 473040671.015185 GERMANY | 1993 | 471278306.617991 GERMANY | 1992 | 469216913.102941 INDIA | 1998 | 269820485.581805 INDIA | 1997 | 471025192.566348 INDIA | 1996 | 473128001.70518 INDIA | 1995 | 468293117.547208 INDIA | 1994 | 463713657.047364 INDIA | 1993 | 462860115.036726 INDIA | 1992 | 465177535.45286 INDONESIA | 1998 | 271123250.232082 INDONESIA | 1997 | 458280107.042223 INDONESIA | 1996 | 461587975.024856 INDONESIA | 1995 | 463224580.02979 INDONESIA | 1994 | 461063927.334521 INDONESIA | 1993 | 463540946.083386 INDONESIA | 1992 | 464771896.396668 IRAN | 1998 | 269846921.065072 IRAN | 1997 | 459637086.197997 IRAN | 1996 | 464266664.198514 IRAN | 1995 | 461467438.570637 IRAN | 1994 | 464418454.675145 IRAN | 1993 | 466405616.190564 IRAN | 1992 | 458406466.54139 IRAQ | 1998 | 280331240.587769 IRAQ | 1997 | 476044464.805399 IRAQ | 1996 | 476132626.752189 IRAQ | 1995 | 483177577.257016 IRAQ | 1994 | 483352350.547067 IRAQ | 1993 | 475851320.798409 IRAQ | 1992 | 479752664.052658 JAPAN | 1998 | 270597440.958619 JAPAN | 1997 | 463187693.59292 JAPAN | 1996 | 465899835.374171 JAPAN | 1995 | 464705371.005406 JAPAN | 1994 | 459624273.637571 JAPAN | 1993 | 453747637.647892 JAPAN | 1992 | 455418160.710903 JORDAN | 1998 | 267090851.153435 JORDAN | 1997 | 446573293.314614 JORDAN | 1996 | 452190176.215814 JORDAN | 1995 | 451637557.233833 JORDAN | 1994 | 456991978.982815 JORDAN | 1993 | 455641605.717609 JORDAN | 1992 | 456423421.452733 KENYA | 1998 | 272036886.815469 KENYA | 1997 | 471239945.088714 KENYA | 1996 | 469926114.514294 KENYA | 1995 | 463008942.367806 KENYA | 1994 | 471144942.048402 KENYA | 1993 | 470167724.770976 KENYA | 1992 | 466706407.361779 MOROCCO | 1998 | 269792377.931552 MOROCCO | 1997 | 466364364.591553 MOROCCO | 1996 | 471072729.611075 MOROCCO | 1995 | 466813030.445048 MOROCCO | 1994 | 463246733.186296 MOROCCO | 1993 | 458980062.673494 MOROCCO | 1992 | 470221767.317334 MOZAMBIQUE | 1998 | 276035785.219044 MOZAMBIQUE | 1997 | 462214597.304825 MOZAMBIQUE | 1996 | 461996201.672659 MOZAMBIQUE | 1995 | 464772237.253431 MOZAMBIQUE | 1994 | 465660357.768716 MOZAMBIQUE | 1993 | 467885611.31485 MOZAMBIQUE | 1992 | 457725849.291888 PERU | 1998 | 271978192.782424 PERU | 1997 | 461864748.550581 PERU | 1996 | 457116171.279266 PERU | 1995 | 463148942.931946 PERU | 1994 | 464694130.851318 PERU | 1993 | 466338129.432554 PERU | 1992 | 458536806.840157 ROMANIA | 1998 | 271497546.668893 ROMANIA | 1997 | 458544679.293938 ROMANIA | 1996 | 454681799.248514 ROMANIA | 1995 | 464776250.494146 ROMANIA | 1994 | 466539517.077375 ROMANIA | 1993 | 457140740.669457 ROMANIA | 1992 | 468488877.345068 RUSSIA | 1998 | 279451436.735239 RUSSIA | 1997 | 475287797.951134 RUSSIA | 1996 | 470701449.822335 RUSSIA | 1995 | 469680654.210968 RUSSIA | 1994 | 469274280.64683 RUSSIA | 1993 | 473146516.719567 RUSSIA | 1992 | 472572858.77434 SAUDI ARABIA | 1998 | 270911553.690937 SAUDI ARABIA | 1997 | 466590515.550861 SAUDI ARABIA | 1996 | 468578564.282843 SAUDI ARABIA | 1995 | 466668785.046523 SAUDI ARABIA | 1994 | 468542516.000401 SAUDI ARABIA | 1993 | 464821783.030281 SAUDI ARABIA | 1992 | 467345152.90325 UNITED KINGDOM | 1998 | 268454667.952233 UNITED KINGDOM | 1997 | 455036690.196126 UNITED KINGDOM | 1996 | 455585923.274185 UNITED KINGDOM | 1995 | 458333415.577151 UNITED KINGDOM | 1994 | 453093989.466817 UNITED KINGDOM | 1993 | 457859564.798585 UNITED KINGDOM | 1992 | 460748443.250464 UNITED STATES | 1998 | 281855672.737432 UNITED STATES | 1997 | 472681612.678352 UNITED STATES | 1996 | 481493730.277723 UNITED STATES | 1995 | 476225100.389513 UNITED STATES | 1994 | 472879040.76341 UNITED STATES | 1993 | 474736332.097107 UNITED STATES | 1992 | 488274768.362901 VIETNAM | 1998 | 270653978.106398 VIETNAM | 1997 | 463941793.119032 VIETNAM | 1996 | 475364902.069359 VIETNAM | 1995 | 466373096.354284 VIETNAM | 1994 | 466434329.528198 VIETNAM | 1993 | 472996634.052326 VIETNAM | 1992 | 464499005.905094 (175 rows) COMMIT; COMMIT