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 '%yellow%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270723302.946768 ALGERIA | 1997 | 471457743.287787 ALGERIA | 1996 | 465565639.762483 ALGERIA | 1995 | 465450295.762204 ALGERIA | 1994 | 465314099.951571 ALGERIA | 1993 | 465211362.337113 ALGERIA | 1992 | 465031306.387188 ARGENTINA | 1998 | 275844486.919886 ARGENTINA | 1997 | 471644250.46678 ARGENTINA | 1996 | 478583250.690758 ARGENTINA | 1995 | 473912743.426281 ARGENTINA | 1994 | 469275117.214649 ARGENTINA | 1993 | 467284703.424259 ARGENTINA | 1992 | 474398676.661292 BRAZIL | 1998 | 280069991.799653 BRAZIL | 1997 | 457331837.623373 BRAZIL | 1996 | 463966082.55053 BRAZIL | 1995 | 460317648.091227 BRAZIL | 1994 | 467783505.697142 BRAZIL | 1993 | 462126081.139089 BRAZIL | 1992 | 469120742.158066 CANADA | 1998 | 271067503.678776 CANADA | 1997 | 484081247.348213 CANADA | 1996 | 478897721.003415 CANADA | 1995 | 477193310.511197 CANADA | 1994 | 481302207.549802 CANADA | 1993 | 475373200.814989 CANADA | 1992 | 468966684.136983 CHINA | 1998 | 268165707.488479 CHINA | 1997 | 454634780.310959 CHINA | 1996 | 465297319.171177 CHINA | 1995 | 462554320.395533 CHINA | 1994 | 454795165.203652 CHINA | 1993 | 456038397.680405 CHINA | 1992 | 461648990.104756 EGYPT | 1998 | 270275596.949397 EGYPT | 1997 | 468087407.297433 EGYPT | 1996 | 472582565.064757 EGYPT | 1995 | 460022387.71098 EGYPT | 1994 | 463692048.819012 EGYPT | 1993 | 470831258.634081 EGYPT | 1992 | 470026251.624158 ETHIOPIA | 1998 | 274977837.110754 ETHIOPIA | 1997 | 459516221.037786 ETHIOPIA | 1996 | 462209612.743971 ETHIOPIA | 1995 | 460882000.552761 ETHIOPIA | 1994 | 460549659.578363 ETHIOPIA | 1993 | 467868157.302077 ETHIOPIA | 1992 | 462684593.953708 FRANCE | 1998 | 270705971.988595 FRANCE | 1997 | 462448686.638322 FRANCE | 1996 | 461077280.606799 FRANCE | 1995 | 465925133.278123 FRANCE | 1994 | 464854549.804793 FRANCE | 1993 | 458754978.43863 FRANCE | 1992 | 462379277.587625 GERMANY | 1998 | 277961325.771707 GERMANY | 1997 | 476626945.573149 GERMANY | 1996 | 479647909.411292 GERMANY | 1995 | 485060558.183745 GERMANY | 1994 | 481543625.80958 GERMANY | 1993 | 489753235.747171 GERMANY | 1992 | 474537357.425069 INDIA | 1998 | 280157837.114054 INDIA | 1997 | 467579275.772787 INDIA | 1996 | 469810179.982508 INDIA | 1995 | 469396540.430175 INDIA | 1994 | 473047445.837348 INDIA | 1993 | 470506772.466428 INDIA | 1992 | 461235801.272085 INDONESIA | 1998 | 272649931.771699 INDONESIA | 1997 | 461196492.900941 INDONESIA | 1996 | 454501217.069988 INDONESIA | 1995 | 459018519.332113 INDONESIA | 1994 | 469099135.550051 INDONESIA | 1993 | 464667780.438988 INDONESIA | 1992 | 459480167.701993 IRAN | 1998 | 271339073.59275 IRAN | 1997 | 460523796.13865 IRAN | 1996 | 461736888.262514 IRAN | 1995 | 469065076.671952 IRAN | 1994 | 464597465.059946 IRAN | 1993 | 460890989.688685 IRAN | 1992 | 457666042.719597 IRAQ | 1998 | 278590879.133577 IRAQ | 1997 | 477185413.442148 IRAQ | 1996 | 481396225.657511 IRAQ | 1995 | 483004351.125851 IRAQ | 1994 | 485036082.226708 IRAQ | 1993 | 472154206.628601 IRAQ | 1992 | 476113527.669593 JAPAN | 1998 | 276959702.143459 JAPAN | 1997 | 463767764.328538 JAPAN | 1996 | 472997039.842967 JAPAN | 1995 | 467833206.781469 JAPAN | 1994 | 468326711.511132 JAPAN | 1993 | 473168545.154106 JAPAN | 1992 | 474297036.1224 JORDAN | 1998 | 267229859.668606 JORDAN | 1997 | 454168686.128686 JORDAN | 1996 | 452216664.234953 JORDAN | 1995 | 455929991.826424 JORDAN | 1994 | 464481498.333774 JORDAN | 1993 | 458617591.633366 JORDAN | 1992 | 463205807.82558 KENYA | 1998 | 280972537.896833 KENYA | 1997 | 470825867.845582 KENYA | 1996 | 472849950.467753 KENYA | 1995 | 470822103.643419 KENYA | 1994 | 476815806.701901 KENYA | 1993 | 479553640.970965 KENYA | 1992 | 478071550.477446 MOROCCO | 1998 | 271222606.324596 MOROCCO | 1997 | 463188876.426427 MOROCCO | 1996 | 469823805.925527 MOROCCO | 1995 | 465844506.014895 MOROCCO | 1994 | 465796415.689564 MOROCCO | 1993 | 463541959.40897 MOROCCO | 1992 | 462359846.401304 MOZAMBIQUE | 1998 | 271487549.09348 MOZAMBIQUE | 1997 | 458559279.903157 MOZAMBIQUE | 1996 | 462668664.54538 MOZAMBIQUE | 1995 | 463985726.206041 MOZAMBIQUE | 1994 | 463639346.560225 MOZAMBIQUE | 1993 | 455664762.025973 MOZAMBIQUE | 1992 | 457016043.970394 PERU | 1998 | 276733866.639768 PERU | 1997 | 467629169.137683 PERU | 1996 | 460438225.436501 PERU | 1995 | 467116127.324354 PERU | 1994 | 455598287.14669 PERU | 1993 | 458687556.070388 PERU | 1992 | 467302226.894526 ROMANIA | 1998 | 274098122.878806 ROMANIA | 1997 | 471541435.032674 ROMANIA | 1996 | 473094611.213367 ROMANIA | 1995 | 467481548.441048 ROMANIA | 1994 | 462159092.268607 ROMANIA | 1993 | 474039370.851315 ROMANIA | 1992 | 477905147.736407 RUSSIA | 1998 | 276833866.570579 RUSSIA | 1997 | 470617993.893995 RUSSIA | 1996 | 484621863.470855 RUSSIA | 1995 | 476571976.531374 RUSSIA | 1994 | 474931078.718225 RUSSIA | 1993 | 475760993.560259 RUSSIA | 1992 | 480830977.567801 SAUDI ARABIA | 1998 | 269728757.244425 SAUDI ARABIA | 1997 | 460731899.544599 SAUDI ARABIA | 1996 | 466325480.922813 SAUDI ARABIA | 1995 | 461781437.55522 SAUDI ARABIA | 1994 | 459987160.578185 SAUDI ARABIA | 1993 | 463555460.383184 SAUDI ARABIA | 1992 | 468778784.92894 UNITED KINGDOM | 1998 | 270704771.844861 UNITED KINGDOM | 1997 | 463956831.014467 UNITED KINGDOM | 1996 | 456713885.122445 UNITED KINGDOM | 1995 | 461251969.675251 UNITED KINGDOM | 1994 | 458874268.770796 UNITED KINGDOM | 1993 | 461632970.518363 UNITED KINGDOM | 1992 | 465060323.614206 UNITED STATES | 1998 | 277707178.638672 UNITED STATES | 1997 | 476841286.883766 UNITED STATES | 1996 | 474734794.296221 UNITED STATES | 1995 | 477527262.847717 UNITED STATES | 1994 | 476771155.525497 UNITED STATES | 1993 | 473272144.148474 UNITED STATES | 1992 | 476054216.46041 VIETNAM | 1998 | 271171575.835927 VIETNAM | 1997 | 455455687.493143 VIETNAM | 1996 | 469273325.207901 VIETNAM | 1995 | 464874028.53285 VIETNAM | 1994 | 460848468.780216 VIETNAM | 1993 | 464042416.690159 VIETNAM | 1992 | 461218306.144635 (175 rows) COMMIT; COMMIT