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 '%floral%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267438323.79185 ALGERIA | 1997 | 451586100.187106 ALGERIA | 1996 | 459519541.351955 ALGERIA | 1995 | 461482322.139642 ALGERIA | 1994 | 459500220.195435 ALGERIA | 1993 | 460607586.593702 ALGERIA | 1992 | 459818432.268069 ARGENTINA | 1998 | 278913890.479596 ARGENTINA | 1997 | 473354214.648544 ARGENTINA | 1996 | 476728793.523099 ARGENTINA | 1995 | 469543438.103933 ARGENTINA | 1994 | 477508357.069581 ARGENTINA | 1993 | 468383389.232718 ARGENTINA | 1992 | 473357382.062873 BRAZIL | 1998 | 273011679.02681 BRAZIL | 1997 | 462878150.389563 BRAZIL | 1996 | 461880797.159411 BRAZIL | 1995 | 470456393.346892 BRAZIL | 1994 | 468201547.819317 BRAZIL | 1993 | 469207364.888131 BRAZIL | 1992 | 467791437.891716 CANADA | 1998 | 277869050.364271 CANADA | 1997 | 479235541.436503 CANADA | 1996 | 476663362.342375 CANADA | 1995 | 474730250.97569 CANADA | 1994 | 471618947.916459 CANADA | 1993 | 471412377.72147 CANADA | 1992 | 473238605.31107 CHINA | 1998 | 266362717.353646 CHINA | 1997 | 468859312.164686 CHINA | 1996 | 469112656.232688 CHINA | 1995 | 462505007.994022 CHINA | 1994 | 472637299.178636 CHINA | 1993 | 474473386.665632 CHINA | 1992 | 469013465.981978 EGYPT | 1998 | 276640274.762344 EGYPT | 1997 | 463787677.832062 EGYPT | 1996 | 468178623.495278 EGYPT | 1995 | 467645335.716286 EGYPT | 1994 | 463778898.482666 EGYPT | 1993 | 460413293.439566 EGYPT | 1992 | 462426577.980278 ETHIOPIA | 1998 | 269172671.416576 ETHIOPIA | 1997 | 449851763.131033 ETHIOPIA | 1996 | 447010994.710282 ETHIOPIA | 1995 | 461056870.496233 ETHIOPIA | 1994 | 447647587.946704 ETHIOPIA | 1993 | 449068584.776071 ETHIOPIA | 1992 | 456186793.51844 FRANCE | 1998 | 269422446.674563 FRANCE | 1997 | 457295640.764652 FRANCE | 1996 | 463604318.992957 FRANCE | 1995 | 460606952.695986 FRANCE | 1994 | 457340073.472463 FRANCE | 1993 | 474068671.454033 FRANCE | 1992 | 460912802.07091 GERMANY | 1998 | 276596557.418897 GERMANY | 1997 | 475452747.689835 GERMANY | 1996 | 470910081.462704 GERMANY | 1995 | 473920807.650687 GERMANY | 1994 | 471919155.111667 GERMANY | 1993 | 476445443.712277 GERMANY | 1992 | 478435432.548095 INDIA | 1998 | 275679031.571789 INDIA | 1997 | 463582093.472214 INDIA | 1996 | 470137980.756952 INDIA | 1995 | 467609930.991301 INDIA | 1994 | 462290768.93516 INDIA | 1993 | 467540657.476096 INDIA | 1992 | 473851134.943081 INDONESIA | 1998 | 273858393.073693 INDONESIA | 1997 | 465637703.723728 INDONESIA | 1996 | 470896284.893751 INDONESIA | 1995 | 467868556.970964 INDONESIA | 1994 | 461876920.893224 INDONESIA | 1993 | 459896339.706686 INDONESIA | 1992 | 466599331.841525 IRAN | 1998 | 268580319.449047 IRAN | 1997 | 457121191.430672 IRAN | 1996 | 453880073.340411 IRAN | 1995 | 450387021.867143 IRAN | 1994 | 452597081.248006 IRAN | 1993 | 459995778.083205 IRAN | 1992 | 461153765.367149 IRAQ | 1998 | 277566042.756387 IRAQ | 1997 | 464452997.920065 IRAQ | 1996 | 472199525.204643 IRAQ | 1995 | 475362752.252423 IRAQ | 1994 | 473907380.575526 IRAQ | 1993 | 477912482.792196 IRAQ | 1992 | 469007597.318067 JAPAN | 1998 | 267196520.997909 JAPAN | 1997 | 464754277.554736 JAPAN | 1996 | 458559615.199811 JAPAN | 1995 | 459115097.956252 JAPAN | 1994 | 451615966.507858 JAPAN | 1993 | 455792546.050471 JAPAN | 1992 | 462433495.970274 JORDAN | 1998 | 270238077.615613 JORDAN | 1997 | 468693451.917053 JORDAN | 1996 | 464730787.26883 JORDAN | 1995 | 457872785.506397 JORDAN | 1994 | 464916683.929959 JORDAN | 1993 | 460959492.124499 JORDAN | 1992 | 465320810.16471 KENYA | 1998 | 273304575.569657 KENYA | 1997 | 455989377.974631 KENYA | 1996 | 455437464.430887 KENYA | 1995 | 453121095.517754 KENYA | 1994 | 459942581.120445 KENYA | 1993 | 464594917.08761 KENYA | 1992 | 456717910.360925 MOROCCO | 1998 | 272568460.2775 MOROCCO | 1997 | 452565154.590518 MOROCCO | 1996 | 464820803.291572 MOROCCO | 1995 | 470278695.661791 MOROCCO | 1994 | 473076394.981819 MOROCCO | 1993 | 459009846.267846 MOROCCO | 1992 | 462416750.004798 MOZAMBIQUE | 1998 | 262152864.034447 MOZAMBIQUE | 1997 | 449470088.238929 MOZAMBIQUE | 1996 | 451371137.009913 MOZAMBIQUE | 1995 | 450392196.428607 MOZAMBIQUE | 1994 | 453369605.875629 MOZAMBIQUE | 1993 | 449456764.003617 MOZAMBIQUE | 1992 | 449059636.36104 PERU | 1998 | 273044145.502497 PERU | 1997 | 459284940.090344 PERU | 1996 | 464427245.010933 PERU | 1995 | 457059839.5504 PERU | 1994 | 455588390.807267 PERU | 1993 | 453516746.967688 PERU | 1992 | 454686666.386573 ROMANIA | 1998 | 267248325.740315 ROMANIA | 1997 | 463783136.285517 ROMANIA | 1996 | 467690645.589237 ROMANIA | 1995 | 461611872.681051 ROMANIA | 1994 | 468375375.094381 ROMANIA | 1993 | 456806794.696719 ROMANIA | 1992 | 465730790.799745 RUSSIA | 1998 | 275129187.697873 RUSSIA | 1997 | 461644059.325993 RUSSIA | 1996 | 470564125.272461 RUSSIA | 1995 | 468607568.633782 RUSSIA | 1994 | 467836174.271848 RUSSIA | 1993 | 467882331.50691 RUSSIA | 1992 | 466778683.887532 SAUDI ARABIA | 1998 | 275635084.933234 SAUDI ARABIA | 1997 | 462404574.907321 SAUDI ARABIA | 1996 | 455044570.529376 SAUDI ARABIA | 1995 | 460910773.527674 SAUDI ARABIA | 1994 | 460322481.171396 SAUDI ARABIA | 1993 | 456443922.408555 SAUDI ARABIA | 1992 | 461255792.660996 UNITED KINGDOM | 1998 | 276690727.800282 UNITED KINGDOM | 1997 | 457707175.76159 UNITED KINGDOM | 1996 | 464335553.64164 UNITED KINGDOM | 1995 | 467205648.321365 UNITED KINGDOM | 1994 | 465410192.539836 UNITED KINGDOM | 1993 | 465713511.853628 UNITED KINGDOM | 1992 | 471053127.379557 UNITED STATES | 1998 | 277646912.261268 UNITED STATES | 1997 | 477617608.144134 UNITED STATES | 1996 | 472913978.701403 UNITED STATES | 1995 | 474171473.870082 UNITED STATES | 1994 | 478098408.667758 UNITED STATES | 1993 | 477680819.481175 UNITED STATES | 1992 | 471346952.023351 VIETNAM | 1998 | 267242675.049754 VIETNAM | 1997 | 459738541.161614 VIETNAM | 1996 | 463950863.895996 VIETNAM | 1995 | 449209530.457752 VIETNAM | 1994 | 453317518.226794 VIETNAM | 1993 | 465591644.240689 VIETNAM | 1992 | 455111981.450747 (175 rows) COMMIT; COMMIT