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 '%deep%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272577319.661186 ALGERIA | 1997 | 457139562.812628 ALGERIA | 1996 | 463860383.231098 ALGERIA | 1995 | 454087177.088815 ALGERIA | 1994 | 453620407.410993 ALGERIA | 1993 | 453290364.002493 ALGERIA | 1992 | 460026066.763694 ARGENTINA | 1998 | 284140248.762069 ARGENTINA | 1997 | 479356805.205642 ARGENTINA | 1996 | 476718628.412044 ARGENTINA | 1995 | 480058706.317991 ARGENTINA | 1994 | 476501931.774482 ARGENTINA | 1993 | 483533283.571134 ARGENTINA | 1992 | 486066200.359225 BRAZIL | 1998 | 277650722.440081 BRAZIL | 1997 | 455122406.919616 BRAZIL | 1996 | 463179547.125241 BRAZIL | 1995 | 465034552.26149 BRAZIL | 1994 | 460649054.318191 BRAZIL | 1993 | 461738150.50597 BRAZIL | 1992 | 462974106.340163 CANADA | 1998 | 272509278.626337 CANADA | 1997 | 469164485.993981 CANADA | 1996 | 477853905.214624 CANADA | 1995 | 466386771.890482 CANADA | 1994 | 474912390.276375 CANADA | 1993 | 469382263.977344 CANADA | 1992 | 468553225.330155 CHINA | 1998 | 274100804.359908 CHINA | 1997 | 459118875.785047 CHINA | 1996 | 467028830.982347 CHINA | 1995 | 459561905.55615 CHINA | 1994 | 466237317.047616 CHINA | 1993 | 461913518.169601 CHINA | 1992 | 468989264.301555 EGYPT | 1998 | 270441214.056608 EGYPT | 1997 | 459582766.115665 EGYPT | 1996 | 466446531.088507 EGYPT | 1995 | 463416074.498349 EGYPT | 1994 | 463781959.129164 EGYPT | 1993 | 467031150.834379 EGYPT | 1992 | 460359418.414322 ETHIOPIA | 1998 | 260396562.310104 ETHIOPIA | 1997 | 451969458.676951 ETHIOPIA | 1996 | 449135167.987908 ETHIOPIA | 1995 | 450060721.249437 ETHIOPIA | 1994 | 447564743.772038 ETHIOPIA | 1993 | 453691857.274564 ETHIOPIA | 1992 | 462351319.5369 FRANCE | 1998 | 264482199.332417 FRANCE | 1997 | 455737543.185548 FRANCE | 1996 | 454896037.131549 FRANCE | 1995 | 456700797.418214 FRANCE | 1994 | 454472581.144802 FRANCE | 1993 | 460854068.040443 FRANCE | 1992 | 449063275.100357 GERMANY | 1998 | 274500055.371127 GERMANY | 1997 | 486565001.805679 GERMANY | 1996 | 477370385.421453 GERMANY | 1995 | 487475264.709892 GERMANY | 1994 | 476678463.094966 GERMANY | 1993 | 478482965.059609 GERMANY | 1992 | 476007283.968397 INDIA | 1998 | 277333277.496306 INDIA | 1997 | 479827311.210474 INDIA | 1996 | 474386961.429994 INDIA | 1995 | 477239009.722341 INDIA | 1994 | 464478735.232177 INDIA | 1993 | 481436382.191535 INDIA | 1992 | 475041103.538232 INDONESIA | 1998 | 271872745.995606 INDONESIA | 1997 | 462472420.260837 INDONESIA | 1996 | 462972264.8389 INDONESIA | 1995 | 453808461.321252 INDONESIA | 1994 | 458374980.246356 INDONESIA | 1993 | 459933193.188182 INDONESIA | 1992 | 458183530.138431 IRAN | 1998 | 264541857.440613 IRAN | 1997 | 461582507.384937 IRAN | 1996 | 466194109.483645 IRAN | 1995 | 470274613.573321 IRAN | 1994 | 468487039.844382 IRAN | 1993 | 466528894.185729 IRAN | 1992 | 466503545.87501 IRAQ | 1998 | 271470663.100851 IRAQ | 1997 | 463707835.974919 IRAQ | 1996 | 470405382.364091 IRAQ | 1995 | 467109215.98129 IRAQ | 1994 | 468426462.206042 IRAQ | 1993 | 468685167.603232 IRAQ | 1992 | 471905326.196464 JAPAN | 1998 | 270004045.740297 JAPAN | 1997 | 465512595.57424 JAPAN | 1996 | 457317557.351345 JAPAN | 1995 | 457912928.597342 JAPAN | 1994 | 468672544.433553 JAPAN | 1993 | 467771797.923393 JAPAN | 1992 | 463155068.515081 JORDAN | 1998 | 269802441.067611 JORDAN | 1997 | 461035205.773866 JORDAN | 1996 | 454483827.969017 JORDAN | 1995 | 464308310.173011 JORDAN | 1994 | 462672096.96965 JORDAN | 1993 | 460725932.701985 JORDAN | 1992 | 458115231.400961 KENYA | 1998 | 275786759.332854 KENYA | 1997 | 467282930.131382 KENYA | 1996 | 469269683.45032 KENYA | 1995 | 463744144.132916 KENYA | 1994 | 469662891.64482 KENYA | 1993 | 474223723.415873 KENYA | 1992 | 476549689.487165 MOROCCO | 1998 | 275983305.911049 MOROCCO | 1997 | 469075368.343801 MOROCCO | 1996 | 463971421.595439 MOROCCO | 1995 | 464017724.011398 MOROCCO | 1994 | 463532581.919092 MOROCCO | 1993 | 460442790.845349 MOROCCO | 1992 | 463198926.204758 MOZAMBIQUE | 1998 | 274911191.698565 MOZAMBIQUE | 1997 | 463857087.474126 MOZAMBIQUE | 1996 | 469278466.167919 MOZAMBIQUE | 1995 | 469183494.022481 MOZAMBIQUE | 1994 | 472651619.50087 MOZAMBIQUE | 1993 | 458285984.730619 MOZAMBIQUE | 1992 | 467406595.196536 PERU | 1998 | 270955453.190405 PERU | 1997 | 460656801.71552 PERU | 1996 | 466705711.624673 PERU | 1995 | 459842787.42627 PERU | 1994 | 455674049.440233 PERU | 1993 | 457017910.352485 PERU | 1992 | 463269597.684637 ROMANIA | 1998 | 269273330.722141 ROMANIA | 1997 | 464615077.889547 ROMANIA | 1996 | 464586274.925899 ROMANIA | 1995 | 454598841.936495 ROMANIA | 1994 | 462248251.718844 ROMANIA | 1993 | 460761732.174649 ROMANIA | 1992 | 465526608.882254 RUSSIA | 1998 | 276268395.822644 RUSSIA | 1997 | 473868286.988368 RUSSIA | 1996 | 473454315.136671 RUSSIA | 1995 | 473037412.355374 RUSSIA | 1994 | 465483489.974142 RUSSIA | 1993 | 471487924.140015 RUSSIA | 1992 | 474607958.37171 SAUDI ARABIA | 1998 | 275353656.699913 SAUDI ARABIA | 1997 | 473122701.454708 SAUDI ARABIA | 1996 | 475800209.928691 SAUDI ARABIA | 1995 | 471782322.461448 SAUDI ARABIA | 1994 | 468830534.138191 SAUDI ARABIA | 1993 | 469278997.385935 SAUDI ARABIA | 1992 | 467473909.058785 UNITED KINGDOM | 1998 | 272294548.014367 UNITED KINGDOM | 1997 | 463368242.361715 UNITED KINGDOM | 1996 | 462449346.901703 UNITED KINGDOM | 1995 | 468747919.074504 UNITED KINGDOM | 1994 | 465122449.483213 UNITED KINGDOM | 1993 | 467277258.40768 UNITED KINGDOM | 1992 | 463678781.369791 UNITED STATES | 1998 | 276783577.860672 UNITED STATES | 1997 | 472229661.807157 UNITED STATES | 1996 | 475111427.935688 UNITED STATES | 1995 | 468005125.396702 UNITED STATES | 1994 | 469654033.061111 UNITED STATES | 1993 | 464940620.979684 UNITED STATES | 1992 | 464025486.206622 VIETNAM | 1998 | 279080804.900696 VIETNAM | 1997 | 469102445.272179 VIETNAM | 1996 | 473625200.930763 VIETNAM | 1995 | 464098430.430533 VIETNAM | 1994 | 471454998.093419 VIETNAM | 1993 | 468645437.284408 VIETNAM | 1992 | 470728833.617803 (175 rows) COMMIT; COMMIT