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 '%ivory%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 262246388.643132 ALGERIA | 1997 | 458760328.709178 ALGERIA | 1996 | 461363831.038981 ALGERIA | 1995 | 461589034.997985 ALGERIA | 1994 | 459845237.403041 ALGERIA | 1993 | 454411717.319562 ALGERIA | 1992 | 468579753.070442 ARGENTINA | 1998 | 267248395.866024 ARGENTINA | 1997 | 455412007.611156 ARGENTINA | 1996 | 460619275.999289 ARGENTINA | 1995 | 454097110.736813 ARGENTINA | 1994 | 455262182.088591 ARGENTINA | 1993 | 458910217.813906 ARGENTINA | 1992 | 459160770.89447 BRAZIL | 1998 | 272351301.717549 BRAZIL | 1997 | 468379808.154503 BRAZIL | 1996 | 474311748.24501 BRAZIL | 1995 | 463045867.858805 BRAZIL | 1994 | 462437797.3904 BRAZIL | 1993 | 464234995.56102 BRAZIL | 1992 | 471753428.301918 CANADA | 1998 | 271696685.072528 CANADA | 1997 | 470044142.287762 CANADA | 1996 | 464772510.294385 CANADA | 1995 | 461322846.795285 CANADA | 1994 | 463927365.917833 CANADA | 1993 | 476265666.279169 CANADA | 1992 | 461814404.163848 CHINA | 1998 | 277159033.400349 CHINA | 1997 | 467031311.938012 CHINA | 1996 | 465326834.563754 CHINA | 1995 | 468785611.985194 CHINA | 1994 | 473203488.801333 CHINA | 1993 | 472110835.793386 CHINA | 1992 | 467358683.959189 EGYPT | 1998 | 277382137.750755 EGYPT | 1997 | 462664839.986616 EGYPT | 1996 | 463095490.468872 EGYPT | 1995 | 461133918.793521 EGYPT | 1994 | 458712792.360064 EGYPT | 1993 | 459531097.338538 EGYPT | 1992 | 467072060.223395 ETHIOPIA | 1998 | 275352130.750693 ETHIOPIA | 1997 | 460030496.236568 ETHIOPIA | 1996 | 464563697.07443 ETHIOPIA | 1995 | 457931152.205595 ETHIOPIA | 1994 | 463400251.784367 ETHIOPIA | 1993 | 457904333.258288 ETHIOPIA | 1992 | 457376793.637687 FRANCE | 1998 | 271994687.612687 FRANCE | 1997 | 455891404.026381 FRANCE | 1996 | 456482437.83261 FRANCE | 1995 | 460565201.029972 FRANCE | 1994 | 455527113.578848 FRANCE | 1993 | 455526815.242296 FRANCE | 1992 | 450733262.299488 GERMANY | 1998 | 275756655.188624 GERMANY | 1997 | 472790477.096488 GERMANY | 1996 | 471583221.478478 GERMANY | 1995 | 468880036.524864 GERMANY | 1994 | 468252644.058657 GERMANY | 1993 | 466018086.657602 GERMANY | 1992 | 469610410.385922 INDIA | 1998 | 279215760.69764 INDIA | 1997 | 478194499.667121 INDIA | 1996 | 473603183.082865 INDIA | 1995 | 474483942.741049 INDIA | 1994 | 474981816.057444 INDIA | 1993 | 477033036.695299 INDIA | 1992 | 483097438.25924 INDONESIA | 1998 | 265621148.352504 INDONESIA | 1997 | 454498154.157625 INDONESIA | 1996 | 463200720.891073 INDONESIA | 1995 | 455229566.939561 INDONESIA | 1994 | 457726522.741965 INDONESIA | 1993 | 463521914.792326 INDONESIA | 1992 | 462098711.532172 IRAN | 1998 | 267107491.690475 IRAN | 1997 | 466945448.622799 IRAN | 1996 | 468659716.821076 IRAN | 1995 | 464788612.616545 IRAN | 1994 | 461404964.564947 IRAN | 1993 | 464627124.877455 IRAN | 1992 | 465421452.721823 IRAQ | 1998 | 279185419.337111 IRAQ | 1997 | 469988144.59494 IRAQ | 1996 | 466947202.131456 IRAQ | 1995 | 463668176.947467 IRAQ | 1994 | 465985950.873155 IRAQ | 1993 | 466583422.03849 IRAQ | 1992 | 470671412.764078 JAPAN | 1998 | 269816903.676465 JAPAN | 1997 | 465260155.801511 JAPAN | 1996 | 470165090.378529 JAPAN | 1995 | 462430040.507611 JAPAN | 1994 | 468180133.56946 JAPAN | 1993 | 469741786.118816 JAPAN | 1992 | 460744906.80843 JORDAN | 1998 | 271107889.921257 JORDAN | 1997 | 460340053.994147 JORDAN | 1996 | 459660048.316608 JORDAN | 1995 | 461668716.030203 JORDAN | 1994 | 459701127.078152 JORDAN | 1993 | 457194565.024321 JORDAN | 1992 | 459760435.005763 KENYA | 1998 | 279638951.713179 KENYA | 1997 | 468841047.584963 KENYA | 1996 | 471182165.66827 KENYA | 1995 | 469907209.292087 KENYA | 1994 | 480487429.999209 KENYA | 1993 | 466368159.177906 KENYA | 1992 | 477961708.108739 MOROCCO | 1998 | 271060323.854842 MOROCCO | 1997 | 459723127.971896 MOROCCO | 1996 | 461510516.633781 MOROCCO | 1995 | 460946474.838902 MOROCCO | 1994 | 460617663.798588 MOROCCO | 1993 | 462384430.167811 MOROCCO | 1992 | 460082841.559553 MOZAMBIQUE | 1998 | 269172608.786965 MOZAMBIQUE | 1997 | 456686698.873233 MOZAMBIQUE | 1996 | 463930305.654929 MOZAMBIQUE | 1995 | 456643028.217765 MOZAMBIQUE | 1994 | 451487020.816604 MOZAMBIQUE | 1993 | 464999663.577631 MOZAMBIQUE | 1992 | 461730910.538441 PERU | 1998 | 275298558.907617 PERU | 1997 | 476211176.344064 PERU | 1996 | 472464965.094576 PERU | 1995 | 478350431.333573 PERU | 1994 | 467980721.648352 PERU | 1993 | 466956881.974738 PERU | 1992 | 469303386.445289 ROMANIA | 1998 | 272221933.888942 ROMANIA | 1997 | 460069222.261156 ROMANIA | 1996 | 471885885.7098 ROMANIA | 1995 | 466736140.507814 ROMANIA | 1994 | 465056552.935283 ROMANIA | 1993 | 457179567.626057 ROMANIA | 1992 | 466511927.64775 RUSSIA | 1998 | 275191946.830878 RUSSIA | 1997 | 467435216.049587 RUSSIA | 1996 | 473913061.101871 RUSSIA | 1995 | 469213934.546712 RUSSIA | 1994 | 467063451.934322 RUSSIA | 1993 | 474982289.726751 RUSSIA | 1992 | 466321258.5192 SAUDI ARABIA | 1998 | 265697433.923049 SAUDI ARABIA | 1997 | 470295434.209165 SAUDI ARABIA | 1996 | 468268362.536447 SAUDI ARABIA | 1995 | 465429535.537535 SAUDI ARABIA | 1994 | 472297408.645156 SAUDI ARABIA | 1993 | 464825984.447873 SAUDI ARABIA | 1992 | 476907760.127401 UNITED KINGDOM | 1998 | 267225855.45015 UNITED KINGDOM | 1997 | 453967531.857527 UNITED KINGDOM | 1996 | 461041838.726725 UNITED KINGDOM | 1995 | 455254739.342611 UNITED KINGDOM | 1994 | 455517203.04332 UNITED KINGDOM | 1993 | 454176918.876082 UNITED KINGDOM | 1992 | 462002827.14811 UNITED STATES | 1998 | 272557119.340531 UNITED STATES | 1997 | 469276029.426919 UNITED STATES | 1996 | 475730857.152328 UNITED STATES | 1995 | 464618200.318398 UNITED STATES | 1994 | 472560377.082251 UNITED STATES | 1993 | 470525411.622872 UNITED STATES | 1992 | 459603136.431441 VIETNAM | 1998 | 273143367.904801 VIETNAM | 1997 | 455852999.390533 VIETNAM | 1996 | 462639877.223165 VIETNAM | 1995 | 455847889.173905 VIETNAM | 1994 | 460518809.863875 VIETNAM | 1993 | 459807300.219235 VIETNAM | 1992 | 460001159.610827 (175 rows) COMMIT; COMMIT