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 '%seashell%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267762934.464585 ALGERIA | 1997 | 466278853.449983 ALGERIA | 1996 | 459067151.645781 ALGERIA | 1995 | 457982672.958116 ALGERIA | 1994 | 457979410.609789 ALGERIA | 1993 | 464716195.184445 ALGERIA | 1992 | 458398131.135072 ARGENTINA | 1998 | 279636910.752676 ARGENTINA | 1997 | 472279266.873231 ARGENTINA | 1996 | 461846424.053277 ARGENTINA | 1995 | 465037847.274863 ARGENTINA | 1994 | 470210019.762716 ARGENTINA | 1993 | 464468805.863501 ARGENTINA | 1992 | 466133352.716246 BRAZIL | 1998 | 274612798.153786 BRAZIL | 1997 | 476486101.725732 BRAZIL | 1996 | 471359280.98859 BRAZIL | 1995 | 465724238.560849 BRAZIL | 1994 | 470684213.402751 BRAZIL | 1993 | 477227247.238943 BRAZIL | 1992 | 473340171.598336 CANADA | 1998 | 271690946.02674 CANADA | 1997 | 475051069.229105 CANADA | 1996 | 473402370.143605 CANADA | 1995 | 475986531.127708 CANADA | 1994 | 482040249.790242 CANADA | 1993 | 463529965.815529 CANADA | 1992 | 470984959.834944 CHINA | 1998 | 271390986.283077 CHINA | 1997 | 475092725.238006 CHINA | 1996 | 471315861.024203 CHINA | 1995 | 466966822.694153 CHINA | 1994 | 465267637.371702 CHINA | 1993 | 476991129.097894 CHINA | 1992 | 473694899.57244 EGYPT | 1998 | 276197069.445194 EGYPT | 1997 | 472775345.759888 EGYPT | 1996 | 468382328.205276 EGYPT | 1995 | 467142555.33796 EGYPT | 1994 | 457047353.777935 EGYPT | 1993 | 473368876.237469 EGYPT | 1992 | 467754986.074262 ETHIOPIA | 1998 | 267502516.30317 ETHIOPIA | 1997 | 455409908.364249 ETHIOPIA | 1996 | 459649575.961241 ETHIOPIA | 1995 | 464281021.466862 ETHIOPIA | 1994 | 457712468.369859 ETHIOPIA | 1993 | 462644656.12562 ETHIOPIA | 1992 | 464399120.115716 FRANCE | 1998 | 269920496.407531 FRANCE | 1997 | 456965820.722812 FRANCE | 1996 | 460987509.655811 FRANCE | 1995 | 473116573.648194 FRANCE | 1994 | 460676332.647741 FRANCE | 1993 | 467196181.983721 FRANCE | 1992 | 468846110.245028 GERMANY | 1998 | 278966257.467424 GERMANY | 1997 | 479260323.880749 GERMANY | 1996 | 481191906.586172 GERMANY | 1995 | 481045078.90797 GERMANY | 1994 | 470207201.938553 GERMANY | 1993 | 481961952.631105 GERMANY | 1992 | 477437663.063017 INDIA | 1998 | 266537726.388462 INDIA | 1997 | 469685596.989682 INDIA | 1996 | 465229869.315233 INDIA | 1995 | 464053126.47357 INDIA | 1994 | 466961722.46996 INDIA | 1993 | 466971660.614625 INDIA | 1992 | 475026503.613192 INDONESIA | 1998 | 271154108.115977 INDONESIA | 1997 | 456134835.730471 INDONESIA | 1996 | 460133392.170586 INDONESIA | 1995 | 458335095.304789 INDONESIA | 1994 | 460875544.03664 INDONESIA | 1993 | 463441555.292179 INDONESIA | 1992 | 462395987.223055 IRAN | 1998 | 273115083.065992 IRAN | 1997 | 460885118.846195 IRAN | 1996 | 470420088.000957 IRAN | 1995 | 462440478.576614 IRAN | 1994 | 458542489.817245 IRAN | 1993 | 452815509.082962 IRAN | 1992 | 462018176.884296 IRAQ | 1998 | 275529940.727381 IRAQ | 1997 | 473816443.982956 IRAQ | 1996 | 476134888.520273 IRAQ | 1995 | 473072740.325605 IRAQ | 1994 | 478626188.262899 IRAQ | 1993 | 465352644.380274 IRAQ | 1992 | 480590094.348292 JAPAN | 1998 | 273109927.994321 JAPAN | 1997 | 467168577.578344 JAPAN | 1996 | 471360285.749683 JAPAN | 1995 | 475074410.984747 JAPAN | 1994 | 474584482.695648 JAPAN | 1993 | 477172658.767852 JAPAN | 1992 | 469580932.711391 JORDAN | 1998 | 274287233.234255 JORDAN | 1997 | 461055417.135432 JORDAN | 1996 | 459295176.733168 JORDAN | 1995 | 457859730.588525 JORDAN | 1994 | 464604385.540135 JORDAN | 1993 | 462428290.044536 JORDAN | 1992 | 456646865.878826 KENYA | 1998 | 280682130.828667 KENYA | 1997 | 477063977.508484 KENYA | 1996 | 472746391.105547 KENYA | 1995 | 472628517.881843 KENYA | 1994 | 467695991.519222 KENYA | 1993 | 476897693.336717 KENYA | 1992 | 481760514.105772 MOROCCO | 1998 | 279696733.107248 MOROCCO | 1997 | 479941691.610015 MOROCCO | 1996 | 474331353.323724 MOROCCO | 1995 | 484164010.300108 MOROCCO | 1994 | 482625108.283166 MOROCCO | 1993 | 478879078.610219 MOROCCO | 1992 | 475707438.505616 MOZAMBIQUE | 1998 | 273739159.990556 MOZAMBIQUE | 1997 | 462176425.937421 MOZAMBIQUE | 1996 | 466819244.994399 MOZAMBIQUE | 1995 | 459055353.255693 MOZAMBIQUE | 1994 | 460303953.048788 MOZAMBIQUE | 1993 | 466268187.515602 MOZAMBIQUE | 1992 | 458757782.914609 PERU | 1998 | 273888759.433583 PERU | 1997 | 471050612.359377 PERU | 1996 | 468079366.255481 PERU | 1995 | 458864161.87474 PERU | 1994 | 468960588.55895 PERU | 1993 | 462938201.255103 PERU | 1992 | 466851811.320207 ROMANIA | 1998 | 269729963.399758 ROMANIA | 1997 | 459632701.698902 ROMANIA | 1996 | 473584578.351289 ROMANIA | 1995 | 471460625.141148 ROMANIA | 1994 | 469420656.192486 ROMANIA | 1993 | 465104400.438786 ROMANIA | 1992 | 454724810.682969 RUSSIA | 1998 | 284337198.868611 RUSSIA | 1997 | 482653640.707695 RUSSIA | 1996 | 480625351.848546 RUSSIA | 1995 | 479891100.772263 RUSSIA | 1994 | 475662622.602011 RUSSIA | 1993 | 484542047.175357 RUSSIA | 1992 | 481923867.101875 SAUDI ARABIA | 1998 | 276443950.278872 SAUDI ARABIA | 1997 | 472201344.965411 SAUDI ARABIA | 1996 | 470664908.765351 SAUDI ARABIA | 1995 | 465889597.958389 SAUDI ARABIA | 1994 | 472942417.978425 SAUDI ARABIA | 1993 | 460464547.962218 SAUDI ARABIA | 1992 | 469379048.415279 UNITED KINGDOM | 1998 | 273094467.241313 UNITED KINGDOM | 1997 | 461508252.407125 UNITED KINGDOM | 1996 | 464507955.344593 UNITED KINGDOM | 1995 | 466330166.244331 UNITED KINGDOM | 1994 | 463386983.168282 UNITED KINGDOM | 1993 | 469605340.794714 UNITED KINGDOM | 1992 | 466081167.975359 UNITED STATES | 1998 | 272217434.493826 UNITED STATES | 1997 | 464782364.330939 UNITED STATES | 1996 | 472430838.138935 UNITED STATES | 1995 | 469601790.053212 UNITED STATES | 1994 | 461926814.536891 UNITED STATES | 1993 | 464596740.168791 UNITED STATES | 1992 | 460670416.347456 VIETNAM | 1998 | 274692734.771642 VIETNAM | 1997 | 468008445.125838 VIETNAM | 1996 | 471084979.819876 VIETNAM | 1995 | 461018498.53948 VIETNAM | 1994 | 466174916.682152 VIETNAM | 1993 | 471357260.156478 VIETNAM | 1992 | 463722964.194243 (175 rows) COMMIT; COMMIT