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 '%purple%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 274427034.283401 ALGERIA | 1997 | 460109383.673872 ALGERIA | 1996 | 458030998.284186 ALGERIA | 1995 | 464280284.84446 ALGERIA | 1994 | 466139928.835446 ALGERIA | 1993 | 461586340.039251 ALGERIA | 1992 | 469731183.545671 ARGENTINA | 1998 | 273455301.219241 ARGENTINA | 1997 | 470185775.601376 ARGENTINA | 1996 | 474354150.344757 ARGENTINA | 1995 | 470198050.63942 ARGENTINA | 1994 | 464174791.097687 ARGENTINA | 1993 | 478575536.888551 ARGENTINA | 1992 | 475394916.077223 BRAZIL | 1998 | 274023194.816433 BRAZIL | 1997 | 470810638.846097 BRAZIL | 1996 | 466691026.411845 BRAZIL | 1995 | 468956630.216152 BRAZIL | 1994 | 473177761.714165 BRAZIL | 1993 | 466941632.333354 BRAZIL | 1992 | 468987823.066958 CANADA | 1998 | 280111584.54606 CANADA | 1997 | 475940981.698889 CANADA | 1996 | 481936911.028881 CANADA | 1995 | 473040248.338055 CANADA | 1994 | 477003654.498813 CANADA | 1993 | 472942193.884303 CANADA | 1992 | 476918555.252649 CHINA | 1998 | 275204671.079745 CHINA | 1997 | 462140760.010369 CHINA | 1996 | 474951811.244131 CHINA | 1995 | 472661896.886599 CHINA | 1994 | 463818321.629001 CHINA | 1993 | 472167170.39199 CHINA | 1992 | 471834015.751152 EGYPT | 1998 | 269776210.141081 EGYPT | 1997 | 459282604.252978 EGYPT | 1996 | 460184456.978945 EGYPT | 1995 | 464792331.157829 EGYPT | 1994 | 464464091.264845 EGYPT | 1993 | 462358816.44765 EGYPT | 1992 | 467762685.094135 ETHIOPIA | 1998 | 266381065.847823 ETHIOPIA | 1997 | 453380530.659471 ETHIOPIA | 1996 | 452546248.376289 ETHIOPIA | 1995 | 454895358.485209 ETHIOPIA | 1994 | 450426061.476844 ETHIOPIA | 1993 | 458685029.950195 ETHIOPIA | 1992 | 463903302.011099 FRANCE | 1998 | 265466220.193973 FRANCE | 1997 | 456444608.076337 FRANCE | 1996 | 461032786.284619 FRANCE | 1995 | 457831129.438464 FRANCE | 1994 | 454161145.347472 FRANCE | 1993 | 461865505.102502 FRANCE | 1992 | 457925751.63578 GERMANY | 1998 | 278540043.962083 GERMANY | 1997 | 484885677.037732 GERMANY | 1996 | 475780103.014739 GERMANY | 1995 | 477872863.307873 GERMANY | 1994 | 477531229.950821 GERMANY | 1993 | 471104675.014606 GERMANY | 1992 | 482042380.828297 INDIA | 1998 | 286972909.673568 INDIA | 1997 | 481684561.399678 INDIA | 1996 | 482766258.111394 INDIA | 1995 | 483782755.168052 INDIA | 1994 | 488051975.616575 INDIA | 1993 | 478928341.46817 INDIA | 1992 | 492120017.804511 INDONESIA | 1998 | 273278330.182109 INDONESIA | 1997 | 457415980.525162 INDONESIA | 1996 | 466807504.893884 INDONESIA | 1995 | 463948962.404808 INDONESIA | 1994 | 469783706.078342 INDONESIA | 1993 | 467028336.151475 INDONESIA | 1992 | 466382473.993195 IRAN | 1998 | 274997907.515932 IRAN | 1997 | 465688468.085542 IRAN | 1996 | 453209049.265899 IRAN | 1995 | 466729169.646768 IRAN | 1994 | 462040472.348609 IRAN | 1993 | 455333644.964859 IRAN | 1992 | 462805058.486294 IRAQ | 1998 | 281562208.515034 IRAQ | 1997 | 468517966.669882 IRAQ | 1996 | 477363128.33562 IRAQ | 1995 | 472683678.7666 IRAQ | 1994 | 467148821.431517 IRAQ | 1993 | 469231565.602565 IRAQ | 1992 | 478521899.668582 JAPAN | 1998 | 274557581.37408 JAPAN | 1997 | 463203555.579194 JAPAN | 1996 | 463708252.448757 JAPAN | 1995 | 466123775.006247 JAPAN | 1994 | 461987194.494686 JAPAN | 1993 | 465616694.13424 JAPAN | 1992 | 467804416.908123 JORDAN | 1998 | 273316588.945083 JORDAN | 1997 | 462972723.635191 JORDAN | 1996 | 468640202.747692 JORDAN | 1995 | 459339593.951344 JORDAN | 1994 | 467112870.229312 JORDAN | 1993 | 473026163.076343 JORDAN | 1992 | 467331588.285196 KENYA | 1998 | 280026261.280278 KENYA | 1997 | 474940882.145013 KENYA | 1996 | 473850005.859365 KENYA | 1995 | 476968301.704529 KENYA | 1994 | 471919312.449052 KENYA | 1993 | 477298593.577277 KENYA | 1992 | 478672853.762453 MOROCCO | 1998 | 278253357.406719 MOROCCO | 1997 | 477591838.982747 MOROCCO | 1996 | 480986123.300559 MOROCCO | 1995 | 483625315.721898 MOROCCO | 1994 | 482643955.74087 MOROCCO | 1993 | 474943480.407408 MOROCCO | 1992 | 480102986.200648 MOZAMBIQUE | 1998 | 263025418.342423 MOZAMBIQUE | 1997 | 455284324.822254 MOZAMBIQUE | 1996 | 460400470.624949 MOZAMBIQUE | 1995 | 459387198.693827 MOZAMBIQUE | 1994 | 452872507.303358 MOZAMBIQUE | 1993 | 459529244.488399 MOZAMBIQUE | 1992 | 453786321.224062 PERU | 1998 | 278269980.511317 PERU | 1997 | 456687764.046856 PERU | 1996 | 469702292.469214 PERU | 1995 | 459359145.331495 PERU | 1994 | 460111663.045593 PERU | 1993 | 462436381.187884 PERU | 1992 | 467975790.267684 ROMANIA | 1998 | 276703458.040582 ROMANIA | 1997 | 471700521.181661 ROMANIA | 1996 | 474543401.798103 ROMANIA | 1995 | 473884467.96323 ROMANIA | 1994 | 470233978.102713 ROMANIA | 1993 | 472993562.206124 ROMANIA | 1992 | 472171719.804017 RUSSIA | 1998 | 280479931.181979 RUSSIA | 1997 | 466712234.291368 RUSSIA | 1996 | 474023494.362456 RUSSIA | 1995 | 473981446.93024 RUSSIA | 1994 | 472997371.574145 RUSSIA | 1993 | 471907502.049913 RUSSIA | 1992 | 470639890.079687 SAUDI ARABIA | 1998 | 276621441.641441 SAUDI ARABIA | 1997 | 470105276.578472 SAUDI ARABIA | 1996 | 467691654.513178 SAUDI ARABIA | 1995 | 466259867.472956 SAUDI ARABIA | 1994 | 468782478.227695 SAUDI ARABIA | 1993 | 473558987.234468 SAUDI ARABIA | 1992 | 470605323.946825 UNITED KINGDOM | 1998 | 269640952.430912 UNITED KINGDOM | 1997 | 467854860.999975 UNITED KINGDOM | 1996 | 467776554.148102 UNITED KINGDOM | 1995 | 466621459.045931 UNITED KINGDOM | 1994 | 459309955.580115 UNITED KINGDOM | 1993 | 468423316.145519 UNITED KINGDOM | 1992 | 464500192.440889 UNITED STATES | 1998 | 273049204.415853 UNITED STATES | 1997 | 471708691.106124 UNITED STATES | 1996 | 474505614.306419 UNITED STATES | 1995 | 464146366.169019 UNITED STATES | 1994 | 476749242.166055 UNITED STATES | 1993 | 468841255.913931 UNITED STATES | 1992 | 470437632.212752 VIETNAM | 1998 | 279984656.96001 VIETNAM | 1997 | 467669385.923401 VIETNAM | 1996 | 472025694.531688 VIETNAM | 1995 | 470484219.084239 VIETNAM | 1994 | 473978475.609793 VIETNAM | 1993 | 476952205.113204 VIETNAM | 1992 | 474930070.011902 (175 rows) COMMIT; COMMIT