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 | 266906262.602214 ALGERIA | 1997 | 457717316.326579 ALGERIA | 1996 | 455887169.998559 ALGERIA | 1995 | 459305999.035544 ALGERIA | 1994 | 457351242.561739 ALGERIA | 1993 | 462097146.300108 ALGERIA | 1992 | 464591231.122601 ARGENTINA | 1998 | 269265669.361393 ARGENTINA | 1997 | 469048328.687571 ARGENTINA | 1996 | 470754641.783041 ARGENTINA | 1995 | 472501575.79346 ARGENTINA | 1994 | 469984935.693939 ARGENTINA | 1993 | 466299728.493684 ARGENTINA | 1992 | 474933770.813192 BRAZIL | 1998 | 269905875.354283 BRAZIL | 1997 | 468963452.208587 BRAZIL | 1996 | 465525052.7452 BRAZIL | 1995 | 468435979.779777 BRAZIL | 1994 | 469153839.675727 BRAZIL | 1993 | 468390142.314781 BRAZIL | 1992 | 463285027.661831 CANADA | 1998 | 272599377.497415 CANADA | 1997 | 474239741.651506 CANADA | 1996 | 479471028.556702 CANADA | 1995 | 467629420.601238 CANADA | 1994 | 475104485.75761 CANADA | 1993 | 468593313.205064 CANADA | 1992 | 469086042.331965 CHINA | 1998 | 267086597.060716 CHINA | 1997 | 471330599.551219 CHINA | 1996 | 462343729.71893 CHINA | 1995 | 461437098.291 CHINA | 1994 | 460174645.641318 CHINA | 1993 | 464606097.902064 CHINA | 1992 | 462450757.223753 EGYPT | 1998 | 274399818.752469 EGYPT | 1997 | 472512491.838464 EGYPT | 1996 | 461648670.218196 EGYPT | 1995 | 453278351.27899 EGYPT | 1994 | 456718113.036549 EGYPT | 1993 | 466322114.338304 EGYPT | 1992 | 462452591.250614 ETHIOPIA | 1998 | 271230495.910719 ETHIOPIA | 1997 | 464225595.868794 ETHIOPIA | 1996 | 466104945.001179 ETHIOPIA | 1995 | 468363037.2691 ETHIOPIA | 1994 | 462435053.694741 ETHIOPIA | 1993 | 465438273.778593 ETHIOPIA | 1992 | 457971611.139702 FRANCE | 1998 | 267299067.16675 FRANCE | 1997 | 459948903.817021 FRANCE | 1996 | 460985398.722958 FRANCE | 1995 | 469488004.138479 FRANCE | 1994 | 460766276.623166 FRANCE | 1993 | 456348120.090066 FRANCE | 1992 | 470343508.317173 GERMANY | 1998 | 278646942.313381 GERMANY | 1997 | 477629694.973353 GERMANY | 1996 | 463117784.415954 GERMANY | 1995 | 473769884.314709 GERMANY | 1994 | 475120296.920781 GERMANY | 1993 | 477099215.880918 GERMANY | 1992 | 478193640.29374 INDIA | 1998 | 277743588.106794 INDIA | 1997 | 467264446.422024 INDIA | 1996 | 472985788.639368 INDIA | 1995 | 474854045.939358 INDIA | 1994 | 473063097.258442 INDIA | 1993 | 470369869.999679 INDIA | 1992 | 473249429.149597 INDONESIA | 1998 | 268878028.25654 INDONESIA | 1997 | 451758365.176776 INDONESIA | 1996 | 459635492.119106 INDONESIA | 1995 | 461800192.765678 INDONESIA | 1994 | 462197338.084429 INDONESIA | 1993 | 469516760.208589 INDONESIA | 1992 | 463516457.556206 IRAN | 1998 | 271476301.500087 IRAN | 1997 | 457592575.587697 IRAN | 1996 | 461459882.131436 IRAN | 1995 | 465126039.966613 IRAN | 1994 | 460911396.029123 IRAN | 1993 | 472751623.250777 IRAN | 1992 | 470878764.212117 IRAQ | 1998 | 274884632.568925 IRAQ | 1997 | 464119122.192092 IRAQ | 1996 | 474607026.331761 IRAQ | 1995 | 472772775.133132 IRAQ | 1994 | 470507844.449598 IRAQ | 1993 | 463627169.458585 IRAQ | 1992 | 464730346.341926 JAPAN | 1998 | 271861325.822364 JAPAN | 1997 | 459719027.056029 JAPAN | 1996 | 463737033.189414 JAPAN | 1995 | 463481988.807371 JAPAN | 1994 | 459486704.753043 JAPAN | 1993 | 464680708.406282 JAPAN | 1992 | 465734027.724145 JORDAN | 1998 | 266766975.103457 JORDAN | 1997 | 456057072.458493 JORDAN | 1996 | 455298635.74217 JORDAN | 1995 | 461553613.436458 JORDAN | 1994 | 457274088.720396 JORDAN | 1993 | 459645162.452832 JORDAN | 1992 | 453520442.68018 KENYA | 1998 | 276893141.358352 KENYA | 1997 | 465003152.462206 KENYA | 1996 | 457715830.893086 KENYA | 1995 | 467900104.738848 KENYA | 1994 | 459122301.522004 KENYA | 1993 | 464359590.200322 KENYA | 1992 | 465834794.434391 MOROCCO | 1998 | 275739675.535387 MOROCCO | 1997 | 476573122.540966 MOROCCO | 1996 | 475526444.341885 MOROCCO | 1995 | 476854023.276369 MOROCCO | 1994 | 472183261.116246 MOROCCO | 1993 | 474423948.58437 MOROCCO | 1992 | 478868849.260863 MOZAMBIQUE | 1998 | 272064838.309916 MOZAMBIQUE | 1997 | 460286905.147964 MOZAMBIQUE | 1996 | 462091328.910967 MOZAMBIQUE | 1995 | 456836987.617554 MOZAMBIQUE | 1994 | 459057715.533044 MOZAMBIQUE | 1993 | 458838511.057541 MOZAMBIQUE | 1992 | 459883676.211608 PERU | 1998 | 266385912.19053 PERU | 1997 | 454091398.114655 PERU | 1996 | 465038659.604609 PERU | 1995 | 467792149.570162 PERU | 1994 | 465054874.707702 PERU | 1993 | 460884514.238808 PERU | 1992 | 466018107.40827 ROMANIA | 1998 | 276137306.86768 ROMANIA | 1997 | 462004923.843334 ROMANIA | 1996 | 472508147.870319 ROMANIA | 1995 | 464436169.59977 ROMANIA | 1994 | 465520611.019002 ROMANIA | 1993 | 462590054.518217 ROMANIA | 1992 | 462738059.327718 RUSSIA | 1998 | 270465464.036888 RUSSIA | 1997 | 463231487.665685 RUSSIA | 1996 | 470106736.467284 RUSSIA | 1995 | 455623254.684846 RUSSIA | 1994 | 466477784.935631 RUSSIA | 1993 | 460192511.565989 RUSSIA | 1992 | 471747017.530512 SAUDI ARABIA | 1998 | 269878066.114587 SAUDI ARABIA | 1997 | 466090635.059338 SAUDI ARABIA | 1996 | 474093605.641021 SAUDI ARABIA | 1995 | 468876460.577663 SAUDI ARABIA | 1994 | 468600756.870496 SAUDI ARABIA | 1993 | 469096366.885599 SAUDI ARABIA | 1992 | 468401867.602179 UNITED KINGDOM | 1998 | 269097729.193921 UNITED KINGDOM | 1997 | 455397563.338499 UNITED KINGDOM | 1996 | 459400973.809146 UNITED KINGDOM | 1995 | 449684937.077753 UNITED KINGDOM | 1994 | 452736618.864114 UNITED KINGDOM | 1993 | 451462145.499989 UNITED KINGDOM | 1992 | 456286299.839552 UNITED STATES | 1998 | 276243755.944063 UNITED STATES | 1997 | 468300377.206756 UNITED STATES | 1996 | 475299619.736299 UNITED STATES | 1995 | 467917940.067807 UNITED STATES | 1994 | 471307970.534781 UNITED STATES | 1993 | 464667317.27275 UNITED STATES | 1992 | 466527249.153076 VIETNAM | 1998 | 262853957.943138 VIETNAM | 1997 | 454987456.622262 VIETNAM | 1996 | 456704572.639087 VIETNAM | 1995 | 458397052.60925 VIETNAM | 1994 | 458243326.771699 VIETNAM | 1993 | 471813569.631074 VIETNAM | 1992 | 463453773.221253 (175 rows) COMMIT; COMMIT