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 '%chartreuse%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 268634526.931015 ALGERIA | 1997 | 451310101.21912 ALGERIA | 1996 | 454410224.722208 ALGERIA | 1995 | 455125207.959039 ALGERIA | 1994 | 445746341.83116 ALGERIA | 1993 | 448947496.457621 ALGERIA | 1992 | 450707633.695018 ARGENTINA | 1998 | 278320057.166697 ARGENTINA | 1997 | 473181540.172439 ARGENTINA | 1996 | 477222777.575231 ARGENTINA | 1995 | 475110192.196236 ARGENTINA | 1994 | 472748033.371499 ARGENTINA | 1993 | 477681840.466795 ARGENTINA | 1992 | 472216779.413818 BRAZIL | 1998 | 268656537.895347 BRAZIL | 1997 | 461111864.713863 BRAZIL | 1996 | 461742794.958473 BRAZIL | 1995 | 455367147.780041 BRAZIL | 1994 | 458645705.690009 BRAZIL | 1993 | 452140200.942 BRAZIL | 1992 | 460935016.72155 CANADA | 1998 | 273526576.036138 CANADA | 1997 | 469254358.215357 CANADA | 1996 | 470202165.897605 CANADA | 1995 | 465192769.988251 CANADA | 1994 | 465149735.435304 CANADA | 1993 | 465421929.063134 CANADA | 1992 | 468752259.535916 CHINA | 1998 | 278295804.162667 CHINA | 1997 | 466641834.299654 CHINA | 1996 | 469874417.624818 CHINA | 1995 | 456480241.543759 CHINA | 1994 | 462438756.594384 CHINA | 1993 | 460581158.66064 CHINA | 1992 | 462042855.337437 EGYPT | 1998 | 274498437.129628 EGYPT | 1997 | 455451541.428645 EGYPT | 1996 | 462320512.408911 EGYPT | 1995 | 466050059.370928 EGYPT | 1994 | 461003673.336495 EGYPT | 1993 | 466670326.717066 EGYPT | 1992 | 464700202.583538 ETHIOPIA | 1998 | 267147868.4651 ETHIOPIA | 1997 | 453793714.003293 ETHIOPIA | 1996 | 459224543.035577 ETHIOPIA | 1995 | 458919819.523085 ETHIOPIA | 1994 | 464240414.829548 ETHIOPIA | 1993 | 461436207.270939 ETHIOPIA | 1992 | 454925263.098131 FRANCE | 1998 | 270905415.170051 FRANCE | 1997 | 458778445.775015 FRANCE | 1996 | 460023328.801687 FRANCE | 1995 | 459818062.88839 FRANCE | 1994 | 456431759.182399 FRANCE | 1993 | 460740636.454116 FRANCE | 1992 | 453776508.831606 GERMANY | 1998 | 278811412.158135 GERMANY | 1997 | 469933846.875875 GERMANY | 1996 | 480949647.118124 GERMANY | 1995 | 475733292.923864 GERMANY | 1994 | 469952634.864551 GERMANY | 1993 | 472922781.26037 GERMANY | 1992 | 469949519.105325 INDIA | 1998 | 268844998.830469 INDIA | 1997 | 468623176.030455 INDIA | 1996 | 478497649.957216 INDIA | 1995 | 465669322.903767 INDIA | 1994 | 468680060.553669 INDIA | 1993 | 474752059.18812 INDIA | 1992 | 461548802.718885 INDONESIA | 1998 | 279305084.631641 INDONESIA | 1997 | 455916718.588187 INDONESIA | 1996 | 466222808.404964 INDONESIA | 1995 | 464681420.266238 INDONESIA | 1994 | 465387897.200004 INDONESIA | 1993 | 461254616.937016 INDONESIA | 1992 | 470485979.468649 IRAN | 1998 | 267099087.844511 IRAN | 1997 | 453336939.210566 IRAN | 1996 | 460030609.216013 IRAN | 1995 | 456725435.270235 IRAN | 1994 | 464480007.992148 IRAN | 1993 | 460221158.877858 IRAN | 1992 | 450152267.065777 IRAQ | 1998 | 280092616.637071 IRAQ | 1997 | 472751195.180617 IRAQ | 1996 | 469317429.881024 IRAQ | 1995 | 467666746.251288 IRAQ | 1994 | 464778983.900603 IRAQ | 1993 | 469531673.720897 IRAQ | 1992 | 477404287.551802 JAPAN | 1998 | 273423094.324106 JAPAN | 1997 | 470692812.955432 JAPAN | 1996 | 466158251.760756 JAPAN | 1995 | 462094611.187116 JAPAN | 1994 | 469289014.661667 JAPAN | 1993 | 464660161.274423 JAPAN | 1992 | 460020072.842563 JORDAN | 1998 | 268998727.291257 JORDAN | 1997 | 459301472.335025 JORDAN | 1996 | 459070106.788108 JORDAN | 1995 | 450645702.505211 JORDAN | 1994 | 454908249.607461 JORDAN | 1993 | 450697051.759646 JORDAN | 1992 | 456216012.068787 KENYA | 1998 | 271382821.330118 KENYA | 1997 | 463158342.989115 KENYA | 1996 | 470703622.179614 KENYA | 1995 | 476052042.181102 KENYA | 1994 | 465021645.969261 KENYA | 1993 | 463057673.232083 KENYA | 1992 | 475171595.932977 MOROCCO | 1998 | 280952827.479645 MOROCCO | 1997 | 467615063.021262 MOROCCO | 1996 | 468917416.560618 MOROCCO | 1995 | 464422069.737653 MOROCCO | 1994 | 467823252.908271 MOROCCO | 1993 | 469324905.149829 MOROCCO | 1992 | 468631871.659371 MOZAMBIQUE | 1998 | 274436725.603325 MOZAMBIQUE | 1997 | 469114425.450256 MOZAMBIQUE | 1996 | 466294703.782896 MOZAMBIQUE | 1995 | 472636004.104379 MOZAMBIQUE | 1994 | 467144733.472935 MOZAMBIQUE | 1993 | 466508750.722106 MOZAMBIQUE | 1992 | 466266803.888756 PERU | 1998 | 275001431.742367 PERU | 1997 | 464345887.135861 PERU | 1996 | 463190335.234104 PERU | 1995 | 460522015.830581 PERU | 1994 | 470789380.03179 PERU | 1993 | 461425652.679127 PERU | 1992 | 467917405.408174 ROMANIA | 1998 | 268602212.716435 ROMANIA | 1997 | 465840827.477329 ROMANIA | 1996 | 468005881.801436 ROMANIA | 1995 | 469778282.798031 ROMANIA | 1994 | 460557342.414603 ROMANIA | 1993 | 463129424.174625 ROMANIA | 1992 | 461539573.948164 RUSSIA | 1998 | 276889821.175672 RUSSIA | 1997 | 470532486.122186 RUSSIA | 1996 | 467587628.97928 RUSSIA | 1995 | 470815201.406107 RUSSIA | 1994 | 469357596.199146 RUSSIA | 1993 | 468330939.911658 RUSSIA | 1992 | 473515486.791613 SAUDI ARABIA | 1998 | 280254325.714021 SAUDI ARABIA | 1997 | 465112205.368859 SAUDI ARABIA | 1996 | 464792499.141228 SAUDI ARABIA | 1995 | 466432940.07425 SAUDI ARABIA | 1994 | 464446932.653095 SAUDI ARABIA | 1993 | 462530858.712548 SAUDI ARABIA | 1992 | 463731046.879565 UNITED KINGDOM | 1998 | 271749593.434487 UNITED KINGDOM | 1997 | 460217341.101737 UNITED KINGDOM | 1996 | 464657387.677635 UNITED KINGDOM | 1995 | 459707026.356656 UNITED KINGDOM | 1994 | 461227560.720003 UNITED KINGDOM | 1993 | 455849538.100004 UNITED KINGDOM | 1992 | 457704065.116283 UNITED STATES | 1998 | 269769228.460682 UNITED STATES | 1997 | 455052947.67283 UNITED STATES | 1996 | 458739573.51181 UNITED STATES | 1995 | 465443509.948738 UNITED STATES | 1994 | 469970896.653317 UNITED STATES | 1993 | 456844194.972839 UNITED STATES | 1992 | 454373068.294556 VIETNAM | 1998 | 273888965.424626 VIETNAM | 1997 | 460477426.626821 VIETNAM | 1996 | 467914178.882324 VIETNAM | 1995 | 464096647.748694 VIETNAM | 1994 | 466695391.022308 VIETNAM | 1993 | 464135249.41978 VIETNAM | 1992 | 461265610.371847 (175 rows) COMMIT; COMMIT