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 | 273087286.22338 ALGERIA | 1997 | 471481561.916526 ALGERIA | 1996 | 468719527.61724 ALGERIA | 1995 | 456175579.483454 ALGERIA | 1994 | 462234818.284615 ALGERIA | 1993 | 458102879.071584 ALGERIA | 1992 | 459751324.757245 ARGENTINA | 1998 | 274566590.986221 ARGENTINA | 1997 | 470652782.905349 ARGENTINA | 1996 | 472876855.710937 ARGENTINA | 1995 | 473813366.232259 ARGENTINA | 1994 | 473583323.331658 ARGENTINA | 1993 | 472685575.525676 ARGENTINA | 1992 | 469742229.21704 BRAZIL | 1998 | 279582552.427316 BRAZIL | 1997 | 474109085.663275 BRAZIL | 1996 | 468816977.110904 BRAZIL | 1995 | 477651095.491819 BRAZIL | 1994 | 477247566.766889 BRAZIL | 1993 | 469355841.593714 BRAZIL | 1992 | 466708999.560386 CANADA | 1998 | 273823731.987494 CANADA | 1997 | 468842804.187517 CANADA | 1996 | 472549667.673019 CANADA | 1995 | 473648938.393267 CANADA | 1994 | 479053520.686854 CANADA | 1993 | 475963911.645017 CANADA | 1992 | 480074196.165984 CHINA | 1998 | 277221424.497851 CHINA | 1997 | 467966457.491832 CHINA | 1996 | 474225475.681678 CHINA | 1995 | 475433600.08661 CHINA | 1994 | 469559099.488696 CHINA | 1993 | 468336425.072977 CHINA | 1992 | 477691885.806596 EGYPT | 1998 | 274320548.949903 EGYPT | 1997 | 464003626.908243 EGYPT | 1996 | 457550603.85884 EGYPT | 1995 | 466861072.842325 EGYPT | 1994 | 466855456.614362 EGYPT | 1993 | 467602484.373451 EGYPT | 1992 | 466124325.016136 ETHIOPIA | 1998 | 272683113.919248 ETHIOPIA | 1997 | 455257505.441328 ETHIOPIA | 1996 | 459618470.282382 ETHIOPIA | 1995 | 463930445.779174 ETHIOPIA | 1994 | 466127037.034978 ETHIOPIA | 1993 | 461963393.321218 ETHIOPIA | 1992 | 462240909.469871 FRANCE | 1998 | 269606603.991422 FRANCE | 1997 | 458809827.31607 FRANCE | 1996 | 460611731.932448 FRANCE | 1995 | 455230549.66385 FRANCE | 1994 | 460014554.535161 FRANCE | 1993 | 457867518.085058 FRANCE | 1992 | 459388198.27832 GERMANY | 1998 | 280786905.851845 GERMANY | 1997 | 479135405.756145 GERMANY | 1996 | 473702970.072331 GERMANY | 1995 | 489251195.7741 GERMANY | 1994 | 482502278.940642 GERMANY | 1993 | 478622899.571419 GERMANY | 1992 | 483410239.90829 INDIA | 1998 | 277296281.490489 INDIA | 1997 | 475369493.393915 INDIA | 1996 | 479470832.703953 INDIA | 1995 | 479391104.612313 INDIA | 1994 | 475466921.090086 INDIA | 1993 | 478914948.123257 INDIA | 1992 | 483540151.840645 INDONESIA | 1998 | 273777401.05898 INDONESIA | 1997 | 457683802.070017 INDONESIA | 1996 | 468263346.940547 INDONESIA | 1995 | 469863015.105412 INDONESIA | 1994 | 460611335.355857 INDONESIA | 1993 | 474760988.115072 INDONESIA | 1992 | 472512042.156563 IRAN | 1998 | 275639325.752272 IRAN | 1997 | 472000683.484543 IRAN | 1996 | 466998478.714069 IRAN | 1995 | 465188680.668901 IRAN | 1994 | 470344337.740653 IRAN | 1993 | 469796852.710752 IRAN | 1992 | 469394344.41885 IRAQ | 1998 | 276317032.022359 IRAQ | 1997 | 484856076.404606 IRAQ | 1996 | 477757363.818203 IRAQ | 1995 | 473156688.006295 IRAQ | 1994 | 473155689.6156 IRAQ | 1993 | 478099858.699054 IRAQ | 1992 | 474069082.949226 JAPAN | 1998 | 270997055.585783 JAPAN | 1997 | 472022384.609365 JAPAN | 1996 | 470742860.321529 JAPAN | 1995 | 464256487.248566 JAPAN | 1994 | 459201364.03963 JAPAN | 1993 | 469585502.973042 JAPAN | 1992 | 463082849.975795 JORDAN | 1998 | 274204665.051563 JORDAN | 1997 | 466390784.423114 JORDAN | 1996 | 458758027.786542 JORDAN | 1995 | 463336170.004952 JORDAN | 1994 | 467803217.640562 JORDAN | 1993 | 472981057.001026 JORDAN | 1992 | 462088299.218617 KENYA | 1998 | 278061058.422457 KENYA | 1997 | 475355263.663875 KENYA | 1996 | 478152440.073855 KENYA | 1995 | 479870129.321621 KENYA | 1994 | 468768263.639986 KENYA | 1993 | 466410050.949337 KENYA | 1992 | 478551911.217538 MOROCCO | 1998 | 281990763.115278 MOROCCO | 1997 | 468549005.217681 MOROCCO | 1996 | 469846679.152194 MOROCCO | 1995 | 475990104.567341 MOROCCO | 1994 | 475135931.866754 MOROCCO | 1993 | 466585290.174727 MOROCCO | 1992 | 467918112.704105 MOZAMBIQUE | 1998 | 271910083.420721 MOZAMBIQUE | 1997 | 465460338.599741 MOZAMBIQUE | 1996 | 463104862.388396 MOZAMBIQUE | 1995 | 459206626.197348 MOZAMBIQUE | 1994 | 464724693.443646 MOZAMBIQUE | 1993 | 463291861.226122 MOZAMBIQUE | 1992 | 460687863.616347 PERU | 1998 | 273846626.503406 PERU | 1997 | 467414287.180826 PERU | 1996 | 468699540.871698 PERU | 1995 | 466496189.739406 PERU | 1994 | 469697707.012735 PERU | 1993 | 470729202.047011 PERU | 1992 | 470414293.103814 ROMANIA | 1998 | 276765039.876903 ROMANIA | 1997 | 477131148.63013 ROMANIA | 1996 | 482906642.190964 ROMANIA | 1995 | 466285918.736128 ROMANIA | 1994 | 464735841.6753 ROMANIA | 1993 | 467487579.459447 ROMANIA | 1992 | 470871985.741688 RUSSIA | 1998 | 280098801.315204 RUSSIA | 1997 | 475309148.662049 RUSSIA | 1996 | 473160411.396533 RUSSIA | 1995 | 478383876.376505 RUSSIA | 1994 | 470088985.13807 RUSSIA | 1993 | 480318755.0792 RUSSIA | 1992 | 474136422.224384 SAUDI ARABIA | 1998 | 279707948.961469 SAUDI ARABIA | 1997 | 471450309.957759 SAUDI ARABIA | 1996 | 472868027.863122 SAUDI ARABIA | 1995 | 468277968.90919 SAUDI ARABIA | 1994 | 468263349.500989 SAUDI ARABIA | 1993 | 469109211.609447 SAUDI ARABIA | 1992 | 468634284.41838 UNITED KINGDOM | 1998 | 264459675.053093 UNITED KINGDOM | 1997 | 458050385.237972 UNITED KINGDOM | 1996 | 466407838.235048 UNITED KINGDOM | 1995 | 459559882.671393 UNITED KINGDOM | 1994 | 462483479.581995 UNITED KINGDOM | 1993 | 459332430.240795 UNITED KINGDOM | 1992 | 459839520.580304 UNITED STATES | 1998 | 285188532.783324 UNITED STATES | 1997 | 470914863.627785 UNITED STATES | 1996 | 477637626.684223 UNITED STATES | 1995 | 474490525.370066 UNITED STATES | 1994 | 480216776.64162 UNITED STATES | 1993 | 473130976.899486 UNITED STATES | 1992 | 476513975.330301 VIETNAM | 1998 | 271463597.587134 VIETNAM | 1997 | 466089500.392984 VIETNAM | 1996 | 478837202.568516 VIETNAM | 1995 | 464066611.536323 VIETNAM | 1994 | 470745238.943862 VIETNAM | 1993 | 474823594.819375 VIETNAM | 1992 | 469361942.339881 (175 rows) COMMIT; COMMIT