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 | 267671755.762512 ALGERIA | 1997 | 451774818.760916 ALGERIA | 1996 | 459706431.484149 ALGERIA | 1995 | 461579241.790764 ALGERIA | 1994 | 459528655.866009 ALGERIA | 1993 | 459753043.370449 ALGERIA | 1992 | 459663789.609555 ARGENTINA | 1998 | 279214906.400363 ARGENTINA | 1997 | 473790001.639481 ARGENTINA | 1996 | 477147545.695079 ARGENTINA | 1995 | 470146465.274499 ARGENTINA | 1994 | 477867236.060921 ARGENTINA | 1993 | 468037714.306673 ARGENTINA | 1992 | 473451221.586581 BRAZIL | 1998 | 272803093.011726 BRAZIL | 1997 | 462992258.747637 BRAZIL | 1996 | 461517829.406192 BRAZIL | 1995 | 470497436.823608 BRAZIL | 1994 | 467402044.738793 BRAZIL | 1993 | 468764202.431023 BRAZIL | 1992 | 467785867.74993 CANADA | 1998 | 278113568.849376 CANADA | 1997 | 479016269.802294 CANADA | 1996 | 476418316.195932 CANADA | 1995 | 474658505.950225 CANADA | 1994 | 471672751.817388 CANADA | 1993 | 471773549.15972 CANADA | 1992 | 472824413.821499 CHINA | 1998 | 266145124.996565 CHINA | 1997 | 469318028.338731 CHINA | 1996 | 468827084.732236 CHINA | 1995 | 462707255.887592 CHINA | 1994 | 472550576.659177 CHINA | 1993 | 474366081.783996 CHINA | 1992 | 468339087.606127 EGYPT | 1998 | 276755362.336639 EGYPT | 1997 | 463323912.013499 EGYPT | 1996 | 468552946.909886 EGYPT | 1995 | 467653817.172337 EGYPT | 1994 | 463503979.554795 EGYPT | 1993 | 459929023.305153 EGYPT | 1992 | 462693092.992182 ETHIOPIA | 1998 | 269960506.752522 ETHIOPIA | 1997 | 450477816.212101 ETHIOPIA | 1996 | 447620758.927406 ETHIOPIA | 1995 | 461005845.245719 ETHIOPIA | 1994 | 447963964.097249 ETHIOPIA | 1993 | 449573274.422205 ETHIOPIA | 1992 | 455958427.029658 FRANCE | 1998 | 269522873.996237 FRANCE | 1997 | 457357036.181278 FRANCE | 1996 | 463585854.147476 FRANCE | 1995 | 461338963.079263 FRANCE | 1994 | 457970975.856172 FRANCE | 1993 | 473211100.790179 FRANCE | 1992 | 460723267.906035 GERMANY | 1998 | 276899104.144801 GERMANY | 1997 | 476149240.593005 GERMANY | 1996 | 471227593.061341 GERMANY | 1995 | 473980503.334112 GERMANY | 1994 | 471601372.731227 GERMANY | 1993 | 476473635.174497 GERMANY | 1992 | 479081094.166225 INDIA | 1998 | 275614427.095913 INDIA | 1997 | 464073471.851788 INDIA | 1996 | 470502404.432099 INDIA | 1995 | 467739679.984865 INDIA | 1994 | 462388516.499723 INDIA | 1993 | 467576032.380689 INDIA | 1992 | 473658166.010137 INDONESIA | 1998 | 273497822.207165 INDONESIA | 1997 | 466137815.658737 INDONESIA | 1996 | 470885638.043586 INDONESIA | 1995 | 467499474.613239 INDONESIA | 1994 | 462166479.514393 INDONESIA | 1993 | 460270430.452279 INDONESIA | 1992 | 466852303.538212 IRAN | 1998 | 268667976.253111 IRAN | 1997 | 456664089.274879 IRAN | 1996 | 454178632.56793 IRAN | 1995 | 450044551.092327 IRAN | 1994 | 452736901.139981 IRAN | 1993 | 459945750.59597 IRAN | 1992 | 461136213.479163 IRAQ | 1998 | 277607016.758916 IRAQ | 1997 | 464226518.291065 IRAQ | 1996 | 472288155.273401 IRAQ | 1995 | 474844969.376916 IRAQ | 1994 | 473897597.980518 IRAQ | 1993 | 478155824.781373 IRAQ | 1992 | 469388527.965594 JAPAN | 1998 | 267759004.821757 JAPAN | 1997 | 464507934.25402 JAPAN | 1996 | 458409703.612292 JAPAN | 1995 | 459529697.11298 JAPAN | 1994 | 451709113.885789 JAPAN | 1993 | 455772380.331826 JAPAN | 1992 | 461844019.516125 JORDAN | 1998 | 270412820.689389 JORDAN | 1997 | 469155131.186125 JORDAN | 1996 | 464474245.643771 JORDAN | 1995 | 458327453.063752 JORDAN | 1994 | 464078887.950801 JORDAN | 1993 | 460551395.192132 JORDAN | 1992 | 465841425.459849 KENYA | 1998 | 273764775.498767 KENYA | 1997 | 455677758.029563 KENYA | 1996 | 455655346.05165 KENYA | 1995 | 453162707.932457 KENYA | 1994 | 460188423.116544 KENYA | 1993 | 464067294.854423 KENYA | 1992 | 456852436.959805 MOROCCO | 1998 | 272875465.119772 MOROCCO | 1997 | 452545795.042614 MOROCCO | 1996 | 464658912.689812 MOROCCO | 1995 | 470517941.398315 MOROCCO | 1994 | 472912110.385046 MOROCCO | 1993 | 458999957.609463 MOROCCO | 1992 | 462146710.923336 MOZAMBIQUE | 1998 | 262623865.964882 MOZAMBIQUE | 1997 | 449197275.286522 MOZAMBIQUE | 1996 | 451621252.801107 MOZAMBIQUE | 1995 | 450910580.336517 MOZAMBIQUE | 1994 | 453228123.611124 MOZAMBIQUE | 1993 | 449298301.669081 MOZAMBIQUE | 1992 | 449013741.624283 PERU | 1998 | 272472647.923036 PERU | 1997 | 459315036.888384 PERU | 1996 | 464168457.013995 PERU | 1995 | 456685706.685493 PERU | 1994 | 455980129.795028 PERU | 1993 | 453562497.820877 PERU | 1992 | 454870425.114563 ROMANIA | 1998 | 267602432.764079 ROMANIA | 1997 | 464185983.837997 ROMANIA | 1996 | 467558927.204795 ROMANIA | 1995 | 461735536.056929 ROMANIA | 1994 | 468510225.117419 ROMANIA | 1993 | 456781313.515996 ROMANIA | 1992 | 465988515.108052 RUSSIA | 1998 | 275818459.576801 RUSSIA | 1997 | 461910989.234365 RUSSIA | 1996 | 470489554.923308 RUSSIA | 1995 | 468868416.521142 RUSSIA | 1994 | 467825788.063057 RUSSIA | 1993 | 468702460.793385 RUSSIA | 1992 | 467473646.564123 SAUDI ARABIA | 1998 | 275787672.635039 SAUDI ARABIA | 1997 | 462160029.592492 SAUDI ARABIA | 1996 | 455644107.915525 SAUDI ARABIA | 1995 | 460809197.35208 SAUDI ARABIA | 1994 | 460323116.092428 SAUDI ARABIA | 1993 | 456715447.626159 SAUDI ARABIA | 1992 | 461617324.38137 UNITED KINGDOM | 1998 | 276975612.600662 UNITED KINGDOM | 1997 | 457406513.1965 UNITED KINGDOM | 1996 | 464633789.215982 UNITED KINGDOM | 1995 | 466882205.498249 UNITED KINGDOM | 1994 | 465248800.038632 UNITED KINGDOM | 1993 | 465477289.433706 UNITED KINGDOM | 1992 | 471161685.793834 UNITED STATES | 1998 | 277263002.275403 UNITED STATES | 1997 | 478062508.458804 UNITED STATES | 1996 | 473096542.546139 UNITED STATES | 1995 | 474263172.749339 UNITED STATES | 1994 | 478336617.791439 UNITED STATES | 1993 | 477867958.743083 UNITED STATES | 1992 | 471311906.402751 VIETNAM | 1998 | 267385776.651845 VIETNAM | 1997 | 460089114.982526 VIETNAM | 1996 | 463737010.596351 VIETNAM | 1995 | 449761445.982387 VIETNAM | 1994 | 452821546.103051 VIETNAM | 1993 | 465451788.105511 VIETNAM | 1992 | 454902340.82214 (175 rows) COMMIT; COMMIT