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 '%orchid%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 269377804.87921 ALGERIA | 1997 | 455566063.470155 ALGERIA | 1996 | 461072671.688689 ALGERIA | 1995 | 470947671.000841 ALGERIA | 1994 | 462426112.657188 ALGERIA | 1993 | 461172243.619919 ALGERIA | 1992 | 465494355.475953 ARGENTINA | 1998 | 272015498.161638 ARGENTINA | 1997 | 471891987.144541 ARGENTINA | 1996 | 466019684.670993 ARGENTINA | 1995 | 462803343.033483 ARGENTINA | 1994 | 470209810.913091 ARGENTINA | 1993 | 464322248.327207 ARGENTINA | 1992 | 458406873.724514 BRAZIL | 1998 | 266469353.663252 BRAZIL | 1997 | 471357985.76328 BRAZIL | 1996 | 468420549.596895 BRAZIL | 1995 | 470397823.319985 BRAZIL | 1994 | 465883762.084883 BRAZIL | 1993 | 465447907.405836 BRAZIL | 1992 | 468444334.481632 CANADA | 1998 | 281158299.786468 CANADA | 1997 | 474387493.078024 CANADA | 1996 | 475626079.000037 CANADA | 1995 | 477831360.878084 CANADA | 1994 | 475168267.23973 CANADA | 1993 | 475859571.790274 CANADA | 1992 | 475553242.356447 CHINA | 1998 | 274546097.239431 CHINA | 1997 | 472190895.81035 CHINA | 1996 | 467962078.657882 CHINA | 1995 | 465367119.074069 CHINA | 1994 | 468572627.374567 CHINA | 1993 | 467475251.915825 CHINA | 1992 | 468114438.682706 EGYPT | 1998 | 273970972.789442 EGYPT | 1997 | 459149988.922323 EGYPT | 1996 | 473015505.54505 EGYPT | 1995 | 460352196.583173 EGYPT | 1994 | 462715352.521225 EGYPT | 1993 | 467859986.401857 EGYPT | 1992 | 462679791.339446 ETHIOPIA | 1998 | 265699776.372356 ETHIOPIA | 1997 | 465799472.156608 ETHIOPIA | 1996 | 457764519.785612 ETHIOPIA | 1995 | 459622980.71064 ETHIOPIA | 1994 | 462582501.591574 ETHIOPIA | 1993 | 460528479.320238 ETHIOPIA | 1992 | 466326636.949291 FRANCE | 1998 | 269048111.456103 FRANCE | 1997 | 466515738.257221 FRANCE | 1996 | 461400301.331212 FRANCE | 1995 | 458854436.769348 FRANCE | 1994 | 458346416.45971 FRANCE | 1993 | 458642836.743164 FRANCE | 1992 | 457326653.45722 GERMANY | 1998 | 271827425.226126 GERMANY | 1997 | 475439243.991339 GERMANY | 1996 | 471648157.713881 GERMANY | 1995 | 474685487.002235 GERMANY | 1994 | 470852187.604489 GERMANY | 1993 | 478010518.369251 GERMANY | 1992 | 469862470.444534 INDIA | 1998 | 275393221.858139 INDIA | 1997 | 475969187.793715 INDIA | 1996 | 482055209.47034 INDIA | 1995 | 476841106.886738 INDIA | 1994 | 477931016.314351 INDIA | 1993 | 483013223.264526 INDIA | 1992 | 476788076.3296 INDONESIA | 1998 | 276199312.019585 INDONESIA | 1997 | 463908749.930927 INDONESIA | 1996 | 461715112.870367 INDONESIA | 1995 | 450456010.35929 INDONESIA | 1994 | 459203110.777105 INDONESIA | 1993 | 471102845.992387 INDONESIA | 1992 | 461618128.941083 IRAN | 1998 | 268525601.393806 IRAN | 1997 | 468706114.057302 IRAN | 1996 | 468530823.67446 IRAN | 1995 | 472485480.739321 IRAN | 1994 | 463960792.053202 IRAN | 1993 | 471612655.341727 IRAN | 1992 | 476456370.802873 IRAQ | 1998 | 280480112.300469 IRAQ | 1997 | 471455956.682206 IRAQ | 1996 | 467893065.297754 IRAQ | 1995 | 471935601.262163 IRAQ | 1994 | 474117359.813033 IRAQ | 1993 | 476384466.996707 IRAQ | 1992 | 477138828.793304 JAPAN | 1998 | 269526681.47006 JAPAN | 1997 | 465044314.051591 JAPAN | 1996 | 458446922.943923 JAPAN | 1995 | 462640678.591355 JAPAN | 1994 | 458066722.372789 JAPAN | 1993 | 464808350.947692 JAPAN | 1992 | 472013956.179165 JORDAN | 1998 | 267467226.195589 JORDAN | 1997 | 465178263.156156 JORDAN | 1996 | 460285223.074516 JORDAN | 1995 | 466295977.10033 JORDAN | 1994 | 464485661.707534 JORDAN | 1993 | 464672686.098156 JORDAN | 1992 | 461738759.172805 KENYA | 1998 | 283592744.007856 KENYA | 1997 | 473316755.610267 KENYA | 1996 | 481067016.605631 KENYA | 1995 | 476209668.010506 KENYA | 1994 | 477580733.588884 KENYA | 1993 | 484271021.486735 KENYA | 1992 | 476779679.497868 MOROCCO | 1998 | 277121833.511229 MOROCCO | 1997 | 474221436.572874 MOROCCO | 1996 | 480795242.820769 MOROCCO | 1995 | 470514961.217593 MOROCCO | 1994 | 478994767.007548 MOROCCO | 1993 | 479159286.307187 MOROCCO | 1992 | 475704396.066134 MOZAMBIQUE | 1998 | 268212038.589535 MOZAMBIQUE | 1997 | 468134236.199976 MOZAMBIQUE | 1996 | 470214763.538726 MOZAMBIQUE | 1995 | 464890535.259097 MOZAMBIQUE | 1994 | 458618461.860358 MOZAMBIQUE | 1993 | 467091394.63592 MOZAMBIQUE | 1992 | 458374122.61236 PERU | 1998 | 276101874.526819 PERU | 1997 | 465391003.301801 PERU | 1996 | 474352555.190771 PERU | 1995 | 463755150.24802 PERU | 1994 | 474764990.930245 PERU | 1993 | 476833066.897939 PERU | 1992 | 475846674.34535 ROMANIA | 1998 | 271576844.619341 ROMANIA | 1997 | 470256002.542863 ROMANIA | 1996 | 475199814.582592 ROMANIA | 1995 | 471947484.297983 ROMANIA | 1994 | 467000984.499446 ROMANIA | 1993 | 461813682.497085 ROMANIA | 1992 | 470913381.27205 RUSSIA | 1998 | 276869390.415954 RUSSIA | 1997 | 474489213.756728 RUSSIA | 1996 | 475893315.535713 RUSSIA | 1995 | 468118509.176727 RUSSIA | 1994 | 476363359.376843 RUSSIA | 1993 | 469513998.99408 RUSSIA | 1992 | 468353013.016828 SAUDI ARABIA | 1998 | 274011889.579781 SAUDI ARABIA | 1997 | 462740181.447427 SAUDI ARABIA | 1996 | 468109703.298146 SAUDI ARABIA | 1995 | 469242104.940387 SAUDI ARABIA | 1994 | 472168207.724909 SAUDI ARABIA | 1993 | 474117412.768988 SAUDI ARABIA | 1992 | 466726610.011491 UNITED KINGDOM | 1998 | 268826880.432068 UNITED KINGDOM | 1997 | 454509537.430775 UNITED KINGDOM | 1996 | 461276314.450788 UNITED KINGDOM | 1995 | 459128830.802866 UNITED KINGDOM | 1994 | 462590833.699562 UNITED KINGDOM | 1993 | 463028889.78594 UNITED KINGDOM | 1992 | 466362339.04865 UNITED STATES | 1998 | 280885945.17354 UNITED STATES | 1997 | 479506200.348523 UNITED STATES | 1996 | 472723411.412263 UNITED STATES | 1995 | 477378448.842652 UNITED STATES | 1994 | 475484214.89988 UNITED STATES | 1993 | 474678084.158568 UNITED STATES | 1992 | 485146814.03239 VIETNAM | 1998 | 271397096.936081 VIETNAM | 1997 | 466100057.693384 VIETNAM | 1996 | 470826802.623598 VIETNAM | 1995 | 461828179.061581 VIETNAM | 1994 | 468896909.679479 VIETNAM | 1993 | 465986008.815217 VIETNAM | 1992 | 462007014.511144 (175 rows) COMMIT; COMMIT