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 | 267290701.377934 ALGERIA | 1997 | 451041324.686988 ALGERIA | 1996 | 459393966.36358 ALGERIA | 1995 | 461066354.583871 ALGERIA | 1994 | 458979455.364489 ALGERIA | 1993 | 459737078.877648 ALGERIA | 1992 | 459684185.105806 ARGENTINA | 1998 | 278766272.712356 ARGENTINA | 1997 | 473107424.319712 ARGENTINA | 1996 | 476348900.95406 ARGENTINA | 1995 | 469517820.730691 ARGENTINA | 1994 | 477062383.061016 ARGENTINA | 1993 | 467577850.008804 ARGENTINA | 1992 | 473160423.509405 BRAZIL | 1998 | 272727985.271877 BRAZIL | 1997 | 462798660.062286 BRAZIL | 1996 | 461115194.007853 BRAZIL | 1995 | 469959257.734543 BRAZIL | 1994 | 467531698.488769 BRAZIL | 1993 | 468535742.775163 BRAZIL | 1992 | 467329565.336226 CANADA | 1998 | 277431171.042559 CANADA | 1997 | 478899488.345575 CANADA | 1996 | 476481508.746556 CANADA | 1995 | 474199538.673535 CANADA | 1994 | 471134958.324703 CANADA | 1993 | 471026456.6379 CANADA | 1992 | 472769211.576728 CHINA | 1998 | 266104419.612665 CHINA | 1997 | 468791937.851599 CHINA | 1996 | 468755343.83226 CHINA | 1995 | 461953940.273192 CHINA | 1994 | 472295136.990975 CHINA | 1993 | 474204336.688182 CHINA | 1992 | 468273794.643835 EGYPT | 1998 | 276448147.446568 EGYPT | 1997 | 463066377.165604 EGYPT | 1996 | 467999672.603065 EGYPT | 1995 | 466966312.51482 EGYPT | 1994 | 463101176.152127 EGYPT | 1993 | 459671594.740709 EGYPT | 1992 | 461926299.48319 ETHIOPIA | 1998 | 269174605.644791 ETHIOPIA | 1997 | 449582488.336998 ETHIOPIA | 1996 | 446750785.800744 ETHIOPIA | 1995 | 460627815.204951 ETHIOPIA | 1994 | 447382198.165367 ETHIOPIA | 1993 | 448868676.489197 ETHIOPIA | 1992 | 455444308.189029 FRANCE | 1998 | 269331879.815199 FRANCE | 1997 | 457047138.625048 FRANCE | 1996 | 463173209.317576 FRANCE | 1995 | 460351175.099386 FRANCE | 1994 | 457458061.446647 FRANCE | 1993 | 473064084.808697 FRANCE | 1992 | 460700740.848952 GERMANY | 1998 | 276455589.40931 GERMANY | 1997 | 475234470.916555 GERMANY | 1996 | 470397409.758309 GERMANY | 1995 | 473535838.47655 GERMANY | 1994 | 471554406.804681 GERMANY | 1993 | 475630496.769247 GERMANY | 1992 | 478179957.429753 INDIA | 1998 | 275184055.415317 INDIA | 1997 | 463487687.666114 INDIA | 1996 | 469743227.379752 INDIA | 1995 | 467150207.840793 INDIA | 1994 | 462081144.993683 INDIA | 1993 | 466647102.337813 INDIA | 1992 | 473417641.2227 INDONESIA | 1998 | 273251555.649019 INDONESIA | 1997 | 465446698.52651 INDONESIA | 1996 | 470191245.11866 INDONESIA | 1995 | 467242636.395384 INDONESIA | 1994 | 461716348.323026 INDONESIA | 1993 | 459217789.039973 INDONESIA | 1992 | 466195057.814474 IRAN | 1998 | 268626420.769278 IRAN | 1997 | 456374032.795846 IRAN | 1996 | 453616106.175902 IRAN | 1995 | 449834111.387982 IRAN | 1994 | 451940697.996385 IRAN | 1993 | 459373337.424443 IRAN | 1992 | 461096403.027947 IRAQ | 1998 | 276972248.109413 IRAQ | 1997 | 463872197.717417 IRAQ | 1996 | 471885557.353279 IRAQ | 1995 | 474751584.997951 IRAQ | 1994 | 473387601.81868 IRAQ | 1993 | 477382117.4459 IRAQ | 1992 | 468571400.392904 JAPAN | 1998 | 266983055.155541 JAPAN | 1997 | 464040299.521977 JAPAN | 1996 | 457637406.985892 JAPAN | 1995 | 459138417.342241 JAPAN | 1994 | 451039336.191998 JAPAN | 1993 | 455661311.752049 JAPAN | 1992 | 461716881.951709 JORDAN | 1998 | 270048865.167542 JORDAN | 1997 | 468381857.97162 JORDAN | 1996 | 464134404.340258 JORDAN | 1995 | 457449089.595928 JORDAN | 1994 | 464446515.982699 JORDAN | 1993 | 460626968.621293 JORDAN | 1992 | 465316993.161731 KENYA | 1998 | 273301871.050574 KENYA | 1997 | 455101838.650958 KENYA | 1996 | 455031934.905043 KENYA | 1995 | 452556603.990252 KENYA | 1994 | 459745345.368277 KENYA | 1993 | 463968855.540676 KENYA | 1992 | 456440116.674513 MOROCCO | 1998 | 272458034.644922 MOROCCO | 1997 | 452239639.599944 MOROCCO | 1996 | 464297910.082926 MOROCCO | 1995 | 469802344.671981 MOROCCO | 1994 | 472622577.373095 MOROCCO | 1993 | 458328592.847446 MOROCCO | 1992 | 461859746.314658 MOZAMBIQUE | 1998 | 262093433.248685 MOZAMBIQUE | 1997 | 448885814.534609 MOZAMBIQUE | 1996 | 451137761.538763 MOZAMBIQUE | 1995 | 450293335.939202 MOZAMBIQUE | 1994 | 453086480.890861 MOZAMBIQUE | 1993 | 449016510.755812 MOZAMBIQUE | 1992 | 448765860.004755 PERU | 1998 | 272375852.942431 PERU | 1997 | 458938482.86136 PERU | 1996 | 463829533.986609 PERU | 1995 | 456723674.488273 PERU | 1994 | 455043987.664526 PERU | 1993 | 453119748.344905 PERU | 1992 | 454414790.591232 ROMANIA | 1998 | 267228671.669551 ROMANIA | 1997 | 463334463.104699 ROMANIA | 1996 | 467369099.879173 ROMANIA | 1995 | 461623348.88588 ROMANIA | 1994 | 467806439.27567 ROMANIA | 1993 | 456223558.447117 ROMANIA | 1992 | 465491886.623106 RUSSIA | 1998 | 274987735.03158 RUSSIA | 1997 | 461334051.802312 RUSSIA | 1996 | 469716597.567844 RUSSIA | 1995 | 468294786.508681 RUSSIA | 1994 | 467404381.97514 RUSSIA | 1993 | 467481355.657918 RUSSIA | 1992 | 466628755.60995 SAUDI ARABIA | 1998 | 275429034.250407 SAUDI ARABIA | 1997 | 462224679.982283 SAUDI ARABIA | 1996 | 454648202.423589 SAUDI ARABIA | 1995 | 460711610.555345 SAUDI ARABIA | 1994 | 459934406.678007 SAUDI ARABIA | 1993 | 456065947.190363 SAUDI ARABIA | 1992 | 460885837.420041 UNITED KINGDOM | 1998 | 276581864.421671 UNITED KINGDOM | 1997 | 457420825.624838 UNITED KINGDOM | 1996 | 464077171.991697 UNITED KINGDOM | 1995 | 466643827.079929 UNITED KINGDOM | 1994 | 465011168.97992 UNITED KINGDOM | 1993 | 465173474.841268 UNITED KINGDOM | 1992 | 470628753.922486 UNITED STATES | 1998 | 277133288.192248 UNITED STATES | 1997 | 477168962.088223 UNITED STATES | 1996 | 472326782.729073 UNITED STATES | 1995 | 473861339.958928 UNITED STATES | 1994 | 477896792.644209 UNITED STATES | 1993 | 476940321.965185 UNITED STATES | 1992 | 470847092.163504 VIETNAM | 1998 | 267062363.174601 VIETNAM | 1997 | 459343144.301228 VIETNAM | 1996 | 463419903.564328 VIETNAM | 1995 | 448856395.946534 VIETNAM | 1994 | 452628027.339789 VIETNAM | 1993 | 465166908.991095 VIETNAM | 1992 | 454583249.06867 (175 rows) COMMIT; COMMIT