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 '%dark%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265440168.068247 ALGERIA | 1997 | 442255644.467219 ALGERIA | 1996 | 452307490.617423 ALGERIA | 1995 | 446361486.31164 ALGERIA | 1994 | 452201375.669912 ALGERIA | 1993 | 447141300.414561 ALGERIA | 1992 | 452503414.717881 ARGENTINA | 1998 | 270328134.987151 ARGENTINA | 1997 | 466907694.557457 ARGENTINA | 1996 | 468265783.056574 ARGENTINA | 1995 | 461073465.186853 ARGENTINA | 1994 | 466318532.850357 ARGENTINA | 1993 | 473417446.881038 ARGENTINA | 1992 | 462384993.524383 BRAZIL | 1998 | 269236263.964318 BRAZIL | 1997 | 463988547.995372 BRAZIL | 1996 | 469024997.5944 BRAZIL | 1995 | 467321119.165446 BRAZIL | 1994 | 479703244.028346 BRAZIL | 1993 | 467247063.328627 BRAZIL | 1992 | 466808127.515776 CANADA | 1998 | 279329358.324169 CANADA | 1997 | 464599424.562658 CANADA | 1996 | 472883608.59147 CANADA | 1995 | 471363977.461469 CANADA | 1994 | 485728799.399901 CANADA | 1993 | 477028413.204286 CANADA | 1992 | 470852753.349949 CHINA | 1998 | 277933495.865611 CHINA | 1997 | 475121579.080425 CHINA | 1996 | 472434729.786309 CHINA | 1995 | 474003855.446942 CHINA | 1994 | 479929513.609455 CHINA | 1993 | 472794151.52057 CHINA | 1992 | 473919337.343565 EGYPT | 1998 | 272456552.21189 EGYPT | 1997 | 463569607.361453 EGYPT | 1996 | 468438532.365 EGYPT | 1995 | 454843448.425748 EGYPT | 1994 | 454435095.829327 EGYPT | 1993 | 456345140.986282 EGYPT | 1992 | 460878253.751215 ETHIOPIA | 1998 | 268250582.510682 ETHIOPIA | 1997 | 462123689.746127 ETHIOPIA | 1996 | 464440111.374211 ETHIOPIA | 1995 | 461347157.401254 ETHIOPIA | 1994 | 462674661.783297 ETHIOPIA | 1993 | 459357773.98407 ETHIOPIA | 1992 | 465093290.56259 FRANCE | 1998 | 270233565.556831 FRANCE | 1997 | 455081946.248756 FRANCE | 1996 | 458328303.654336 FRANCE | 1995 | 451064289.878125 FRANCE | 1994 | 467420381.08619 FRANCE | 1993 | 454313711.363555 FRANCE | 1992 | 465107694.282985 GERMANY | 1998 | 272202130.093313 GERMANY | 1997 | 472504040.992039 GERMANY | 1996 | 476184387.1593 GERMANY | 1995 | 472037622.034427 GERMANY | 1994 | 468805237.70617 GERMANY | 1993 | 476819903.626665 GERMANY | 1992 | 474983641.234481 INDIA | 1998 | 278724037.552207 INDIA | 1997 | 470413852.388235 INDIA | 1996 | 476096685.43545 INDIA | 1995 | 471470333.434549 INDIA | 1994 | 472835168.982547 INDIA | 1993 | 476266146.700171 INDIA | 1992 | 473820583.218834 INDONESIA | 1998 | 268990807.510078 INDONESIA | 1997 | 456047695.523541 INDONESIA | 1996 | 460266196.489751 INDONESIA | 1995 | 462143394.062649 INDONESIA | 1994 | 457235584.599275 INDONESIA | 1993 | 460364277.921516 INDONESIA | 1992 | 453448110.940453 IRAN | 1998 | 270434197.547999 IRAN | 1997 | 462966216.507741 IRAN | 1996 | 460177180.994989 IRAN | 1995 | 460070026.478205 IRAN | 1994 | 450488182.238383 IRAN | 1993 | 454738363.868179 IRAN | 1992 | 457529449.792639 IRAQ | 1998 | 277883518.068304 IRAQ | 1997 | 472600803.496184 IRAQ | 1996 | 481123587.758188 IRAQ | 1995 | 477992725.738001 IRAQ | 1994 | 477073024.623673 IRAQ | 1993 | 479690347.848932 IRAQ | 1992 | 465575606.384909 JAPAN | 1998 | 273762094.70477 JAPAN | 1997 | 464820900.389463 JAPAN | 1996 | 461875416.408776 JAPAN | 1995 | 468975857.43443 JAPAN | 1994 | 466991854.333219 JAPAN | 1993 | 463034463.677653 JAPAN | 1992 | 457056368.49997 JORDAN | 1998 | 266991664.595653 JORDAN | 1997 | 452825404.491501 JORDAN | 1996 | 461697529.315494 JORDAN | 1995 | 455127833.264502 JORDAN | 1994 | 454878346.975797 JORDAN | 1993 | 462007061.433731 JORDAN | 1992 | 462904968.499412 KENYA | 1998 | 278973477.634087 KENYA | 1997 | 470883473.666857 KENYA | 1996 | 473699966.726436 KENYA | 1995 | 470609353.056932 KENYA | 1994 | 475738123.607215 KENYA | 1993 | 484688778.896237 KENYA | 1992 | 474399787.185056 MOROCCO | 1998 | 269863456.545504 MOROCCO | 1997 | 470210266.787994 MOROCCO | 1996 | 474267321.774624 MOROCCO | 1995 | 466560894.685544 MOROCCO | 1994 | 466688149.791826 MOROCCO | 1993 | 464508643.497447 MOROCCO | 1992 | 476306941.775771 MOZAMBIQUE | 1998 | 273886663.239171 MOZAMBIQUE | 1997 | 457184650.725558 MOZAMBIQUE | 1996 | 462250002.647746 MOZAMBIQUE | 1995 | 461266384.682665 MOZAMBIQUE | 1994 | 460949743.838919 MOZAMBIQUE | 1993 | 451350461.091179 MOZAMBIQUE | 1992 | 455353492.538169 PERU | 1998 | 270998266.09057 PERU | 1997 | 473225093.754647 PERU | 1996 | 462538019.340685 PERU | 1995 | 467170389.740066 PERU | 1994 | 459106223.833311 PERU | 1993 | 467044129.746182 PERU | 1992 | 468835501.631235 ROMANIA | 1998 | 271830001.06685 ROMANIA | 1997 | 466103695.662306 ROMANIA | 1996 | 464164044.282762 ROMANIA | 1995 | 460432224.977536 ROMANIA | 1994 | 468449091.228773 ROMANIA | 1993 | 460093668.797063 ROMANIA | 1992 | 464199678.810508 RUSSIA | 1998 | 274991372.393989 RUSSIA | 1997 | 458010613.491406 RUSSIA | 1996 | 467754285.016753 RUSSIA | 1995 | 458956388.227816 RUSSIA | 1994 | 460404688.704805 RUSSIA | 1993 | 458647194.557731 RUSSIA | 1992 | 468831857.256983 SAUDI ARABIA | 1998 | 281102264.598379 SAUDI ARABIA | 1997 | 470316661.684358 SAUDI ARABIA | 1996 | 477278219.74234 SAUDI ARABIA | 1995 | 482084137.447531 SAUDI ARABIA | 1994 | 471793908.809895 SAUDI ARABIA | 1993 | 479132914.3709 SAUDI ARABIA | 1992 | 481342827.03528 UNITED KINGDOM | 1998 | 266280871.626112 UNITED KINGDOM | 1997 | 461473116.534575 UNITED KINGDOM | 1996 | 460565585.719059 UNITED KINGDOM | 1995 | 449739630.684143 UNITED KINGDOM | 1994 | 453339628.576329 UNITED KINGDOM | 1993 | 457767554.954394 UNITED KINGDOM | 1992 | 462476411.008887 UNITED STATES | 1998 | 271134147.063033 UNITED STATES | 1997 | 472413637.437855 UNITED STATES | 1996 | 468278521.104292 UNITED STATES | 1995 | 467469002.101203 UNITED STATES | 1994 | 478064755.792787 UNITED STATES | 1993 | 469484575.014707 UNITED STATES | 1992 | 470921912.187482 VIETNAM | 1998 | 270116312.763043 VIETNAM | 1997 | 463339454.273101 VIETNAM | 1996 | 468052541.301117 VIETNAM | 1995 | 461485268.534453 VIETNAM | 1994 | 460896768.437238 VIETNAM | 1993 | 457764278.790338 VIETNAM | 1992 | 455409187.569879 (175 rows) COMMIT; COMMIT