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 '%cream%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264227699.201844 ALGERIA | 1997 | 452758229.621017 ALGERIA | 1996 | 455494550.052106 ALGERIA | 1995 | 455579402.57007 ALGERIA | 1994 | 454558456.307088 ALGERIA | 1993 | 456041805.301401 ALGERIA | 1992 | 457211636.869553 ARGENTINA | 1998 | 267831073.06463 ARGENTINA | 1997 | 468732328.282539 ARGENTINA | 1996 | 468106019.130412 ARGENTINA | 1995 | 467939558.640975 ARGENTINA | 1994 | 463556956.880271 ARGENTINA | 1993 | 464668401.259462 ARGENTINA | 1992 | 466006726.697165 BRAZIL | 1998 | 269115561.092579 BRAZIL | 1997 | 475664885.120159 BRAZIL | 1996 | 470399219.571554 BRAZIL | 1995 | 467407402.753143 BRAZIL | 1994 | 469668237.268993 BRAZIL | 1993 | 472270277.105248 BRAZIL | 1992 | 470523163.519767 CANADA | 1998 | 279643107.719172 CANADA | 1997 | 477401952.003294 CANADA | 1996 | 487943734.627737 CANADA | 1995 | 483251443.243124 CANADA | 1994 | 484190176.34061 CANADA | 1993 | 472344933.652819 CANADA | 1992 | 484813711.172352 CHINA | 1998 | 275015295.973226 CHINA | 1997 | 464784221.302153 CHINA | 1996 | 466712533.055073 CHINA | 1995 | 462729798.661831 CHINA | 1994 | 467192896.522398 CHINA | 1993 | 468851106.384786 CHINA | 1992 | 462411228.174637 EGYPT | 1998 | 270102899.251116 EGYPT | 1997 | 460797053.812501 EGYPT | 1996 | 456122229.67009 EGYPT | 1995 | 467834526.685529 EGYPT | 1994 | 459256098.635871 EGYPT | 1993 | 459510544.378693 EGYPT | 1992 | 456971804.244936 ETHIOPIA | 1998 | 273883596.401388 ETHIOPIA | 1997 | 449292381.268202 ETHIOPIA | 1996 | 456563265.147196 ETHIOPIA | 1995 | 468139181.857792 ETHIOPIA | 1994 | 455149233.823451 ETHIOPIA | 1993 | 460835571.602133 ETHIOPIA | 1992 | 468958610.582811 FRANCE | 1998 | 271404924.050779 FRANCE | 1997 | 468455240.287691 FRANCE | 1996 | 473797643.718673 FRANCE | 1995 | 461771550.652108 FRANCE | 1994 | 468889638.111797 FRANCE | 1993 | 465751217.799073 FRANCE | 1992 | 464705630.777563 GERMANY | 1998 | 275110060.136832 GERMANY | 1997 | 465923633.812141 GERMANY | 1996 | 473719727.570063 GERMANY | 1995 | 471604182.535572 GERMANY | 1994 | 470983119.755063 GERMANY | 1993 | 467547286.859589 GERMANY | 1992 | 465026711.535501 INDIA | 1998 | 272923698.154334 INDIA | 1997 | 478277225.877687 INDIA | 1996 | 474500825.432933 INDIA | 1995 | 472414741.193003 INDIA | 1994 | 473558882.46987 INDIA | 1993 | 471415836.523461 INDIA | 1992 | 475963282.708031 INDONESIA | 1998 | 273385386.949571 INDONESIA | 1997 | 462696795.10344 INDONESIA | 1996 | 456611865.492995 INDONESIA | 1995 | 453973108.98419 INDONESIA | 1994 | 453520488.100671 INDONESIA | 1993 | 459416242.85624 INDONESIA | 1992 | 456596337.57137 IRAN | 1998 | 267486543.68093 IRAN | 1997 | 468575033.830884 IRAN | 1996 | 463208896.653234 IRAN | 1995 | 465207927.948294 IRAN | 1994 | 462158562.714869 IRAN | 1993 | 473516680.600053 IRAN | 1992 | 466812051.900348 IRAQ | 1998 | 277290630.538718 IRAQ | 1997 | 468567804.601311 IRAQ | 1996 | 482154251.41485 IRAQ | 1995 | 474980556.982456 IRAQ | 1994 | 478517571.245482 IRAQ | 1993 | 469966144.543936 IRAQ | 1992 | 475779640.825164 JAPAN | 1998 | 269144736.072916 JAPAN | 1997 | 459216687.102694 JAPAN | 1996 | 459382831.230042 JAPAN | 1995 | 456517527.712747 JAPAN | 1994 | 459836867.617962 JAPAN | 1993 | 448105747.420547 JAPAN | 1992 | 450440265.557979 JORDAN | 1998 | 270607170.124767 JORDAN | 1997 | 458109258.96726 JORDAN | 1996 | 458801281.120035 JORDAN | 1995 | 456370778.654116 JORDAN | 1994 | 460079418.363809 JORDAN | 1993 | 465041929.192379 JORDAN | 1992 | 461851578.11787 KENYA | 1998 | 270316840.607541 KENYA | 1997 | 480532386.477256 KENYA | 1996 | 479891575.575771 KENYA | 1995 | 473391770.957938 KENYA | 1994 | 477712427.819963 KENYA | 1993 | 472006999.771209 KENYA | 1992 | 474864400.421418 MOROCCO | 1998 | 277626060.140754 MOROCCO | 1997 | 461786144.53905 MOROCCO | 1996 | 471523417.86749 MOROCCO | 1995 | 467855443.439872 MOROCCO | 1994 | 471160173.758288 MOROCCO | 1993 | 472558621.65672 MOROCCO | 1992 | 471884767.654454 MOZAMBIQUE | 1998 | 271236120.755094 MOZAMBIQUE | 1997 | 462960620.917671 MOZAMBIQUE | 1996 | 462669370.471586 MOZAMBIQUE | 1995 | 454768274.330158 MOZAMBIQUE | 1994 | 457010129.020781 MOZAMBIQUE | 1993 | 457438873.999799 MOZAMBIQUE | 1992 | 454283018.060819 PERU | 1998 | 271791202.556953 PERU | 1997 | 468442018.776176 PERU | 1996 | 470640701.449638 PERU | 1995 | 462657754.115363 PERU | 1994 | 459905550.975009 PERU | 1993 | 463730679.496922 PERU | 1992 | 467426813.672875 ROMANIA | 1998 | 283953732.63998 ROMANIA | 1997 | 471942759.767048 ROMANIA | 1996 | 475126144.372188 ROMANIA | 1995 | 462132786.272668 ROMANIA | 1994 | 469359367.164954 ROMANIA | 1993 | 470926123.238484 ROMANIA | 1992 | 466726262.437057 RUSSIA | 1998 | 277244850.399256 RUSSIA | 1997 | 472870712.532064 RUSSIA | 1996 | 469287696.796334 RUSSIA | 1995 | 469853569.95318 RUSSIA | 1994 | 465005795.600892 RUSSIA | 1993 | 470950053.996283 RUSSIA | 1992 | 470274574.741501 SAUDI ARABIA | 1998 | 263925242.183576 SAUDI ARABIA | 1997 | 461173523.3392 SAUDI ARABIA | 1996 | 453452972.831049 SAUDI ARABIA | 1995 | 459736889.926652 SAUDI ARABIA | 1994 | 458412334.549713 SAUDI ARABIA | 1993 | 461722828.505648 SAUDI ARABIA | 1992 | 463443747.118539 UNITED KINGDOM | 1998 | 274399546.70047 UNITED KINGDOM | 1997 | 467721893.726957 UNITED KINGDOM | 1996 | 469506807.044018 UNITED KINGDOM | 1995 | 466655165.860133 UNITED KINGDOM | 1994 | 471286849.602567 UNITED KINGDOM | 1993 | 470395295.524399 UNITED KINGDOM | 1992 | 462042996.062975 UNITED STATES | 1998 | 276015443.551907 UNITED STATES | 1997 | 475165086.987844 UNITED STATES | 1996 | 474506559.922321 UNITED STATES | 1995 | 475285000.957517 UNITED STATES | 1994 | 481883880.04574 UNITED STATES | 1993 | 475424549.901128 UNITED STATES | 1992 | 474162788.993304 VIETNAM | 1998 | 272776177.449314 VIETNAM | 1997 | 465049743.6625 VIETNAM | 1996 | 470624166.109347 VIETNAM | 1995 | 458434549.388966 VIETNAM | 1994 | 459962126.224552 VIETNAM | 1993 | 471218319.272964 VIETNAM | 1992 | 461850236.880751 (175 rows) COMMIT; COMMIT