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 '%blue%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 262195731.293709 ALGERIA | 1997 | 451082762.739713 ALGERIA | 1996 | 459644340.808371 ALGERIA | 1995 | 447954836.259861 ALGERIA | 1994 | 460205672.338875 ALGERIA | 1993 | 456640767.843974 ALGERIA | 1992 | 449945535.123831 ARGENTINA | 1998 | 275379308.865143 ARGENTINA | 1997 | 468295493.856323 ARGENTINA | 1996 | 470230325.743971 ARGENTINA | 1995 | 466158646.839126 ARGENTINA | 1994 | 467485571.18843 ARGENTINA | 1993 | 466466235.41541 ARGENTINA | 1992 | 471715851.890884 BRAZIL | 1998 | 276280933.155969 BRAZIL | 1997 | 461717348.97829 BRAZIL | 1996 | 461786349.85361 BRAZIL | 1995 | 457619762.377275 BRAZIL | 1994 | 459574402.465654 BRAZIL | 1993 | 458786368.81873 BRAZIL | 1992 | 460860504.980877 CANADA | 1998 | 281480897.34293 CANADA | 1997 | 473775046.077014 CANADA | 1996 | 481825222.611693 CANADA | 1995 | 477799463.044017 CANADA | 1994 | 484163443.016148 CANADA | 1993 | 476536312.397474 CANADA | 1992 | 482910512.690894 CHINA | 1998 | 272069327.495644 CHINA | 1997 | 463352743.886214 CHINA | 1996 | 464388072.796546 CHINA | 1995 | 464380587.057121 CHINA | 1994 | 467400559.306465 CHINA | 1993 | 468208808.856095 CHINA | 1992 | 470520748.97935 EGYPT | 1998 | 271877890.643555 EGYPT | 1997 | 472881231.117223 EGYPT | 1996 | 470156844.086731 EGYPT | 1995 | 469721838.838699 EGYPT | 1994 | 471113679.61194 EGYPT | 1993 | 466948276.043643 EGYPT | 1992 | 456364780.875719 ETHIOPIA | 1998 | 262966800.665981 ETHIOPIA | 1997 | 442766429.823234 ETHIOPIA | 1996 | 451247635.243918 ETHIOPIA | 1995 | 459217610.482783 ETHIOPIA | 1994 | 452602098.995365 ETHIOPIA | 1993 | 447637408.87802 ETHIOPIA | 1992 | 459665426.89476 FRANCE | 1998 | 272674303.304447 FRANCE | 1997 | 464439972.42053 FRANCE | 1996 | 475542212.878919 FRANCE | 1995 | 471744472.153126 FRANCE | 1994 | 473010557.139781 FRANCE | 1993 | 468823061.090213 FRANCE | 1992 | 470548684.403001 GERMANY | 1998 | 276852774.140263 GERMANY | 1997 | 468491039.215299 GERMANY | 1996 | 471839976.109801 GERMANY | 1995 | 471044934.621023 GERMANY | 1994 | 469160470.402915 GERMANY | 1993 | 469651253.320737 GERMANY | 1992 | 471032975.781284 INDIA | 1998 | 273121070.97614 INDIA | 1997 | 476207624.098641 INDIA | 1996 | 482270444.830112 INDIA | 1995 | 476926632.600771 INDIA | 1994 | 472268276.494248 INDIA | 1993 | 481723066.832523 INDIA | 1992 | 484277698.459664 INDONESIA | 1998 | 270564463.962599 INDONESIA | 1997 | 459418031.954472 INDONESIA | 1996 | 469852230.959846 INDONESIA | 1995 | 461690028.895557 INDONESIA | 1994 | 463309618.780899 INDONESIA | 1993 | 462629371.657884 INDONESIA | 1992 | 468557601.121262 IRAN | 1998 | 272028739.059743 IRAN | 1997 | 466536227.672297 IRAN | 1996 | 461068378.366253 IRAN | 1995 | 468820522.662826 IRAN | 1994 | 465272370.317925 IRAN | 1993 | 469842114.381339 IRAN | 1992 | 464091897.370589 IRAQ | 1998 | 279324203.423933 IRAQ | 1997 | 479485389.173887 IRAQ | 1996 | 478237029.311859 IRAQ | 1995 | 474886172.219365 IRAQ | 1994 | 472774542.930798 IRAQ | 1993 | 473265870.962154 IRAQ | 1992 | 469207663.543197 JAPAN | 1998 | 272316190.052569 JAPAN | 1997 | 469171965.677428 JAPAN | 1996 | 464790216.684721 JAPAN | 1995 | 463468117.019679 JAPAN | 1994 | 469703107.589796 JAPAN | 1993 | 470830182.850115 JAPAN | 1992 | 465652625.691636 JORDAN | 1998 | 275058682.057444 JORDAN | 1997 | 453789883.368631 JORDAN | 1996 | 465691465.275471 JORDAN | 1995 | 467717903.902971 JORDAN | 1994 | 468910058.390426 JORDAN | 1993 | 461427312.64129 JORDAN | 1992 | 472114816.55862 KENYA | 1998 | 275763231.848774 KENYA | 1997 | 476696017.498754 KENYA | 1996 | 477537698.930483 KENYA | 1995 | 468053132.861266 KENYA | 1994 | 479050590.338964 KENYA | 1993 | 478773763.3938 KENYA | 1992 | 466056789.791493 MOROCCO | 1998 | 276094209.793893 MOROCCO | 1997 | 477419118.896709 MOROCCO | 1996 | 484028572.906494 MOROCCO | 1995 | 476412554.526732 MOROCCO | 1994 | 481775088.920403 MOROCCO | 1993 | 476025760.223469 MOROCCO | 1992 | 482460674.267339 MOZAMBIQUE | 1998 | 276885208.346435 MOZAMBIQUE | 1997 | 460636499.948785 MOZAMBIQUE | 1996 | 459680370.331255 MOZAMBIQUE | 1995 | 459527335.704711 MOZAMBIQUE | 1994 | 456827466.495357 MOZAMBIQUE | 1993 | 460516462.582851 MOZAMBIQUE | 1992 | 457803579.653944 PERU | 1998 | 271661622.186536 PERU | 1997 | 470192959.536873 PERU | 1996 | 466202250.20648 PERU | 1995 | 462963329.375725 PERU | 1994 | 464019749.202604 PERU | 1993 | 465852148.444865 PERU | 1992 | 468536635.790968 ROMANIA | 1998 | 277243782.875179 ROMANIA | 1997 | 474881441.937431 ROMANIA | 1996 | 478355558.261163 ROMANIA | 1995 | 473875576.955276 ROMANIA | 1994 | 473546284.650044 ROMANIA | 1993 | 480575963.054941 ROMANIA | 1992 | 477960560.687734 RUSSIA | 1998 | 274879463.872793 RUSSIA | 1997 | 470881612.410281 RUSSIA | 1996 | 462082088.450756 RUSSIA | 1995 | 455496312.231615 RUSSIA | 1994 | 466345813.279154 RUSSIA | 1993 | 466312616.317902 RUSSIA | 1992 | 475889777.656525 SAUDI ARABIA | 1998 | 269657468.466282 SAUDI ARABIA | 1997 | 458952408.544349 SAUDI ARABIA | 1996 | 461791481.381723 SAUDI ARABIA | 1995 | 469201982.277054 SAUDI ARABIA | 1994 | 473035999.806821 SAUDI ARABIA | 1993 | 464750890.47108 SAUDI ARABIA | 1992 | 475416135.21157 UNITED KINGDOM | 1998 | 274407672.709338 UNITED KINGDOM | 1997 | 460383479.79904 UNITED KINGDOM | 1996 | 467509240.552794 UNITED KINGDOM | 1995 | 470612445.321928 UNITED KINGDOM | 1994 | 470043864.606533 UNITED KINGDOM | 1993 | 461777051.749288 UNITED KINGDOM | 1992 | 466662033.904282 UNITED STATES | 1998 | 277017807.379793 UNITED STATES | 1997 | 474895256.736633 UNITED STATES | 1996 | 476426712.609681 UNITED STATES | 1995 | 475106037.907909 UNITED STATES | 1994 | 476840392.91397 UNITED STATES | 1993 | 470628864.480577 UNITED STATES | 1992 | 476516431.829678 VIETNAM | 1998 | 277777130.428968 VIETNAM | 1997 | 465066376.955104 VIETNAM | 1996 | 479777185.982876 VIETNAM | 1995 | 474391063.983599 VIETNAM | 1994 | 469223745.329217 VIETNAM | 1993 | 468342585.950658 VIETNAM | 1992 | 468168780.458131 (175 rows) COMMIT; COMMIT