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 '%metallic%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 269409888.626409 ALGERIA | 1997 | 462565795.095134 ALGERIA | 1996 | 456568877.838088 ALGERIA | 1995 | 462821718.986613 ALGERIA | 1994 | 459490621.672022 ALGERIA | 1993 | 460726333.554188 ALGERIA | 1992 | 454153619.412796 ARGENTINA | 1998 | 275103086.991377 ARGENTINA | 1997 | 469525011.570261 ARGENTINA | 1996 | 464927308.356822 ARGENTINA | 1995 | 463630456.688002 ARGENTINA | 1994 | 467564145.849252 ARGENTINA | 1993 | 469515383.706828 ARGENTINA | 1992 | 458382862.443122 BRAZIL | 1998 | 270744690.331272 BRAZIL | 1997 | 456881266.825822 BRAZIL | 1996 | 463229600.138242 BRAZIL | 1995 | 455842869.613803 BRAZIL | 1994 | 461023209.454052 BRAZIL | 1993 | 465100865.890032 BRAZIL | 1992 | 470035904.908306 CANADA | 1998 | 278887289.540967 CANADA | 1997 | 471174851.377946 CANADA | 1996 | 477743207.125723 CANADA | 1995 | 474121395.338534 CANADA | 1994 | 478906351.814628 CANADA | 1993 | 464119526.808852 CANADA | 1992 | 466421288.658772 CHINA | 1998 | 270547046.194058 CHINA | 1997 | 464195764.565619 CHINA | 1996 | 465991360.693893 CHINA | 1995 | 456759903.583874 CHINA | 1994 | 453570417.072613 CHINA | 1993 | 459230524.68445 CHINA | 1992 | 464456637.120819 EGYPT | 1998 | 277782884.738199 EGYPT | 1997 | 473529412.909501 EGYPT | 1996 | 471812643.912756 EGYPT | 1995 | 471823904.44178 EGYPT | 1994 | 478766592.013876 EGYPT | 1993 | 469887421.241536 EGYPT | 1992 | 475888928.452379 ETHIOPIA | 1998 | 262788157.793152 ETHIOPIA | 1997 | 464747890.67578 ETHIOPIA | 1996 | 456504320.607486 ETHIOPIA | 1995 | 448245749.568278 ETHIOPIA | 1994 | 453634246.261041 ETHIOPIA | 1993 | 455671679.949159 ETHIOPIA | 1992 | 455231073.446533 FRANCE | 1998 | 267592588.499469 FRANCE | 1997 | 463956631.536589 FRANCE | 1996 | 471646884.479161 FRANCE | 1995 | 462873617.431013 FRANCE | 1994 | 464876725.58963 FRANCE | 1993 | 459621308.677265 FRANCE | 1992 | 466892753.203535 GERMANY | 1998 | 277679792.463373 GERMANY | 1997 | 472663646.640315 GERMANY | 1996 | 475789895.299945 GERMANY | 1995 | 473717031.366979 GERMANY | 1994 | 473478057.215014 GERMANY | 1993 | 473997108.591974 GERMANY | 1992 | 473200619.69665 INDIA | 1998 | 274460177.134039 INDIA | 1997 | 471964330.277674 INDIA | 1996 | 471593487.24745 INDIA | 1995 | 459157011.026788 INDIA | 1994 | 464839092.709331 INDIA | 1993 | 474329100.129447 INDIA | 1992 | 473892589.110095 INDONESIA | 1998 | 267732596.639688 INDONESIA | 1997 | 456623625.626046 INDONESIA | 1996 | 458857698.673258 INDONESIA | 1995 | 451850382.504353 INDONESIA | 1994 | 464760288.423014 INDONESIA | 1993 | 456201261.938288 INDONESIA | 1992 | 457341056.896274 IRAN | 1998 | 272387395.223914 IRAN | 1997 | 465681801.009809 IRAN | 1996 | 472027379.442196 IRAN | 1995 | 467486244.09747 IRAN | 1994 | 465446843.610843 IRAN | 1993 | 464818243.356751 IRAN | 1992 | 463418034.703676 IRAQ | 1998 | 271591390.960544 IRAQ | 1997 | 467064974.975689 IRAQ | 1996 | 469339533.215112 IRAQ | 1995 | 471297355.277167 IRAQ | 1994 | 472134400.697213 IRAQ | 1993 | 471965328.187562 IRAQ | 1992 | 472917455.130093 JAPAN | 1998 | 271655304.428561 JAPAN | 1997 | 464483006.64381 JAPAN | 1996 | 463632743.104143 JAPAN | 1995 | 464330170.655441 JAPAN | 1994 | 459021714.506739 JAPAN | 1993 | 465799319.784354 JAPAN | 1992 | 461862942.698853 JORDAN | 1998 | 269475135.745301 JORDAN | 1997 | 455436654.178421 JORDAN | 1996 | 456781784.761647 JORDAN | 1995 | 464563638.076226 JORDAN | 1994 | 457750111.149504 JORDAN | 1993 | 456445619.045086 JORDAN | 1992 | 458136965.238948 KENYA | 1998 | 274122272.535003 KENYA | 1997 | 469060717.985017 KENYA | 1996 | 466524647.440321 KENYA | 1995 | 468540733.679549 KENYA | 1994 | 466960173.868478 KENYA | 1993 | 468761291.224766 KENYA | 1992 | 473373577.846267 MOROCCO | 1998 | 274100022.412484 MOROCCO | 1997 | 464573187.485786 MOROCCO | 1996 | 460762634.805432 MOROCCO | 1995 | 461448087.070647 MOROCCO | 1994 | 466008368.847694 MOROCCO | 1993 | 460045769.409859 MOROCCO | 1992 | 459654599.201327 MOZAMBIQUE | 1998 | 271286190.175858 MOZAMBIQUE | 1997 | 467392683.130616 MOZAMBIQUE | 1996 | 468836459.147425 MOZAMBIQUE | 1995 | 471527338.345373 MOZAMBIQUE | 1994 | 462865214.456201 MOZAMBIQUE | 1993 | 464107086.971854 MOZAMBIQUE | 1992 | 465409911.452793 PERU | 1998 | 263567825.152212 PERU | 1997 | 457919054.87128 PERU | 1996 | 469449322.206001 PERU | 1995 | 462763272.762167 PERU | 1994 | 453121753.768554 PERU | 1993 | 459081955.377915 PERU | 1992 | 459076232.626968 ROMANIA | 1998 | 280468883.911623 ROMANIA | 1997 | 469870761.676991 ROMANIA | 1996 | 467099333.310965 ROMANIA | 1995 | 460317410.220729 ROMANIA | 1994 | 476050879.045101 ROMANIA | 1993 | 467924206.746641 ROMANIA | 1992 | 474299852.435697 RUSSIA | 1998 | 280881921.010174 RUSSIA | 1997 | 473058437.515002 RUSSIA | 1996 | 475797082.830151 RUSSIA | 1995 | 468791838.523763 RUSSIA | 1994 | 480032982.766484 RUSSIA | 1993 | 469471505.842808 RUSSIA | 1992 | 467609457.913397 SAUDI ARABIA | 1998 | 274317368.940799 SAUDI ARABIA | 1997 | 468390844.890931 SAUDI ARABIA | 1996 | 472271902.481707 SAUDI ARABIA | 1995 | 468464923.474206 SAUDI ARABIA | 1994 | 460880371.030408 SAUDI ARABIA | 1993 | 467104470.25726 SAUDI ARABIA | 1992 | 465986296.189929 UNITED KINGDOM | 1998 | 270633074.945241 UNITED KINGDOM | 1997 | 461388703.135759 UNITED KINGDOM | 1996 | 465889012.217455 UNITED KINGDOM | 1995 | 464114496.485913 UNITED KINGDOM | 1994 | 458720240.880448 UNITED KINGDOM | 1993 | 463194007.889561 UNITED KINGDOM | 1992 | 457916925.869982 UNITED STATES | 1998 | 265856631.678758 UNITED STATES | 1997 | 464984587.40034 UNITED STATES | 1996 | 465530128.539923 UNITED STATES | 1995 | 467420700.834557 UNITED STATES | 1994 | 471294057.549851 UNITED STATES | 1993 | 465905801.575343 UNITED STATES | 1992 | 461856014.736718 VIETNAM | 1998 | 269712363.858381 VIETNAM | 1997 | 458414134.852929 VIETNAM | 1996 | 465401003.273967 VIETNAM | 1995 | 459193870.911084 VIETNAM | 1994 | 468939404.176227 VIETNAM | 1993 | 469318687.115424 VIETNAM | 1992 | 461685679.472066 (175 rows) COMMIT; COMMIT