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 '%chocolate%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272915269.06679 ALGERIA | 1997 | 471115636.340581 ALGERIA | 1996 | 468250004.244883 ALGERIA | 1995 | 455566276.288207 ALGERIA | 1994 | 461718977.620885 ALGERIA | 1993 | 457773844.169929 ALGERIA | 1992 | 459507182.509396 ARGENTINA | 1998 | 274336832.46555 ARGENTINA | 1997 | 470103565.036132 ARGENTINA | 1996 | 472398740.210213 ARGENTINA | 1995 | 473306746.1068 ARGENTINA | 1994 | 473175106.202939 ARGENTINA | 1993 | 472003899.347066 ARGENTINA | 1992 | 469244228.799489 BRAZIL | 1998 | 279306365.807282 BRAZIL | 1997 | 473897247.319595 BRAZIL | 1996 | 468492918.75892 BRAZIL | 1995 | 477104199.973716 BRAZIL | 1994 | 476667272.15487 BRAZIL | 1993 | 468776979.713544 BRAZIL | 1992 | 466139052.933796 CANADA | 1998 | 273459288.647465 CANADA | 1997 | 468360111.33068 CANADA | 1996 | 471909854.945756 CANADA | 1995 | 473347007.159129 CANADA | 1994 | 478585015.797432 CANADA | 1993 | 475394791.526446 CANADA | 1992 | 479708810.826936 CHINA | 1998 | 276692750.130414 CHINA | 1997 | 467262315.117573 CHINA | 1996 | 473818263.421437 CHINA | 1995 | 474885546.283509 CHINA | 1994 | 469088101.633449 CHINA | 1993 | 467751023.593408 CHINA | 1992 | 477345117.278473 EGYPT | 1998 | 274052366.112994 EGYPT | 1997 | 463574913.790088 EGYPT | 1996 | 457232248.871175 EGYPT | 1995 | 466219749.928668 EGYPT | 1994 | 466292834.135139 EGYPT | 1993 | 467107081.698842 EGYPT | 1992 | 465750425.202936 ETHIOPIA | 1998 | 272354869.264028 ETHIOPIA | 1997 | 455048838.4161 ETHIOPIA | 1996 | 459118307.801954 ETHIOPIA | 1995 | 463353182.977589 ETHIOPIA | 1994 | 465862643.619948 ETHIOPIA | 1993 | 461554747.688156 ETHIOPIA | 1992 | 461797264.214669 FRANCE | 1998 | 269217389.859482 FRANCE | 1997 | 458584847.24097 FRANCE | 1996 | 460242337.231083 FRANCE | 1995 | 454824717.655086 FRANCE | 1994 | 459368640.727923 FRANCE | 1993 | 457386934.963771 FRANCE | 1992 | 458755395.636162 GERMANY | 1998 | 280436068.443555 GERMANY | 1997 | 478756273.1062 GERMANY | 1996 | 472976939.869994 GERMANY | 1995 | 488598831.617412 GERMANY | 1994 | 481930326.456301 GERMANY | 1993 | 478184979.895175 GERMANY | 1992 | 482959397.631032 INDIA | 1998 | 276949087.938912 INDIA | 1997 | 474865102.388846 INDIA | 1996 | 479204863.3025 INDIA | 1995 | 478906643.236126 INDIA | 1994 | 474856411.569472 INDIA | 1993 | 478646458.022698 INDIA | 1992 | 482968524.560225 INDONESIA | 1998 | 273579873.755563 INDONESIA | 1997 | 457163229.906248 INDONESIA | 1996 | 467827193.197915 INDONESIA | 1995 | 469212717.185881 INDONESIA | 1994 | 460313725.565899 INDONESIA | 1993 | 474423733.621889 INDONESIA | 1992 | 472315063.376292 IRAN | 1998 | 275281474.734023 IRAN | 1997 | 471273625.752017 IRAN | 1996 | 466346238.504284 IRAN | 1995 | 464551085.272865 IRAN | 1994 | 469863394.757836 IRAN | 1993 | 469275018.348652 IRAN | 1992 | 468945426.661045 IRAQ | 1998 | 276078656.53012 IRAQ | 1997 | 484220511.18122 IRAQ | 1996 | 477255830.319077 IRAQ | 1995 | 472742712.263726 IRAQ | 1994 | 472555552.570649 IRAQ | 1993 | 477674110.606861 IRAQ | 1992 | 473664864.951598 JAPAN | 1998 | 270725030.086279 JAPAN | 1997 | 471546510.182702 JAPAN | 1996 | 470154229.008218 JAPAN | 1995 | 463665083.155255 JAPAN | 1994 | 458607424.997286 JAPAN | 1993 | 469157779.844183 JAPAN | 1992 | 462554247.554669 JORDAN | 1998 | 274024974.237891 JORDAN | 1997 | 465855184.573568 JORDAN | 1996 | 458027581.260721 JORDAN | 1995 | 462984393.210766 JORDAN | 1994 | 467258053.327367 JORDAN | 1993 | 472466347.592902 JORDAN | 1992 | 461689489.148959 KENYA | 1998 | 277750886.411321 KENYA | 1997 | 475094932.408045 KENYA | 1996 | 477664145.250973 KENYA | 1995 | 479408743.963577 KENYA | 1994 | 468251630.856378 KENYA | 1993 | 465996992.415771 KENYA | 1992 | 477952243.455656 MOROCCO | 1998 | 281676863.73732 MOROCCO | 1997 | 467982230.395592 MOROCCO | 1996 | 469267217.644473 MOROCCO | 1995 | 475572956.437605 MOROCCO | 1994 | 474569561.795204 MOROCCO | 1993 | 466210464.532954 MOROCCO | 1992 | 467270206.325028 MOZAMBIQUE | 1998 | 271698939.008694 MOZAMBIQUE | 1997 | 465017110.080064 MOZAMBIQUE | 1996 | 462681271.373141 MOZAMBIQUE | 1995 | 458425387.174388 MOZAMBIQUE | 1994 | 464332789.354355 MOZAMBIQUE | 1993 | 462699058.919809 MOZAMBIQUE | 1992 | 460132901.062505 PERU | 1998 | 273661169.383798 PERU | 1997 | 466741729.345633 PERU | 1996 | 468399938.500947 PERU | 1995 | 466194713.680931 PERU | 1994 | 469162616.499288 PERU | 1993 | 470110917.461794 PERU | 1992 | 470156116.261945 ROMANIA | 1998 | 276583523.198053 ROMANIA | 1997 | 476628925.354773 ROMANIA | 1996 | 482688205.643948 ROMANIA | 1995 | 465926281.217036 ROMANIA | 1994 | 464373133.148448 ROMANIA | 1993 | 466928572.415423 ROMANIA | 1992 | 470388872.045114 RUSSIA | 1998 | 279933965.719188 RUSSIA | 1997 | 474922261.945801 RUSSIA | 1996 | 472909724.620777 RUSSIA | 1995 | 477696811.566294 RUSSIA | 1994 | 469603507.118888 RUSSIA | 1993 | 479968853.848587 RUSSIA | 1992 | 473218186.246827 SAUDI ARABIA | 1998 | 279388119.773283 SAUDI ARABIA | 1997 | 470931262.52272 SAUDI ARABIA | 1996 | 472309598.362223 SAUDI ARABIA | 1995 | 467825104.091601 SAUDI ARABIA | 1994 | 467545441.257626 SAUDI ARABIA | 1993 | 468797234.960977 SAUDI ARABIA | 1992 | 468130055.613784 UNITED KINGDOM | 1998 | 264344229.119917 UNITED KINGDOM | 1997 | 457711919.707476 UNITED KINGDOM | 1996 | 465988279.35036 UNITED KINGDOM | 1995 | 459054587.402179 UNITED KINGDOM | 1994 | 461969898.34408 UNITED KINGDOM | 1993 | 458830414.210602 UNITED KINGDOM | 1992 | 459509989.222531 UNITED STATES | 1998 | 284810018.608523 UNITED STATES | 1997 | 470326004.29654 UNITED STATES | 1996 | 477119387.425403 UNITED STATES | 1995 | 473971759.814697 UNITED STATES | 1994 | 479824460.959402 UNITED STATES | 1993 | 472616727.332618 UNITED STATES | 1992 | 476221990.612297 VIETNAM | 1998 | 271135579.406756 VIETNAM | 1997 | 465496934.332083 VIETNAM | 1996 | 478542934.555797 VIETNAM | 1995 | 463877829.698649 VIETNAM | 1994 | 470320965.108422 VIETNAM | 1993 | 474463723.754141 VIETNAM | 1992 | 468733090.363326 (175 rows) COMMIT; COMMIT