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 '%powder%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264848381.871548 ALGERIA | 1997 | 452224329.216278 ALGERIA | 1996 | 460446047.904116 ALGERIA | 1995 | 454425281.277962 ALGERIA | 1994 | 454358337.543413 ALGERIA | 1993 | 451708706.696688 ALGERIA | 1992 | 453382000.408143 ARGENTINA | 1998 | 273313216.303947 ARGENTINA | 1997 | 459885629.841591 ARGENTINA | 1996 | 462397994.46804 ARGENTINA | 1995 | 465084878.110733 ARGENTINA | 1994 | 467087014.285788 ARGENTINA | 1993 | 465274015.49411 ARGENTINA | 1992 | 464620318.950144 BRAZIL | 1998 | 274530643.288007 BRAZIL | 1997 | 471134331.222351 BRAZIL | 1996 | 472806786.894646 BRAZIL | 1995 | 470709475.046142 BRAZIL | 1994 | 469214189.598013 BRAZIL | 1993 | 464792107.192737 BRAZIL | 1992 | 469630437.52802 CANADA | 1998 | 280364927.061277 CANADA | 1997 | 475236174.218753 CANADA | 1996 | 477575596.191003 CANADA | 1995 | 479223998.418573 CANADA | 1994 | 480724052.57048 CANADA | 1993 | 476834986.658959 CANADA | 1992 | 476207748.66803 CHINA | 1998 | 270644423.005319 CHINA | 1997 | 464180784.91291 CHINA | 1996 | 457021785.49558 CHINA | 1995 | 455146645.114997 CHINA | 1994 | 456927338.804387 CHINA | 1993 | 465004932.936314 CHINA | 1992 | 464893296.023105 EGYPT | 1998 | 275460574.889051 EGYPT | 1997 | 463845620.423007 EGYPT | 1996 | 466114251.702914 EGYPT | 1995 | 462336922.723147 EGYPT | 1994 | 468098101.404396 EGYPT | 1993 | 474036963.49144 EGYPT | 1992 | 466185476.606901 ETHIOPIA | 1998 | 268010507.200315 ETHIOPIA | 1997 | 455013935.970544 ETHIOPIA | 1996 | 460667184.709799 ETHIOPIA | 1995 | 457791143.747642 ETHIOPIA | 1994 | 454934021.520017 ETHIOPIA | 1993 | 464552467.743193 ETHIOPIA | 1992 | 462708878.202583 FRANCE | 1998 | 265240659.106765 FRANCE | 1997 | 448374014.71857 FRANCE | 1996 | 464847661.29499 FRANCE | 1995 | 461662337.848758 FRANCE | 1994 | 453842188.301085 FRANCE | 1993 | 453901186.781315 FRANCE | 1992 | 449263062.043911 GERMANY | 1998 | 276514500.195951 GERMANY | 1997 | 473322547.975064 GERMANY | 1996 | 470735960.487284 GERMANY | 1995 | 470018239.647964 GERMANY | 1994 | 470598620.348763 GERMANY | 1993 | 480104982.549456 GERMANY | 1992 | 476871133.586881 INDIA | 1998 | 271849616.127878 INDIA | 1997 | 466922571.936117 INDIA | 1996 | 469580459.459077 INDIA | 1995 | 463726048.471701 INDIA | 1994 | 464135119.204075 INDIA | 1993 | 472434886.143421 INDIA | 1992 | 468224569.275174 INDONESIA | 1998 | 267244628.690135 INDONESIA | 1997 | 468861396.022124 INDONESIA | 1996 | 464340443.292533 INDONESIA | 1995 | 459828821.931468 INDONESIA | 1994 | 463475400.740924 INDONESIA | 1993 | 458237557.21694 INDONESIA | 1992 | 458585782.707902 IRAN | 1998 | 264163346.221684 IRAN | 1997 | 454286797.513996 IRAN | 1996 | 463166299.428463 IRAN | 1995 | 463734181.798956 IRAN | 1994 | 453359835.022768 IRAN | 1993 | 455446639.171736 IRAN | 1992 | 470098697.288389 IRAQ | 1998 | 273454046.40416 IRAQ | 1997 | 476010094.620533 IRAQ | 1996 | 468656022.58098 IRAQ | 1995 | 465744590.869966 IRAQ | 1994 | 472574738.943923 IRAQ | 1993 | 476997280.730828 IRAQ | 1992 | 464653690.036058 JAPAN | 1998 | 273325099.437999 JAPAN | 1997 | 470858601.450621 JAPAN | 1996 | 466039645.866626 JAPAN | 1995 | 465195944.131149 JAPAN | 1994 | 462450931.604775 JAPAN | 1993 | 469470065.346003 JAPAN | 1992 | 466484591.221814 JORDAN | 1998 | 265285580.360791 JORDAN | 1997 | 454325824.124168 JORDAN | 1996 | 464096596.288595 JORDAN | 1995 | 454431293.90079 JORDAN | 1994 | 461969805.564452 JORDAN | 1993 | 461065838.562018 JORDAN | 1992 | 465224998.82554 KENYA | 1998 | 280282200.123046 KENYA | 1997 | 479247156.8204 KENYA | 1996 | 482004271.258453 KENYA | 1995 | 467107485.996513 KENYA | 1994 | 474199988.044818 KENYA | 1993 | 474958502.949917 KENYA | 1992 | 477046916.712142 MOROCCO | 1998 | 271888529.091437 MOROCCO | 1997 | 462640335.380237 MOROCCO | 1996 | 467247500.65382 MOROCCO | 1995 | 473123052.36652 MOROCCO | 1994 | 467586749.50657 MOROCCO | 1993 | 468766353.076016 MOROCCO | 1992 | 473277144.739546 MOZAMBIQUE | 1998 | 273153134.848803 MOZAMBIQUE | 1997 | 469405317.233079 MOZAMBIQUE | 1996 | 463228364.882044 MOZAMBIQUE | 1995 | 462396014.340579 MOZAMBIQUE | 1994 | 461692635.453042 MOZAMBIQUE | 1993 | 467060192.211163 MOZAMBIQUE | 1992 | 464146397.941987 PERU | 1998 | 266583826.409352 PERU | 1997 | 461189108.59788 PERU | 1996 | 475719811.906572 PERU | 1995 | 468351236.721989 PERU | 1994 | 467181942.474824 PERU | 1993 | 477400158.555735 PERU | 1992 | 460279705.411014 ROMANIA | 1998 | 282607398.894432 ROMANIA | 1997 | 469418666.540002 ROMANIA | 1996 | 467075656.287128 ROMANIA | 1995 | 479205484.453181 ROMANIA | 1994 | 470689764.334967 ROMANIA | 1993 | 461481101.22006 ROMANIA | 1992 | 470740751.35586 RUSSIA | 1998 | 278665934.117388 RUSSIA | 1997 | 472098982.193549 RUSSIA | 1996 | 478425629.895928 RUSSIA | 1995 | 458975337.409883 RUSSIA | 1994 | 474967485.330637 RUSSIA | 1993 | 471716358.625911 RUSSIA | 1992 | 466474336.765239 SAUDI ARABIA | 1998 | 275856614.750148 SAUDI ARABIA | 1997 | 475537524.956752 SAUDI ARABIA | 1996 | 474979734.022523 SAUDI ARABIA | 1995 | 477426621.373809 SAUDI ARABIA | 1994 | 479202711.645871 SAUDI ARABIA | 1993 | 467879750.231574 SAUDI ARABIA | 1992 | 464203102.192493 UNITED KINGDOM | 1998 | 271685970.792682 UNITED KINGDOM | 1997 | 459853952.880893 UNITED KINGDOM | 1996 | 465137271.306173 UNITED KINGDOM | 1995 | 456893335.229209 UNITED KINGDOM | 1994 | 454705224.324696 UNITED KINGDOM | 1993 | 461884559.135861 UNITED KINGDOM | 1992 | 462657082.675209 UNITED STATES | 1998 | 275933618.091798 UNITED STATES | 1997 | 463311187.544664 UNITED STATES | 1996 | 466527106.834006 UNITED STATES | 1995 | 466687767.242565 UNITED STATES | 1994 | 469138459.457042 UNITED STATES | 1993 | 462293547.323765 UNITED STATES | 1992 | 463573052.252011 VIETNAM | 1998 | 271251659.959628 VIETNAM | 1997 | 458837508.059415 VIETNAM | 1996 | 463655466.143787 VIETNAM | 1995 | 454065432.270509 VIETNAM | 1994 | 465205796.30999 VIETNAM | 1993 | 474673178.97996 VIETNAM | 1992 | 472959912.884478 (175 rows) COMMIT; COMMIT