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 '%orchid%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 269381574.149578 ALGERIA | 1997 | 455993733.093138 ALGERIA | 1996 | 461225019.970821 ALGERIA | 1995 | 471123068.393403 ALGERIA | 1994 | 462396592.980867 ALGERIA | 1993 | 460964596.725342 ALGERIA | 1992 | 464686900.54735 ARGENTINA | 1998 | 272387149.538989 ARGENTINA | 1997 | 471790623.114163 ARGENTINA | 1996 | 466091817.975303 ARGENTINA | 1995 | 462846475.692254 ARGENTINA | 1994 | 470502609.13396 ARGENTINA | 1993 | 464314468.63491 ARGENTINA | 1992 | 458442816.938103 BRAZIL | 1998 | 266230374.338718 BRAZIL | 1997 | 470881730.969276 BRAZIL | 1996 | 468798461.917923 BRAZIL | 1995 | 470715314.94914 BRAZIL | 1994 | 466135487.55834 BRAZIL | 1993 | 465312609.898449 BRAZIL | 1992 | 468277741.804696 CANADA | 1998 | 281168893.407863 CANADA | 1997 | 474708103.241191 CANADA | 1996 | 475268357.151327 CANADA | 1995 | 477646941.05758 CANADA | 1994 | 475035422.176995 CANADA | 1993 | 475838043.084547 CANADA | 1992 | 475192843.579792 CHINA | 1998 | 274267921.437905 CHINA | 1997 | 472586582.482316 CHINA | 1996 | 468272897.129174 CHINA | 1995 | 464886970.640762 CHINA | 1994 | 467830216.52506 CHINA | 1993 | 467341253.413491 CHINA | 1992 | 468255249.977575 EGYPT | 1998 | 273648366.970793 EGYPT | 1997 | 459569187.312986 EGYPT | 1996 | 472851910.267389 EGYPT | 1995 | 460451098.010324 EGYPT | 1994 | 462880021.987682 EGYPT | 1993 | 468074824.708072 EGYPT | 1992 | 462438486.995577 ETHIOPIA | 1998 | 265737521.580393 ETHIOPIA | 1997 | 465223606.625359 ETHIOPIA | 1996 | 457951647.618798 ETHIOPIA | 1995 | 459908067.124086 ETHIOPIA | 1994 | 462562985.371158 ETHIOPIA | 1993 | 460445915.427849 ETHIOPIA | 1992 | 466116324.261145 FRANCE | 1998 | 269327781.858229 FRANCE | 1997 | 466346923.606409 FRANCE | 1996 | 461266253.79671 FRANCE | 1995 | 458395592.926215 FRANCE | 1994 | 458451432.534631 FRANCE | 1993 | 458528948.102876 FRANCE | 1992 | 457192404.256348 GERMANY | 1998 | 271754262.854113 GERMANY | 1997 | 475522662.196462 GERMANY | 1996 | 471673185.361939 GERMANY | 1995 | 475023849.8932 GERMANY | 1994 | 470500074.945439 GERMANY | 1993 | 477769241.388393 GERMANY | 1992 | 470169951.039784 INDIA | 1998 | 275314141.272884 INDIA | 1997 | 476155186.9356 INDIA | 1996 | 482436552.98001 INDIA | 1995 | 476453558.357579 INDIA | 1994 | 477561140.543509 INDIA | 1993 | 482912454.938711 INDIA | 1992 | 476549096.210496 INDONESIA | 1998 | 275853364.237097 INDONESIA | 1997 | 464051465.40818 INDONESIA | 1996 | 461360410.433489 INDONESIA | 1995 | 449891942.743462 INDONESIA | 1994 | 458830651.434043 INDONESIA | 1993 | 471003055.935602 INDONESIA | 1992 | 461297991.493315 IRAN | 1998 | 268148132.5432 IRAN | 1997 | 468150563.844128 IRAN | 1996 | 468034211.01676 IRAN | 1995 | 472211799.08016 IRAN | 1994 | 463781883.500885 IRAN | 1993 | 471191822.134775 IRAN | 1992 | 476766239.75597 IRAQ | 1998 | 280299893.597623 IRAQ | 1997 | 471419929.258161 IRAQ | 1996 | 467960410.027756 IRAQ | 1995 | 471623124.385895 IRAQ | 1994 | 474296502.062038 IRAQ | 1993 | 476123231.505773 IRAQ | 1992 | 476795988.384561 JAPAN | 1998 | 269688058.719131 JAPAN | 1997 | 464493574.826079 JAPAN | 1996 | 457806394.995402 JAPAN | 1995 | 463076481.308674 JAPAN | 1994 | 458433609.444492 JAPAN | 1993 | 465025524.391576 JAPAN | 1992 | 472224180.062411 JORDAN | 1998 | 267137603.623703 JORDAN | 1997 | 465389877.198844 JORDAN | 1996 | 459756976.96026 JORDAN | 1995 | 466662784.946425 JORDAN | 1994 | 464637069.589343 JORDAN | 1993 | 464661083.583846 JORDAN | 1992 | 461208669.749015 KENYA | 1998 | 283223509.674734 KENYA | 1997 | 473260375.319865 KENYA | 1996 | 480571346.009288 KENYA | 1995 | 476272477.994004 KENYA | 1994 | 477512229.874257 KENYA | 1993 | 484180086.779454 KENYA | 1992 | 476411661.791334 MOROCCO | 1998 | 276955152.244895 MOROCCO | 1997 | 474537719.875326 MOROCCO | 1996 | 481223952.626254 MOROCCO | 1995 | 470992976.432305 MOROCCO | 1994 | 478767113.957043 MOROCCO | 1993 | 479717667.091028 MOROCCO | 1992 | 476116867.733035 MOZAMBIQUE | 1998 | 268294832.10942 MOZAMBIQUE | 1997 | 467972900.154594 MOZAMBIQUE | 1996 | 470000330.509636 MOZAMBIQUE | 1995 | 464472965.334022 MOZAMBIQUE | 1994 | 458476781.222483 MOZAMBIQUE | 1993 | 467136059.232864 MOZAMBIQUE | 1992 | 458552574.890487 PERU | 1998 | 276054355.70166 PERU | 1997 | 466011585.081935 PERU | 1996 | 474534297.248169 PERU | 1995 | 463855359.476172 PERU | 1994 | 474945729.797071 PERU | 1993 | 477062747.257492 PERU | 1992 | 475910100.135547 ROMANIA | 1998 | 271423902.233111 ROMANIA | 1997 | 470293528.454026 ROMANIA | 1996 | 475115370.237069 ROMANIA | 1995 | 472531201.087249 ROMANIA | 1994 | 466510431.394481 ROMANIA | 1993 | 462045341.100925 ROMANIA | 1992 | 470859600.912631 RUSSIA | 1998 | 276539204.86773 RUSSIA | 1997 | 474712422.488422 RUSSIA | 1996 | 475615331.179249 RUSSIA | 1995 | 467653159.789236 RUSSIA | 1994 | 476291258.764825 RUSSIA | 1993 | 469038471.44532 RUSSIA | 1992 | 468296290.417929 SAUDI ARABIA | 1998 | 273807926.121982 SAUDI ARABIA | 1997 | 463137183.118332 SAUDI ARABIA | 1996 | 468483407.980683 SAUDI ARABIA | 1995 | 469247652.42348 SAUDI ARABIA | 1994 | 472182696.763701 SAUDI ARABIA | 1993 | 473930883.81669 SAUDI ARABIA | 1992 | 466808650.479091 UNITED KINGDOM | 1998 | 269254393.661851 UNITED KINGDOM | 1997 | 454353800.722355 UNITED KINGDOM | 1996 | 460921311.874711 UNITED KINGDOM | 1995 | 459120398.432317 UNITED KINGDOM | 1994 | 462840924.27108 UNITED KINGDOM | 1993 | 462729282.642137 UNITED KINGDOM | 1992 | 466830573.885269 UNITED STATES | 1998 | 280757163.615268 UNITED STATES | 1997 | 479304824.500009 UNITED STATES | 1996 | 473239827.501769 UNITED STATES | 1995 | 476949668.004117 UNITED STATES | 1994 | 475571954.574111 UNITED STATES | 1993 | 474749970.944388 UNITED STATES | 1992 | 485342632.895579 VIETNAM | 1998 | 271234621.499083 VIETNAM | 1997 | 465456527.149856 VIETNAM | 1996 | 470730350.276998 VIETNAM | 1995 | 462158061.979587 VIETNAM | 1994 | 468566188.75762 VIETNAM | 1993 | 465847191.802104 VIETNAM | 1992 | 461875879.565449 (175 rows) COMMIT; COMMIT