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 '%drab%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264144045.641175 ALGERIA | 1997 | 456018257.504414 ALGERIA | 1996 | 449101597.296459 ALGERIA | 1995 | 456224931.612067 ALGERIA | 1994 | 460901612.915318 ALGERIA | 1993 | 460951445.18157 ALGERIA | 1992 | 458053493.981084 ARGENTINA | 1998 | 277769006.670855 ARGENTINA | 1997 | 464289368.68898 ARGENTINA | 1996 | 477679602.331248 ARGENTINA | 1995 | 464804453.932153 ARGENTINA | 1994 | 468844288.580456 ARGENTINA | 1993 | 481602603.143416 ARGENTINA | 1992 | 471292202.947206 BRAZIL | 1998 | 273905539.425425 BRAZIL | 1997 | 465340347.913403 BRAZIL | 1996 | 472384058.255589 BRAZIL | 1995 | 464914174.425433 BRAZIL | 1994 | 467834765.31362 BRAZIL | 1993 | 468308471.473711 BRAZIL | 1992 | 469889581.860653 CANADA | 1998 | 280780596.101821 CANADA | 1997 | 479391270.653015 CANADA | 1996 | 487686607.137575 CANADA | 1995 | 485529147.20521 CANADA | 1994 | 488435100.33265 CANADA | 1993 | 478665770.477694 CANADA | 1992 | 478613873.384467 CHINA | 1998 | 278137738.194669 CHINA | 1997 | 475325308.199724 CHINA | 1996 | 476836238.572827 CHINA | 1995 | 476410129.733316 CHINA | 1994 | 463716854.002257 CHINA | 1993 | 472181789.096622 CHINA | 1992 | 474132836.674395 EGYPT | 1998 | 279089602.36525 EGYPT | 1997 | 469807376.88854 EGYPT | 1996 | 472424305.068421 EGYPT | 1995 | 476822848.851387 EGYPT | 1994 | 473024293.285781 EGYPT | 1993 | 473096158.384061 EGYPT | 1992 | 471476729.254784 ETHIOPIA | 1998 | 280736858.31017 ETHIOPIA | 1997 | 470477551.979466 ETHIOPIA | 1996 | 466335853.228978 ETHIOPIA | 1995 | 475081429.068569 ETHIOPIA | 1994 | 473757321.707092 ETHIOPIA | 1993 | 474654892.104786 ETHIOPIA | 1992 | 478757883.234958 FRANCE | 1998 | 272523065.190904 FRANCE | 1997 | 465760170.034822 FRANCE | 1996 | 466791780.955314 FRANCE | 1995 | 469386177.200901 FRANCE | 1994 | 467322141.93907 FRANCE | 1993 | 464973296.464865 FRANCE | 1992 | 464648931.840389 GERMANY | 1998 | 281235248.639737 GERMANY | 1997 | 469673732.703552 GERMANY | 1996 | 472893731.005551 GERMANY | 1995 | 471497261.923536 GERMANY | 1994 | 485287236.791745 GERMANY | 1993 | 475977865.512555 GERMANY | 1992 | 478060564.319638 INDIA | 1998 | 280188435.124712 INDIA | 1997 | 473327803.346046 INDIA | 1996 | 479584523.605143 INDIA | 1995 | 473928018.175903 INDIA | 1994 | 476996660.850736 INDIA | 1993 | 473872098.02911 INDIA | 1992 | 480607971.297955 INDONESIA | 1998 | 276154300.92816 INDONESIA | 1997 | 463286118.769005 INDONESIA | 1996 | 478608313.200191 INDONESIA | 1995 | 475189666.485144 INDONESIA | 1994 | 463697019.134925 INDONESIA | 1993 | 468642965.382131 INDONESIA | 1992 | 465972545.225264 IRAN | 1998 | 274963867.996617 IRAN | 1997 | 468785557.628157 IRAN | 1996 | 471425583.739022 IRAN | 1995 | 476410710.487284 IRAN | 1994 | 470459702.508856 IRAN | 1993 | 479091838.019144 IRAN | 1992 | 478061915.627084 IRAQ | 1998 | 280162823.737266 IRAQ | 1997 | 468963832.333592 IRAQ | 1996 | 476735655.330809 IRAQ | 1995 | 478262087.961203 IRAQ | 1994 | 479326476.179875 IRAQ | 1993 | 479508047.535542 IRAQ | 1992 | 486814954.912429 JAPAN | 1998 | 271384289.656393 JAPAN | 1997 | 460108716.977798 JAPAN | 1996 | 468479226.78135 JAPAN | 1995 | 463573082.489379 JAPAN | 1994 | 467358738.680481 JAPAN | 1993 | 469844007.956144 JAPAN | 1992 | 463860372.326638 JORDAN | 1998 | 268848716.810197 JORDAN | 1997 | 461452496.494543 JORDAN | 1996 | 458766935.405645 JORDAN | 1995 | 454117125.045255 JORDAN | 1994 | 462345534.633407 JORDAN | 1993 | 455666952.344918 JORDAN | 1992 | 465454215.326283 KENYA | 1998 | 271108108.14791 KENYA | 1997 | 466453944.296869 KENYA | 1996 | 473770574.96387 KENYA | 1995 | 468238776.523213 KENYA | 1994 | 471638850.79528 KENYA | 1993 | 462164939.703028 KENYA | 1992 | 472462625.967916 MOROCCO | 1998 | 277110316.096893 MOROCCO | 1997 | 479905005.785677 MOROCCO | 1996 | 472004529.667803 MOROCCO | 1995 | 477798342.08237 MOROCCO | 1994 | 468080942.456945 MOROCCO | 1993 | 469495131.258002 MOROCCO | 1992 | 480677638.458688 MOZAMBIQUE | 1998 | 270881063.239893 MOZAMBIQUE | 1997 | 460782868.771204 MOZAMBIQUE | 1996 | 462805526.821959 MOZAMBIQUE | 1995 | 463840452.081156 MOZAMBIQUE | 1994 | 478950138.732982 MOZAMBIQUE | 1993 | 466622861.735238 MOZAMBIQUE | 1992 | 462176329.13342 PERU | 1998 | 275806141.538344 PERU | 1997 | 472460090.367272 PERU | 1996 | 465158420.833027 PERU | 1995 | 471931397.724398 PERU | 1994 | 471350206.974149 PERU | 1993 | 467053565.944885 PERU | 1992 | 470128159.434603 ROMANIA | 1998 | 269941499.263384 ROMANIA | 1997 | 462976985.969701 ROMANIA | 1996 | 469358975.585018 ROMANIA | 1995 | 463322654.728566 ROMANIA | 1994 | 468054938.214566 ROMANIA | 1993 | 459048029.966593 ROMANIA | 1992 | 467953764.357033 RUSSIA | 1998 | 280532727.039357 RUSSIA | 1997 | 476131954.041689 RUSSIA | 1996 | 474367920.573633 RUSSIA | 1995 | 468703052.790866 RUSSIA | 1994 | 468724911.318698 RUSSIA | 1993 | 463664733.52307 RUSSIA | 1992 | 467239563.227159 SAUDI ARABIA | 1998 | 281547723.332682 SAUDI ARABIA | 1997 | 470439615.584642 SAUDI ARABIA | 1996 | 468609793.514533 SAUDI ARABIA | 1995 | 472343586.276182 SAUDI ARABIA | 1994 | 465122781.689122 SAUDI ARABIA | 1993 | 469438541.388354 SAUDI ARABIA | 1992 | 470226567.551546 UNITED KINGDOM | 1998 | 276289195.312929 UNITED KINGDOM | 1997 | 462188333.008575 UNITED KINGDOM | 1996 | 464022262.494646 UNITED KINGDOM | 1995 | 464413633.477113 UNITED KINGDOM | 1994 | 469532510.975016 UNITED KINGDOM | 1993 | 465237334.291361 UNITED KINGDOM | 1992 | 467103950.637953 UNITED STATES | 1998 | 277454761.267894 UNITED STATES | 1997 | 471257829.565829 UNITED STATES | 1996 | 473609411.415226 UNITED STATES | 1995 | 465903374.395683 UNITED STATES | 1994 | 465484907.250484 UNITED STATES | 1993 | 470085226.260535 UNITED STATES | 1992 | 477597501.133583 VIETNAM | 1998 | 275132561.825067 VIETNAM | 1997 | 464231952.489591 VIETNAM | 1996 | 464544349.399484 VIETNAM | 1995 | 463372406.615497 VIETNAM | 1994 | 464413354.018598 VIETNAM | 1993 | 456608579.513735 VIETNAM | 1992 | 463541470.456142 (175 rows) COMMIT; COMMIT