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 '%smoke%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 266930667.887754 ALGERIA | 1997 | 457397655.95137 ALGERIA | 1996 | 456639502.485402 ALGERIA | 1995 | 458421241.171205 ALGERIA | 1994 | 457094591.91262 ALGERIA | 1993 | 461559524.697993 ALGERIA | 1992 | 463341872.06109 ARGENTINA | 1998 | 269763927.710345 ARGENTINA | 1997 | 467832062.101671 ARGENTINA | 1996 | 470487444.299439 ARGENTINA | 1995 | 472034805.67337 ARGENTINA | 1994 | 469722999.554384 ARGENTINA | 1993 | 465341722.075735 ARGENTINA | 1992 | 475132545.306207 BRAZIL | 1998 | 269871669.392345 BRAZIL | 1997 | 468753364.036649 BRAZIL | 1996 | 465381742.973675 BRAZIL | 1995 | 468269629.827816 BRAZIL | 1994 | 468941510.95368 BRAZIL | 1993 | 468524813.864423 BRAZIL | 1992 | 462297933.081713 CANADA | 1998 | 272751153.344613 CANADA | 1997 | 473719545.119854 CANADA | 1996 | 479048548.004281 CANADA | 1995 | 466732428.315916 CANADA | 1994 | 475206461.283923 CANADA | 1993 | 468623920.172357 CANADA | 1992 | 468265333.227566 CHINA | 1998 | 266679251.447036 CHINA | 1997 | 471372082.061785 CHINA | 1996 | 461573638.091718 CHINA | 1995 | 460761848.88459 CHINA | 1994 | 459942109.36039 CHINA | 1993 | 464015570.007203 CHINA | 1992 | 463227437.633445 EGYPT | 1998 | 274548071.736595 EGYPT | 1997 | 471282761.765689 EGYPT | 1996 | 460410436.502238 EGYPT | 1995 | 452539159.147876 EGYPT | 1994 | 455802176.830349 EGYPT | 1993 | 465962629.323044 EGYPT | 1992 | 461330090.953214 ETHIOPIA | 1998 | 271483138.418159 ETHIOPIA | 1997 | 463800649.434583 ETHIOPIA | 1996 | 465075075.195275 ETHIOPIA | 1995 | 467850401.43607 ETHIOPIA | 1994 | 462106886.334253 ETHIOPIA | 1993 | 464603969.80994 ETHIOPIA | 1992 | 457262326.004104 FRANCE | 1998 | 267220150.124838 FRANCE | 1997 | 460055881.516575 FRANCE | 1996 | 460457565.604865 FRANCE | 1995 | 468464906.553225 FRANCE | 1994 | 460120894.075422 FRANCE | 1993 | 456057872.16732 FRANCE | 1992 | 470357641.312063 GERMANY | 1998 | 278283822.36535 GERMANY | 1997 | 476556921.233086 GERMANY | 1996 | 462459785.656495 GERMANY | 1995 | 473105494.816707 GERMANY | 1994 | 474392229.575635 GERMANY | 1993 | 476060340.254032 GERMANY | 1992 | 477833170.305974 INDIA | 1998 | 278488291.753061 INDIA | 1997 | 466723102.852598 INDIA | 1996 | 471999802.865131 INDIA | 1995 | 474848895.231145 INDIA | 1994 | 473067933.471429 INDIA | 1993 | 469754407.954909 INDIA | 1992 | 472353042.154437 INDONESIA | 1998 | 269108180.195847 INDONESIA | 1997 | 451250851.479046 INDONESIA | 1996 | 458947262.029898 INDONESIA | 1995 | 461026927.32643 INDONESIA | 1994 | 462522616.308882 INDONESIA | 1993 | 469116773.469741 INDONESIA | 1992 | 462831239.238849 IRAN | 1998 | 271213777.191447 IRAN | 1997 | 457500475.000089 IRAN | 1996 | 460854671.543401 IRAN | 1995 | 464527993.300142 IRAN | 1994 | 461334492.871366 IRAN | 1993 | 472011316.694917 IRAN | 1992 | 469621786.294054 IRAQ | 1998 | 274218850.886675 IRAQ | 1997 | 464163615.732236 IRAQ | 1996 | 474457969.75967 IRAQ | 1995 | 472537696.824878 IRAQ | 1994 | 469904768.968944 IRAQ | 1993 | 462195587.971727 IRAQ | 1992 | 464313874.028787 JAPAN | 1998 | 271608818.562929 JAPAN | 1997 | 459263774.780661 JAPAN | 1996 | 463850194.338701 JAPAN | 1995 | 463452049.987586 JAPAN | 1994 | 458079454.379215 JAPAN | 1993 | 463520028.817034 JAPAN | 1992 | 464537405.94952 JORDAN | 1998 | 266203865.897498 JORDAN | 1997 | 455277939.353448 JORDAN | 1996 | 455372925.012968 JORDAN | 1995 | 461255655.28008 JORDAN | 1994 | 456704613.480715 JORDAN | 1993 | 459038567.773405 JORDAN | 1992 | 452899338.811278 KENYA | 1998 | 277053437.91213 KENYA | 1997 | 464826257.028104 KENYA | 1996 | 457641682.941383 KENYA | 1995 | 466959111.661418 KENYA | 1994 | 457802047.540902 KENYA | 1993 | 464513756.379006 KENYA | 1992 | 465904794.275455 MOROCCO | 1998 | 275474598.804715 MOROCCO | 1997 | 476303063.603236 MOROCCO | 1996 | 474294584.969454 MOROCCO | 1995 | 476889937.449272 MOROCCO | 1994 | 472143587.518085 MOROCCO | 1993 | 473870034.587845 MOROCCO | 1992 | 478396740.731955 MOZAMBIQUE | 1998 | 272011091.782543 MOZAMBIQUE | 1997 | 458739631.118886 MOZAMBIQUE | 1996 | 461760481.95104 MOZAMBIQUE | 1995 | 456636299.420358 MOZAMBIQUE | 1994 | 457504398.651665 MOZAMBIQUE | 1993 | 458794772.788871 MOZAMBIQUE | 1992 | 459633299.442568 PERU | 1998 | 266419190.205638 PERU | 1997 | 453240205.708757 PERU | 1996 | 464962964.941184 PERU | 1995 | 468403552.851506 PERU | 1994 | 464504743.609301 PERU | 1993 | 459550947.481433 PERU | 1992 | 465743683.958407 ROMANIA | 1998 | 275609639.028896 ROMANIA | 1997 | 462283828.37735 ROMANIA | 1996 | 472158105.100425 ROMANIA | 1995 | 464314413.379777 ROMANIA | 1994 | 465800164.25514 ROMANIA | 1993 | 462178016.890017 ROMANIA | 1992 | 462290305.600344 RUSSIA | 1998 | 270497213.946179 RUSSIA | 1997 | 462966934.24886 RUSSIA | 1996 | 469428913.343913 RUSSIA | 1995 | 454944040.003132 RUSSIA | 1994 | 465892388.083993 RUSSIA | 1993 | 459751060.870531 RUSSIA | 1992 | 471105520.146311 SAUDI ARABIA | 1998 | 269554630.103436 SAUDI ARABIA | 1997 | 465480588.357766 SAUDI ARABIA | 1996 | 472749529.670108 SAUDI ARABIA | 1995 | 468475062.688083 SAUDI ARABIA | 1994 | 467271973.876364 SAUDI ARABIA | 1993 | 468576288.477059 SAUDI ARABIA | 1992 | 467769710.972884 UNITED KINGDOM | 1998 | 269143011.813794 UNITED KINGDOM | 1997 | 454936604.554235 UNITED KINGDOM | 1996 | 458800413.252854 UNITED KINGDOM | 1995 | 449547305.404737 UNITED KINGDOM | 1994 | 453093728.730172 UNITED KINGDOM | 1993 | 451386371.06792 UNITED KINGDOM | 1992 | 456086788.895634 UNITED STATES | 1998 | 276255555.918319 UNITED STATES | 1997 | 468116465.348041 UNITED STATES | 1996 | 474262858.867149 UNITED STATES | 1995 | 467867911.58881 UNITED STATES | 1994 | 471554653.192675 UNITED STATES | 1993 | 464651888.033064 UNITED STATES | 1992 | 466386833.200212 VIETNAM | 1998 | 262884759.12275 VIETNAM | 1997 | 455282173.099882 VIETNAM | 1996 | 455979537.803717 VIETNAM | 1995 | 457737632.028766 VIETNAM | 1994 | 457599989.977726 VIETNAM | 1993 | 470811096.394275 VIETNAM | 1992 | 463589336.916444 (175 rows) COMMIT; COMMIT