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 | 270406883.240657 ALGERIA | 1997 | 471465539.359519 ALGERIA | 1996 | 465221645.076867 ALGERIA | 1995 | 465538894.206773 ALGERIA | 1994 | 465403099.122041 ALGERIA | 1993 | 464918208.289118 ALGERIA | 1992 | 464784677.503843 ARGENTINA | 1998 | 275661899.725685 ARGENTINA | 1997 | 471779967.70989 ARGENTINA | 1996 | 478759314.345906 ARGENTINA | 1995 | 473715963.505203 ARGENTINA | 1994 | 468880149.00518 ARGENTINA | 1993 | 467402837.178256 ARGENTINA | 1992 | 474353560.877718 BRAZIL | 1998 | 279829938.419907 BRAZIL | 1997 | 457203675.267548 BRAZIL | 1996 | 464430173.223184 BRAZIL | 1995 | 459962662.624295 BRAZIL | 1994 | 467747894.137095 BRAZIL | 1993 | 461836281.90362 BRAZIL | 1992 | 469100694.845206 CANADA | 1998 | 270994476.800461 CANADA | 1997 | 483844357.575599 CANADA | 1996 | 478966212.431302 CANADA | 1995 | 477121008.569731 CANADA | 1994 | 480755197.939585 CANADA | 1993 | 475127340.16629 CANADA | 1992 | 468581940.331797 CHINA | 1998 | 268053631.330793 CHINA | 1997 | 454369566.237922 CHINA | 1996 | 465628512.737971 CHINA | 1995 | 462805930.094504 CHINA | 1994 | 455246588.872242 CHINA | 1993 | 456419247.044123 CHINA | 1992 | 461194871.028168 EGYPT | 1998 | 269974575.14545 EGYPT | 1997 | 468110155.012675 EGYPT | 1996 | 472667044.211106 EGYPT | 1995 | 459848642.957156 EGYPT | 1994 | 463985429.657501 EGYPT | 1993 | 471042764.17808 EGYPT | 1992 | 469928110.534005 ETHIOPIA | 1998 | 274812205.169476 ETHIOPIA | 1997 | 459253663.782846 ETHIOPIA | 1996 | 461994210.605355 ETHIOPIA | 1995 | 461059670.560874 ETHIOPIA | 1994 | 461012061.810605 ETHIOPIA | 1993 | 467971647.015929 ETHIOPIA | 1992 | 462928677.179153 FRANCE | 1998 | 270701381.297861 FRANCE | 1997 | 462256606.077363 FRANCE | 1996 | 461573006.462505 FRANCE | 1995 | 466178437.001875 FRANCE | 1994 | 465593994.188288 FRANCE | 1993 | 458886796.134027 FRANCE | 1992 | 462514490.569267 GERMANY | 1998 | 277932821.89987 GERMANY | 1997 | 476436735.076538 GERMANY | 1996 | 479298368.10221 GERMANY | 1995 | 485055939.481477 GERMANY | 1994 | 480963769.279602 GERMANY | 1993 | 489469440.63761 GERMANY | 1992 | 474806577.832239 INDIA | 1998 | 280336095.811149 INDIA | 1997 | 467641236.788796 INDIA | 1996 | 469745310.940895 INDIA | 1995 | 469366472.255221 INDIA | 1994 | 473472325.743554 INDIA | 1993 | 470575771.861183 INDIA | 1992 | 460900399.04696 INDONESIA | 1998 | 272531804.279502 INDONESIA | 1997 | 461597121.913899 INDONESIA | 1996 | 454516285.162636 INDONESIA | 1995 | 459162545.963367 INDONESIA | 1994 | 468759654.350496 INDONESIA | 1993 | 464129005.78694 INDONESIA | 1992 | 459662148.089673 IRAN | 1998 | 271202673.769195 IRAN | 1997 | 460519837.158242 IRAN | 1996 | 461412142.620367 IRAN | 1995 | 468701259.666315 IRAN | 1994 | 464347872.993166 IRAN | 1993 | 460821849.56276 IRAN | 1992 | 458415402.575758 IRAQ | 1998 | 278758999.043269 IRAQ | 1997 | 477054269.178116 IRAQ | 1996 | 481229210.263622 IRAQ | 1995 | 482800927.023875 IRAQ | 1994 | 484470531.305405 IRAQ | 1993 | 471942443.544214 IRAQ | 1992 | 476060542.471346 JAPAN | 1998 | 276794102.7088 JAPAN | 1997 | 463960800.019764 JAPAN | 1996 | 473103575.931699 JAPAN | 1995 | 467725698.508525 JAPAN | 1994 | 468367358.893686 JAPAN | 1993 | 473120916.446733 JAPAN | 1992 | 474165462.395965 JORDAN | 1998 | 267233507.826361 JORDAN | 1997 | 454504917.257256 JORDAN | 1996 | 452168477.49843 JORDAN | 1995 | 455763882.376513 JORDAN | 1994 | 464469894.112106 JORDAN | 1993 | 458127487.811328 JORDAN | 1992 | 463201261.888799 KENYA | 1998 | 281074616.188177 KENYA | 1997 | 471027630.75539 KENYA | 1996 | 472652380.546683 KENYA | 1995 | 471010615.600185 KENYA | 1994 | 477043267.38749 KENYA | 1993 | 479750749.794434 KENYA | 1992 | 478539870.814203 MOROCCO | 1998 | 271202333.416974 MOROCCO | 1997 | 463016164.696107 MOROCCO | 1996 | 470081594.747958 MOROCCO | 1995 | 466256957.341589 MOROCCO | 1994 | 465959799.562326 MOROCCO | 1993 | 463650638.473154 MOROCCO | 1992 | 462614433.631101 MOZAMBIQUE | 1998 | 271532882.538394 MOZAMBIQUE | 1997 | 458871559.209611 MOZAMBIQUE | 1996 | 462565274.770872 MOZAMBIQUE | 1995 | 463898060.871542 MOZAMBIQUE | 1994 | 463400378.498504 MOZAMBIQUE | 1993 | 455398197.954189 MOZAMBIQUE | 1992 | 457138686.333247 PERU | 1998 | 276515530.494495 PERU | 1997 | 467358015.405276 PERU | 1996 | 460477716.845722 PERU | 1995 | 467632471.59239 PERU | 1994 | 455511950.035233 PERU | 1993 | 458372892.227967 PERU | 1992 | 466914040.867777 ROMANIA | 1998 | 273899195.279754 ROMANIA | 1997 | 472053747.900791 ROMANIA | 1996 | 472984235.188569 ROMANIA | 1995 | 467750984.011057 ROMANIA | 1994 | 462521441.745454 ROMANIA | 1993 | 473988205.071128 ROMANIA | 1992 | 477364729.086306 RUSSIA | 1998 | 276618522.433429 RUSSIA | 1997 | 470771606.933284 RUSSIA | 1996 | 484658391.408973 RUSSIA | 1995 | 476698859.073282 RUSSIA | 1994 | 474993542.109237 RUSSIA | 1993 | 476294070.68705 RUSSIA | 1992 | 480816851.01112 SAUDI ARABIA | 1998 | 270231956.962552 SAUDI ARABIA | 1997 | 461185817.951653 SAUDI ARABIA | 1996 | 466338705.384482 SAUDI ARABIA | 1995 | 461865387.023934 SAUDI ARABIA | 1994 | 460021289.425306 SAUDI ARABIA | 1993 | 463098339.120887 SAUDI ARABIA | 1992 | 468869267.482772 UNITED KINGDOM | 1998 | 270665826.670618 UNITED KINGDOM | 1997 | 463613453.142691 UNITED KINGDOM | 1996 | 457294416.37322 UNITED KINGDOM | 1995 | 461696363.638134 UNITED KINGDOM | 1994 | 459056853.319537 UNITED KINGDOM | 1993 | 461790758.860838 UNITED KINGDOM | 1992 | 465185754.890949 UNITED STATES | 1998 | 277697404.266104 UNITED STATES | 1997 | 476951220.54013 UNITED STATES | 1996 | 474959298.455214 UNITED STATES | 1995 | 477282829.125222 UNITED STATES | 1994 | 476605566.268712 UNITED STATES | 1993 | 473301019.743682 UNITED STATES | 1992 | 476106142.710146 VIETNAM | 1998 | 271097199.439975 VIETNAM | 1997 | 455444686.031895 VIETNAM | 1996 | 468858003.421522 VIETNAM | 1995 | 465216802.082696 VIETNAM | 1994 | 460787688.842804 VIETNAM | 1993 | 464236174.745472 VIETNAM | 1992 | 461464814.308765 (175 rows) COMMIT; COMMIT