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 '%slate%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261018533.49922 ALGERIA | 1997 | 450149504.947691 ALGERIA | 1996 | 451748804.391631 ALGERIA | 1995 | 445775527.953221 ALGERIA | 1994 | 455432689.834517 ALGERIA | 1993 | 443851587.709798 ALGERIA | 1992 | 451806656.086736 ARGENTINA | 1998 | 278693787.948277 ARGENTINA | 1997 | 470850991.004614 ARGENTINA | 1996 | 465536167.244941 ARGENTINA | 1995 | 468827600.439511 ARGENTINA | 1994 | 472763843.986408 ARGENTINA | 1993 | 462890983.18788 ARGENTINA | 1992 | 468078607.083875 BRAZIL | 1998 | 276436739.380027 BRAZIL | 1997 | 474236348.000747 BRAZIL | 1996 | 472916325.077124 BRAZIL | 1995 | 469421750.38404 BRAZIL | 1994 | 466628370.698633 BRAZIL | 1993 | 472236175.938084 BRAZIL | 1992 | 476967552.461486 CANADA | 1998 | 272702637.681426 CANADA | 1997 | 469084652.202051 CANADA | 1996 | 469148365.768754 CANADA | 1995 | 474291372.556265 CANADA | 1994 | 481744606.228252 CANADA | 1993 | 478470147.450381 CANADA | 1992 | 471458756.091698 CHINA | 1998 | 275707111.845145 CHINA | 1997 | 465766534.045852 CHINA | 1996 | 473532758.890915 CHINA | 1995 | 465438732.414412 CHINA | 1994 | 466369441.579527 CHINA | 1993 | 470707779.83427 CHINA | 1992 | 468667582.013683 EGYPT | 1998 | 269729061.603723 EGYPT | 1997 | 473706677.460856 EGYPT | 1996 | 467398811.279361 EGYPT | 1995 | 468388110.700323 EGYPT | 1994 | 471160572.691083 EGYPT | 1993 | 468826117.356668 EGYPT | 1992 | 478246450.143583 ETHIOPIA | 1998 | 274877102.682569 ETHIOPIA | 1997 | 456546547.770101 ETHIOPIA | 1996 | 452060091.489303 ETHIOPIA | 1995 | 456870473.018352 ETHIOPIA | 1994 | 462066293.45921 ETHIOPIA | 1993 | 459414086.315823 ETHIOPIA | 1992 | 460438749.409121 FRANCE | 1998 | 269295153.721407 FRANCE | 1997 | 460939134.575729 FRANCE | 1996 | 457771723.30263 FRANCE | 1995 | 462134497.174022 FRANCE | 1994 | 458930473.054083 FRANCE | 1993 | 462433167.596267 FRANCE | 1992 | 453887351.863288 GERMANY | 1998 | 282108356.50985 GERMANY | 1997 | 473572900.120536 GERMANY | 1996 | 477804045.207733 GERMANY | 1995 | 480886431.727767 GERMANY | 1994 | 475827548.130263 GERMANY | 1993 | 471186382.718588 GERMANY | 1992 | 472049390.262841 INDIA | 1998 | 267466124.904057 INDIA | 1997 | 463176043.075676 INDIA | 1996 | 479563060.267824 INDIA | 1995 | 477738930.920988 INDIA | 1994 | 465376940.415254 INDIA | 1993 | 469711239.488511 INDIA | 1992 | 467211707.933481 INDONESIA | 1998 | 268415836.757258 INDONESIA | 1997 | 463195661.639301 INDONESIA | 1996 | 474424932.423841 INDONESIA | 1995 | 458665247.301664 INDONESIA | 1994 | 464455877.345847 INDONESIA | 1993 | 462222218.237217 INDONESIA | 1992 | 460958042.404 IRAN | 1998 | 267589009.134853 IRAN | 1997 | 464746400.269505 IRAN | 1996 | 462096307.632953 IRAN | 1995 | 462409630.330468 IRAN | 1994 | 465860210.320795 IRAN | 1993 | 458605705.397295 IRAN | 1992 | 466814384.476762 IRAQ | 1998 | 280560101.802023 IRAQ | 1997 | 471929800.139043 IRAQ | 1996 | 479743409.476964 IRAQ | 1995 | 471021233.962161 IRAQ | 1994 | 472522757.657397 IRAQ | 1993 | 469704255.853549 IRAQ | 1992 | 476878977.377851 JAPAN | 1998 | 268275411.814736 JAPAN | 1997 | 464518976.088529 JAPAN | 1996 | 470761974.894519 JAPAN | 1995 | 460607977.147542 JAPAN | 1994 | 466977563.009898 JAPAN | 1993 | 464044142.08584 JAPAN | 1992 | 462813892.632421 JORDAN | 1998 | 269131433.03656 JORDAN | 1997 | 450614093.232024 JORDAN | 1996 | 459734761.101459 JORDAN | 1995 | 455227162.106679 JORDAN | 1994 | 456662439.903233 JORDAN | 1993 | 448809409.069781 JORDAN | 1992 | 453168202.912393 KENYA | 1998 | 282446970.009952 KENYA | 1997 | 470050784.668732 KENYA | 1996 | 479213695.864883 KENYA | 1995 | 474734905.575731 KENYA | 1994 | 472205450.594315 KENYA | 1993 | 479556361.834336 KENYA | 1992 | 470828129.453976 MOROCCO | 1998 | 275035023.599373 MOROCCO | 1997 | 460564357.706132 MOROCCO | 1996 | 465274430.858716 MOROCCO | 1995 | 469580157.837264 MOROCCO | 1994 | 464415475.833817 MOROCCO | 1993 | 466299283.305402 MOROCCO | 1992 | 472237941.833982 MOZAMBIQUE | 1998 | 267164565.461417 MOZAMBIQUE | 1997 | 455435518.935987 MOZAMBIQUE | 1996 | 457622981.466964 MOZAMBIQUE | 1995 | 455577856.129902 MOZAMBIQUE | 1994 | 450258463.390117 MOZAMBIQUE | 1993 | 448474275.540781 MOZAMBIQUE | 1992 | 455589929.778174 PERU | 1998 | 279752683.741146 PERU | 1997 | 469660171.418118 PERU | 1996 | 468004685.92614 PERU | 1995 | 475499199.986287 PERU | 1994 | 467461344.344144 PERU | 1993 | 471739781.615439 PERU | 1992 | 461562947.391735 ROMANIA | 1998 | 275728201.320833 ROMANIA | 1997 | 460560043.033523 ROMANIA | 1996 | 466173577.312912 ROMANIA | 1995 | 455345700.251106 ROMANIA | 1994 | 460828276.073698 ROMANIA | 1993 | 464129787.110937 ROMANIA | 1992 | 467135802.862018 RUSSIA | 1998 | 275226493.473737 RUSSIA | 1997 | 466483143.839892 RUSSIA | 1996 | 471317019.422198 RUSSIA | 1995 | 464288472.465073 RUSSIA | 1994 | 463906520.336213 RUSSIA | 1993 | 463612253.498777 RUSSIA | 1992 | 466593888.821221 SAUDI ARABIA | 1998 | 278148668.297178 SAUDI ARABIA | 1997 | 470405423.774206 SAUDI ARABIA | 1996 | 465017703.330939 SAUDI ARABIA | 1995 | 466429390.712196 SAUDI ARABIA | 1994 | 463092624.113867 SAUDI ARABIA | 1993 | 467097091.074297 SAUDI ARABIA | 1992 | 461160913.846334 UNITED KINGDOM | 1998 | 275304648.090594 UNITED KINGDOM | 1997 | 470256817.767832 UNITED KINGDOM | 1996 | 467183818.047042 UNITED KINGDOM | 1995 | 472038586.198085 UNITED KINGDOM | 1994 | 473778537.652604 UNITED KINGDOM | 1993 | 464521016.431904 UNITED KINGDOM | 1992 | 466080873.553819 UNITED STATES | 1998 | 274947875.230133 UNITED STATES | 1997 | 465546470.4119 UNITED STATES | 1996 | 462558024.437943 UNITED STATES | 1995 | 469812286.805007 UNITED STATES | 1994 | 469408954.002316 UNITED STATES | 1993 | 467516034.993325 UNITED STATES | 1992 | 466352620.28064 VIETNAM | 1998 | 270995594.359477 VIETNAM | 1997 | 474537393.728406 VIETNAM | 1996 | 466908756.331878 VIETNAM | 1995 | 456350330.176289 VIETNAM | 1994 | 469428561.155846 VIETNAM | 1993 | 473648955.345256 VIETNAM | 1992 | 458840234.505573 (175 rows) COMMIT; COMMIT