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 '%plum%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267313705.619482 ALGERIA | 1997 | 446379666.722826 ALGERIA | 1996 | 463807030.443964 ALGERIA | 1995 | 460032954.01855 ALGERIA | 1994 | 457115693.03943 ALGERIA | 1993 | 463608558.582069 ALGERIA | 1992 | 460989095.474593 ARGENTINA | 1998 | 269995970.204659 ARGENTINA | 1997 | 467352110.038635 ARGENTINA | 1996 | 467184440.569499 ARGENTINA | 1995 | 461412260.208581 ARGENTINA | 1994 | 465999960.43504 ARGENTINA | 1993 | 465697350.281738 ARGENTINA | 1992 | 456508078.837993 BRAZIL | 1998 | 273825698.039299 BRAZIL | 1997 | 482305291.352383 BRAZIL | 1996 | 473183516.194887 BRAZIL | 1995 | 468179469.858997 BRAZIL | 1994 | 469275981.448676 BRAZIL | 1993 | 483546920.323856 BRAZIL | 1992 | 474031805.715158 CANADA | 1998 | 271291227.286982 CANADA | 1997 | 468534106.115488 CANADA | 1996 | 475593728.972409 CANADA | 1995 | 473676350.143779 CANADA | 1994 | 483205642.178108 CANADA | 1993 | 479496230.659946 CANADA | 1992 | 473328056.706558 CHINA | 1998 | 273548586.520353 CHINA | 1997 | 465828015.598432 CHINA | 1996 | 476008464.875073 CHINA | 1995 | 472949930.864084 CHINA | 1994 | 475471785.062191 CHINA | 1993 | 467191006.17039 CHINA | 1992 | 468676692.690103 EGYPT | 1998 | 273963815.844446 EGYPT | 1997 | 464965350.268558 EGYPT | 1996 | 471671008.559932 EGYPT | 1995 | 463446026.808911 EGYPT | 1994 | 455698007.189361 EGYPT | 1993 | 461256118.221525 EGYPT | 1992 | 465765994.498914 ETHIOPIA | 1998 | 268639671.520026 ETHIOPIA | 1997 | 457131472.573827 ETHIOPIA | 1996 | 465482176.365097 ETHIOPIA | 1995 | 465864653.417612 ETHIOPIA | 1994 | 463666196.727103 ETHIOPIA | 1993 | 456852716.954418 ETHIOPIA | 1992 | 467503482.52254 FRANCE | 1998 | 271231669.05016 FRANCE | 1997 | 464085705.813999 FRANCE | 1996 | 467088042.417177 FRANCE | 1995 | 470235191.438778 FRANCE | 1994 | 465371477.454794 FRANCE | 1993 | 465687032.913878 FRANCE | 1992 | 464389051.412897 GERMANY | 1998 | 272606786.654105 GERMANY | 1997 | 463350907.518505 GERMANY | 1996 | 465248035.203479 GERMANY | 1995 | 469482059.817851 GERMANY | 1994 | 469276433.464085 GERMANY | 1993 | 468988056.560691 GERMANY | 1992 | 473064547.959286 INDIA | 1998 | 271105317.062375 INDIA | 1997 | 468480646.703826 INDIA | 1996 | 477873908.448058 INDIA | 1995 | 476813473.915365 INDIA | 1994 | 466713857.886197 INDIA | 1993 | 470177153.625808 INDIA | 1992 | 479242532.549218 INDONESIA | 1998 | 271462125.602659 INDONESIA | 1997 | 468677070.511232 INDONESIA | 1996 | 464779983.380044 INDONESIA | 1995 | 462446871.902662 INDONESIA | 1994 | 459285744.196434 INDONESIA | 1993 | 465722208.504937 INDONESIA | 1992 | 468596432.776844 IRAN | 1998 | 274327529.299407 IRAN | 1997 | 469067377.356724 IRAN | 1996 | 464538133.581958 IRAN | 1995 | 469182703.550314 IRAN | 1994 | 465136160.997354 IRAN | 1993 | 463489110.323538 IRAN | 1992 | 461753160.81644 IRAQ | 1998 | 271710490.728739 IRAQ | 1997 | 468343254.086812 IRAQ | 1996 | 482324504.466983 IRAQ | 1995 | 459218894.491465 IRAQ | 1994 | 471106730.417125 IRAQ | 1993 | 473841885.663255 IRAQ | 1992 | 466462125.472673 JAPAN | 1998 | 274666253.59248 JAPAN | 1997 | 470074523.170961 JAPAN | 1996 | 472166790.594025 JAPAN | 1995 | 466684291.530972 JAPAN | 1994 | 458979997.831152 JAPAN | 1993 | 468254749.172494 JAPAN | 1992 | 461587720.453515 JORDAN | 1998 | 270117194.385094 JORDAN | 1997 | 469963998.560895 JORDAN | 1996 | 462786038.352304 JORDAN | 1995 | 462875214.468466 JORDAN | 1994 | 458347407.566269 JORDAN | 1993 | 472267452.576217 JORDAN | 1992 | 463083289.268283 KENYA | 1998 | 281149393.083745 KENYA | 1997 | 473828206.843941 KENYA | 1996 | 475816151.641302 KENYA | 1995 | 477136509.754521 KENYA | 1994 | 480607879.359878 KENYA | 1993 | 474840872.430515 KENYA | 1992 | 477802729.91141 MOROCCO | 1998 | 276789199.475323 MOROCCO | 1997 | 471729064.437352 MOROCCO | 1996 | 462950118.972191 MOROCCO | 1995 | 464386179.767605 MOROCCO | 1994 | 470931852.371951 MOROCCO | 1993 | 463443841.556735 MOROCCO | 1992 | 464835619.443499 MOZAMBIQUE | 1998 | 266143744.034133 MOZAMBIQUE | 1997 | 455303860.6245 MOZAMBIQUE | 1996 | 454577781.823466 MOZAMBIQUE | 1995 | 449479561.732966 MOZAMBIQUE | 1994 | 450875736.99671 MOZAMBIQUE | 1993 | 464064674.047383 MOZAMBIQUE | 1992 | 459557604.809826 PERU | 1998 | 271989443.628989 PERU | 1997 | 460929142.40078 PERU | 1996 | 459385483.66965 PERU | 1995 | 454424615.258273 PERU | 1994 | 455546117.866314 PERU | 1993 | 464448126.074275 PERU | 1992 | 463126274.337037 ROMANIA | 1998 | 275378473.430813 ROMANIA | 1997 | 456343981.029922 ROMANIA | 1996 | 464494640.533709 ROMANIA | 1995 | 467684945.13053 ROMANIA | 1994 | 469209073.009948 ROMANIA | 1993 | 459964577.083486 ROMANIA | 1992 | 456466458.143351 RUSSIA | 1998 | 277346783.014175 RUSSIA | 1997 | 476087479.288927 RUSSIA | 1996 | 471779055.830632 RUSSIA | 1995 | 477770939.627109 RUSSIA | 1994 | 471926992.801442 RUSSIA | 1993 | 463072710.597627 RUSSIA | 1992 | 467660604.328997 SAUDI ARABIA | 1998 | 273276185.38052 SAUDI ARABIA | 1997 | 468719785.916502 SAUDI ARABIA | 1996 | 468902522.457776 SAUDI ARABIA | 1995 | 469068473.206299 SAUDI ARABIA | 1994 | 462825002.110777 SAUDI ARABIA | 1993 | 465013215.94621 SAUDI ARABIA | 1992 | 471373689.08361 UNITED KINGDOM | 1998 | 267824321.224948 UNITED KINGDOM | 1997 | 466269491.460597 UNITED KINGDOM | 1996 | 459432736.813708 UNITED KINGDOM | 1995 | 462671236.328593 UNITED KINGDOM | 1994 | 455442878.254591 UNITED KINGDOM | 1993 | 461509967.392368 UNITED KINGDOM | 1992 | 464477950.389598 UNITED STATES | 1998 | 276858724.947965 UNITED STATES | 1997 | 481752724.436774 UNITED STATES | 1996 | 473695634.986846 UNITED STATES | 1995 | 473032466.497077 UNITED STATES | 1994 | 473660406.047825 UNITED STATES | 1993 | 473708777.034356 UNITED STATES | 1992 | 471343026.297099 VIETNAM | 1998 | 274886214.907546 VIETNAM | 1997 | 467280092.999828 VIETNAM | 1996 | 481667601.145261 VIETNAM | 1995 | 467167596.631162 VIETNAM | 1994 | 476161484.286193 VIETNAM | 1993 | 476877944.46697 VIETNAM | 1992 | 471431767.611161 (175 rows) COMMIT; COMMIT