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 '%rosy%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270996344.98039 ALGERIA | 1997 | 454087646.171609 ALGERIA | 1996 | 468260129.954117 ALGERIA | 1995 | 456860583.548147 ALGERIA | 1994 | 461683869.737674 ALGERIA | 1993 | 463108635.819295 ALGERIA | 1992 | 457991663.283188 ARGENTINA | 1998 | 274472262.111681 ARGENTINA | 1997 | 466321912.949941 ARGENTINA | 1996 | 471870044.548724 ARGENTINA | 1995 | 470108727.598545 ARGENTINA | 1994 | 474222889.311296 ARGENTINA | 1993 | 472401442.814413 ARGENTINA | 1992 | 467427764.764643 BRAZIL | 1998 | 274254052.61914 BRAZIL | 1997 | 460976361.967189 BRAZIL | 1996 | 464603077.936837 BRAZIL | 1995 | 466698185.401592 BRAZIL | 1994 | 465498492.177078 BRAZIL | 1993 | 473830046.851959 BRAZIL | 1992 | 472682041.785858 CANADA | 1998 | 274180110.968682 CANADA | 1997 | 475080515.75787 CANADA | 1996 | 484936571.63891 CANADA | 1995 | 469609010.914623 CANADA | 1994 | 477269685.830884 CANADA | 1993 | 476981516.264706 CANADA | 1992 | 477348083.422899 CHINA | 1998 | 279567340.689498 CHINA | 1997 | 468141473.025571 CHINA | 1996 | 473233793.44843 CHINA | 1995 | 473089725.038324 CHINA | 1994 | 474974770.922542 CHINA | 1993 | 463828284.927652 CHINA | 1992 | 467861816.912725 EGYPT | 1998 | 271685334.166569 EGYPT | 1997 | 465575620.157002 EGYPT | 1996 | 477326345.361414 EGYPT | 1995 | 461578039.784703 EGYPT | 1994 | 466408234.669524 EGYPT | 1993 | 473632531.881606 EGYPT | 1992 | 466623622.578211 ETHIOPIA | 1998 | 275969838.359522 ETHIOPIA | 1997 | 455685178.691221 ETHIOPIA | 1996 | 462691158.551745 ETHIOPIA | 1995 | 461185959.88107 ETHIOPIA | 1994 | 458144366.495229 ETHIOPIA | 1993 | 457045698.481688 ETHIOPIA | 1992 | 459513677.809653 FRANCE | 1998 | 268957733.458531 FRANCE | 1997 | 465471699.532655 FRANCE | 1996 | 474475164.683959 FRANCE | 1995 | 468452784.758826 FRANCE | 1994 | 468332995.602143 FRANCE | 1993 | 466191812.707608 FRANCE | 1992 | 467829884.711621 GERMANY | 1998 | 271610331.009525 GERMANY | 1997 | 469718626.787455 GERMANY | 1996 | 465926103.574845 GERMANY | 1995 | 465105292.370916 GERMANY | 1994 | 462198161.747465 GERMANY | 1993 | 472104566.506877 GERMANY | 1992 | 463110884.248741 INDIA | 1998 | 277859143.510904 INDIA | 1997 | 472430405.980638 INDIA | 1996 | 480449085.507655 INDIA | 1995 | 479818138.155783 INDIA | 1994 | 470396243.232675 INDIA | 1993 | 478880838.608699 INDIA | 1992 | 474791358.677201 INDONESIA | 1998 | 276452845.131244 INDONESIA | 1997 | 467691313.319878 INDONESIA | 1996 | 463786014.304205 INDONESIA | 1995 | 467375421.182345 INDONESIA | 1994 | 471644305.605671 INDONESIA | 1993 | 463053426.17605 INDONESIA | 1992 | 470902099.298847 IRAN | 1998 | 274460004.45422 IRAN | 1997 | 472055511.051016 IRAN | 1996 | 465494106.455833 IRAN | 1995 | 463242596.65056 IRAN | 1994 | 465633759.12112 IRAN | 1993 | 469984264.235369 IRAN | 1992 | 473947739.492472 IRAQ | 1998 | 277723982.448778 IRAQ | 1997 | 470725875.066861 IRAQ | 1996 | 473046378.705005 IRAQ | 1995 | 466842548.810686 IRAQ | 1994 | 476639633.363288 IRAQ | 1993 | 475215101.693641 IRAQ | 1992 | 469655540.554448 JAPAN | 1998 | 270018572.048077 JAPAN | 1997 | 467899894.556592 JAPAN | 1996 | 458662030.993607 JAPAN | 1995 | 459994294.749764 JAPAN | 1994 | 455302035.083902 JAPAN | 1993 | 464327748.238274 JAPAN | 1992 | 461356724.244315 JORDAN | 1998 | 275911830.042391 JORDAN | 1997 | 461690788.032464 JORDAN | 1996 | 459183287.939248 JORDAN | 1995 | 462627835.081105 JORDAN | 1994 | 454999040.871427 JORDAN | 1993 | 462960524.148856 JORDAN | 1992 | 454977677.458606 KENYA | 1998 | 275728822.375892 KENYA | 1997 | 467959962.544972 KENYA | 1996 | 469008030.24668 KENYA | 1995 | 472825119.052901 KENYA | 1994 | 471119337.392235 KENYA | 1993 | 469271251.40518 KENYA | 1992 | 472992695.736368 MOROCCO | 1998 | 266111346.631217 MOROCCO | 1997 | 455835680.1078 MOROCCO | 1996 | 458106987.677861 MOROCCO | 1995 | 459149747.967464 MOROCCO | 1994 | 468744276.28125 MOROCCO | 1993 | 466366691.565073 MOROCCO | 1992 | 458674420.817888 MOZAMBIQUE | 1998 | 269165518.415089 MOZAMBIQUE | 1997 | 448400035.705341 MOZAMBIQUE | 1996 | 457671286.886126 MOZAMBIQUE | 1995 | 458223045.63643 MOZAMBIQUE | 1994 | 459527031.132249 MOZAMBIQUE | 1993 | 446991350.893008 MOZAMBIQUE | 1992 | 457069951.930054 PERU | 1998 | 276488529.992009 PERU | 1997 | 471807899.480649 PERU | 1996 | 474211839.219322 PERU | 1995 | 474853542.648628 PERU | 1994 | 470646522.466326 PERU | 1993 | 473051950.593774 PERU | 1992 | 471287812.431275 ROMANIA | 1998 | 276942073.507403 ROMANIA | 1997 | 472906495.488156 ROMANIA | 1996 | 470930036.888984 ROMANIA | 1995 | 471273205.587669 ROMANIA | 1994 | 468811276.645599 ROMANIA | 1993 | 470568660.000051 ROMANIA | 1992 | 473498246.930744 RUSSIA | 1998 | 280240762.573075 RUSSIA | 1997 | 471001543.938867 RUSSIA | 1996 | 472699458.218678 RUSSIA | 1995 | 472622213.086057 RUSSIA | 1994 | 474490842.668803 RUSSIA | 1993 | 472320403.27943 RUSSIA | 1992 | 468489062.714711 SAUDI ARABIA | 1998 | 279524734.455627 SAUDI ARABIA | 1997 | 464182525.482628 SAUDI ARABIA | 1996 | 471894863.233239 SAUDI ARABIA | 1995 | 464790273.019805 SAUDI ARABIA | 1994 | 466557204.013656 SAUDI ARABIA | 1993 | 466718193.340412 SAUDI ARABIA | 1992 | 471880139.330576 UNITED KINGDOM | 1998 | 273868649.58959 UNITED KINGDOM | 1997 | 460547531.8005 UNITED KINGDOM | 1996 | 463068814.53502 UNITED KINGDOM | 1995 | 468899910.085098 UNITED KINGDOM | 1994 | 459273384.083822 UNITED KINGDOM | 1993 | 470694562.318337 UNITED KINGDOM | 1992 | 465243567.557446 UNITED STATES | 1998 | 278394291.97092 UNITED STATES | 1997 | 464051782.943545 UNITED STATES | 1996 | 472789143.871016 UNITED STATES | 1995 | 465858595.194855 UNITED STATES | 1994 | 482602437.635648 UNITED STATES | 1993 | 475895779.10902 UNITED STATES | 1992 | 475090955.090558 VIETNAM | 1998 | 273402800.924326 VIETNAM | 1997 | 459351561.38227 VIETNAM | 1996 | 470084883.376259 VIETNAM | 1995 | 462820904.640136 VIETNAM | 1994 | 466095402.517909 VIETNAM | 1993 | 465390495.250485 VIETNAM | 1992 | 462978459.908167 (175 rows) COMMIT; COMMIT