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 '%gainsboro%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264642116.532197 ALGERIA | 1997 | 449532283.306518 ALGERIA | 1996 | 459028112.891959 ALGERIA | 1995 | 448044188.781134 ALGERIA | 1994 | 449157348.757678 ALGERIA | 1993 | 460783377.773257 ALGERIA | 1992 | 449465437.901389 ARGENTINA | 1998 | 277866789.215504 ARGENTINA | 1997 | 474601567.163382 ARGENTINA | 1996 | 472500566.549836 ARGENTINA | 1995 | 467212004.008076 ARGENTINA | 1994 | 468841865.432675 ARGENTINA | 1993 | 468100224.782343 ARGENTINA | 1992 | 473960961.403998 BRAZIL | 1998 | 273332694.05284 BRAZIL | 1997 | 471980569.047511 BRAZIL | 1996 | 455138864.18194 BRAZIL | 1995 | 461714487.431253 BRAZIL | 1994 | 465523761.145033 BRAZIL | 1993 | 461150898.399546 BRAZIL | 1992 | 470134239.352157 CANADA | 1998 | 271151485.458041 CANADA | 1997 | 468096781.339057 CANADA | 1996 | 478507236.695999 CANADA | 1995 | 467669516.632593 CANADA | 1994 | 476614918.684209 CANADA | 1993 | 465140308.133413 CANADA | 1992 | 468383373.697037 CHINA | 1998 | 272243600.090446 CHINA | 1997 | 469159090.149082 CHINA | 1996 | 464742002.386623 CHINA | 1995 | 461179652.599852 CHINA | 1994 | 470680739.014515 CHINA | 1993 | 469845956.118458 CHINA | 1992 | 460957273.353531 EGYPT | 1998 | 283535466.15584 EGYPT | 1997 | 467498732.322538 EGYPT | 1996 | 462226220.292941 EGYPT | 1995 | 456539187.390079 EGYPT | 1994 | 465628352.247724 EGYPT | 1993 | 460551045.141008 EGYPT | 1992 | 464153074.928722 ETHIOPIA | 1998 | 265398579.843538 ETHIOPIA | 1997 | 452124020.86125 ETHIOPIA | 1996 | 454948942.960644 ETHIOPIA | 1995 | 455986968.71069 ETHIOPIA | 1994 | 456895285.876759 ETHIOPIA | 1993 | 449836979.379546 ETHIOPIA | 1992 | 458430731.688808 FRANCE | 1998 | 262110024.78725 FRANCE | 1997 | 451064217.019286 FRANCE | 1996 | 456610629.972491 FRANCE | 1995 | 449641482.701964 FRANCE | 1994 | 447934555.097971 FRANCE | 1993 | 451075192.038027 FRANCE | 1992 | 456219131.712329 GERMANY | 1998 | 279067374.807059 GERMANY | 1997 | 482627953.730102 GERMANY | 1996 | 481617628.21315 GERMANY | 1995 | 469074640.52709 GERMANY | 1994 | 488538111.354511 GERMANY | 1993 | 473865193.488114 GERMANY | 1992 | 474706978.837973 INDIA | 1998 | 279570811.24859 INDIA | 1997 | 468756448.807583 INDIA | 1996 | 469331854.905767 INDIA | 1995 | 466613380.498717 INDIA | 1994 | 472541302.38593 INDIA | 1993 | 472173612.049024 INDIA | 1992 | 467767004.207459 INDONESIA | 1998 | 265985052.519339 INDONESIA | 1997 | 466102958.674632 INDONESIA | 1996 | 472188500.975917 INDONESIA | 1995 | 453002530.452484 INDONESIA | 1994 | 463742423.299759 INDONESIA | 1993 | 459414610.849719 INDONESIA | 1992 | 462218262.676479 IRAN | 1998 | 274107570.401031 IRAN | 1997 | 462644076.948978 IRAN | 1996 | 460976043.296492 IRAN | 1995 | 458197039.836012 IRAN | 1994 | 458514200.516537 IRAN | 1993 | 457530637.028528 IRAN | 1992 | 464474959.49181 IRAQ | 1998 | 277557647.621588 IRAQ | 1997 | 463746757.562494 IRAQ | 1996 | 466814988.548266 IRAQ | 1995 | 465749338.209679 IRAQ | 1994 | 467997812.315361 IRAQ | 1993 | 460819389.093203 IRAQ | 1992 | 467418896.9736 JAPAN | 1998 | 270179644.917135 JAPAN | 1997 | 458147370.794943 JAPAN | 1996 | 469609122.145435 JAPAN | 1995 | 470930400.419873 JAPAN | 1994 | 467051945.089236 JAPAN | 1993 | 467351484.503874 JAPAN | 1992 | 465312228.754832 JORDAN | 1998 | 265861187.897211 JORDAN | 1997 | 458374270.935511 JORDAN | 1996 | 461768017.304198 JORDAN | 1995 | 457665967.976011 JORDAN | 1994 | 459760307.93242 JORDAN | 1993 | 457105651.074631 JORDAN | 1992 | 458234762.380959 KENYA | 1998 | 270143431.172019 KENYA | 1997 | 468544630.216173 KENYA | 1996 | 467429613.400815 KENYA | 1995 | 468138409.097936 KENYA | 1994 | 467114130.86214 KENYA | 1993 | 468747651.032731 KENYA | 1992 | 473297533.806979 MOROCCO | 1998 | 270799102.791671 MOROCCO | 1997 | 469243055.514763 MOROCCO | 1996 | 461868776.276273 MOROCCO | 1995 | 457017343.477775 MOROCCO | 1994 | 464629863.227663 MOROCCO | 1993 | 464195994.163165 MOROCCO | 1992 | 466086216.824437 MOZAMBIQUE | 1998 | 271005513.187144 MOZAMBIQUE | 1997 | 458283057.931431 MOZAMBIQUE | 1996 | 465625885.759682 MOZAMBIQUE | 1995 | 463081913.73149 MOZAMBIQUE | 1994 | 459240884.661898 MOZAMBIQUE | 1993 | 465251888.579876 MOZAMBIQUE | 1992 | 458595145.114138 PERU | 1998 | 270800431.911688 PERU | 1997 | 463470638.62139 PERU | 1996 | 461679222.280567 PERU | 1995 | 465517586.696578 PERU | 1994 | 462652872.608607 PERU | 1993 | 461479906.364245 PERU | 1992 | 463178133.84423 ROMANIA | 1998 | 270572509.161075 ROMANIA | 1997 | 457963976.994927 ROMANIA | 1996 | 468087499.280458 ROMANIA | 1995 | 466224220.203164 ROMANIA | 1994 | 466324630.831214 ROMANIA | 1993 | 462514300.642457 ROMANIA | 1992 | 471386513.048245 RUSSIA | 1998 | 279002637.710946 RUSSIA | 1997 | 472231226.406929 RUSSIA | 1996 | 480539446.165708 RUSSIA | 1995 | 467336156.603687 RUSSIA | 1994 | 471643140.197209 RUSSIA | 1993 | 479577962.238226 RUSSIA | 1992 | 473820073.364896 SAUDI ARABIA | 1998 | 278677305.465364 SAUDI ARABIA | 1997 | 466141774.608618 SAUDI ARABIA | 1996 | 474843823.603315 SAUDI ARABIA | 1995 | 471229268.632934 SAUDI ARABIA | 1994 | 471220499.561609 SAUDI ARABIA | 1993 | 460000265.16472 SAUDI ARABIA | 1992 | 472455614.001263 UNITED KINGDOM | 1998 | 276304503.441035 UNITED KINGDOM | 1997 | 470832334.714995 UNITED KINGDOM | 1996 | 464281219.223573 UNITED KINGDOM | 1995 | 458596832.491795 UNITED KINGDOM | 1994 | 468492346.179301 UNITED KINGDOM | 1993 | 457916616.161282 UNITED KINGDOM | 1992 | 460234478.744889 UNITED STATES | 1998 | 274057123.849468 UNITED STATES | 1997 | 460879132.408054 UNITED STATES | 1996 | 465190672.493691 UNITED STATES | 1995 | 470198151.508105 UNITED STATES | 1994 | 466444694.336009 UNITED STATES | 1993 | 466487847.070038 UNITED STATES | 1992 | 465495123.118848 VIETNAM | 1998 | 276482661.562822 VIETNAM | 1997 | 468664302.187234 VIETNAM | 1996 | 475817056.41907 VIETNAM | 1995 | 468000514.224039 VIETNAM | 1994 | 470694820.614502 VIETNAM | 1993 | 472482156.372019 VIETNAM | 1992 | 466325992.172423 (175 rows) COMMIT; COMMIT