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 '%red%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271894822.40851 ALGERIA | 1997 | 454337131.921889 ALGERIA | 1996 | 457850587.725718 ALGERIA | 1995 | 455301514.427188 ALGERIA | 1994 | 457410927.985351 ALGERIA | 1993 | 461052613.844135 ALGERIA | 1992 | 456112937.321001 ARGENTINA | 1998 | 277059081.137595 ARGENTINA | 1997 | 466623597.61792 ARGENTINA | 1996 | 477673884.33847 ARGENTINA | 1995 | 469340362.394213 ARGENTINA | 1994 | 469138468.15186 ARGENTINA | 1993 | 465146618.526956 ARGENTINA | 1992 | 469810779.159202 BRAZIL | 1998 | 272926774.818167 BRAZIL | 1997 | 466007153.980719 BRAZIL | 1996 | 471104434.201244 BRAZIL | 1995 | 472917723.774902 BRAZIL | 1994 | 463523396.347117 BRAZIL | 1993 | 468173978.509528 BRAZIL | 1992 | 467740143.359057 CANADA | 1998 | 282230503.395506 CANADA | 1997 | 477560958.327518 CANADA | 1996 | 483938995.130332 CANADA | 1995 | 486392801.417126 CANADA | 1994 | 488341250.846972 CANADA | 1993 | 474909188.295204 CANADA | 1992 | 479053231.394601 CHINA | 1998 | 281539346.040669 CHINA | 1997 | 476231555.191344 CHINA | 1996 | 467965086.709052 CHINA | 1995 | 466716229.696102 CHINA | 1994 | 470022963.997643 CHINA | 1993 | 471770582.703689 CHINA | 1992 | 479034342.839201 EGYPT | 1998 | 276145958.969368 EGYPT | 1997 | 468462732.469123 EGYPT | 1996 | 469579938.020986 EGYPT | 1995 | 468514531.255039 EGYPT | 1994 | 472529559.334562 EGYPT | 1993 | 466550572.870791 EGYPT | 1992 | 473422585.312649 ETHIOPIA | 1998 | 270520325.121503 ETHIOPIA | 1997 | 461796613.630351 ETHIOPIA | 1996 | 465046606.763151 ETHIOPIA | 1995 | 470385728.953798 ETHIOPIA | 1994 | 462563921.762149 ETHIOPIA | 1993 | 464542910.018701 ETHIOPIA | 1992 | 455847252.874496 FRANCE | 1998 | 279546751.556696 FRANCE | 1997 | 476019854.705068 FRANCE | 1996 | 479736920.272706 FRANCE | 1995 | 474556509.362816 FRANCE | 1994 | 475208065.882918 FRANCE | 1993 | 467490136.723483 FRANCE | 1992 | 470778733.914859 GERMANY | 1998 | 280526937.332732 GERMANY | 1997 | 476022275.057054 GERMANY | 1996 | 481718248.522851 GERMANY | 1995 | 480432333.706981 GERMANY | 1994 | 482823843.167386 GERMANY | 1993 | 478992905.36007 GERMANY | 1992 | 478784739.717077 INDIA | 1998 | 280376880.100439 INDIA | 1997 | 474380971.091144 INDIA | 1996 | 473505044.532632 INDIA | 1995 | 476139811.65246 INDIA | 1994 | 477172343.435644 INDIA | 1993 | 468699496.740245 INDIA | 1992 | 481806546.950103 INDONESIA | 1998 | 273519801.062165 INDONESIA | 1997 | 458108018.50839 INDONESIA | 1996 | 464370316.132677 INDONESIA | 1995 | 458994144.249228 INDONESIA | 1994 | 462542197.988496 INDONESIA | 1993 | 459642190.712691 INDONESIA | 1992 | 451763514.410498 IRAN | 1998 | 271422291.780967 IRAN | 1997 | 465428318.276932 IRAN | 1996 | 463692470.412433 IRAN | 1995 | 463819859.881198 IRAN | 1994 | 466833593.763257 IRAN | 1993 | 461594275.58627 IRAN | 1992 | 465419508.864379 IRAQ | 1998 | 286170372.882101 IRAQ | 1997 | 471420781.598868 IRAQ | 1996 | 487901922.879787 IRAQ | 1995 | 476194564.97671 IRAQ | 1994 | 474675440.208432 IRAQ | 1993 | 481177167.606455 IRAQ | 1992 | 475800707.415753 JAPAN | 1998 | 277096183.99742 JAPAN | 1997 | 466457317.480264 JAPAN | 1996 | 460228814.478479 JAPAN | 1995 | 469867891.167473 JAPAN | 1994 | 464146298.008946 JAPAN | 1993 | 469593969.480145 JAPAN | 1992 | 464884319.220252 JORDAN | 1998 | 271003193.083514 JORDAN | 1997 | 457526577.431312 JORDAN | 1996 | 459268859.516398 JORDAN | 1995 | 455264108.658688 JORDAN | 1994 | 459672834.505431 JORDAN | 1993 | 457434879.829276 JORDAN | 1992 | 459982187.542449 KENYA | 1998 | 271392207.542089 KENYA | 1997 | 468912660.049781 KENYA | 1996 | 476678098.354972 KENYA | 1995 | 474800862.580231 KENYA | 1994 | 479285238.693637 KENYA | 1993 | 471137774.467418 KENYA | 1992 | 472561445.370146 MOROCCO | 1998 | 279171303.740069 MOROCCO | 1997 | 466302142.021985 MOROCCO | 1996 | 469994218.983561 MOROCCO | 1995 | 470853102.198811 MOROCCO | 1994 | 473450577.949152 MOROCCO | 1993 | 465914960.305567 MOROCCO | 1992 | 474634308.989011 MOZAMBIQUE | 1998 | 270872090.316991 MOZAMBIQUE | 1997 | 459711358.277556 MOZAMBIQUE | 1996 | 454054792.032609 MOZAMBIQUE | 1995 | 458146704.90984 MOZAMBIQUE | 1994 | 460538564.804343 MOZAMBIQUE | 1993 | 451057907.621123 MOZAMBIQUE | 1992 | 454595156.456544 PERU | 1998 | 280211125.364928 PERU | 1997 | 468571149.504369 PERU | 1996 | 469019152.313493 PERU | 1995 | 466529506.70823 PERU | 1994 | 472113574.397058 PERU | 1993 | 459218948.767338 PERU | 1992 | 466927119.524795 ROMANIA | 1998 | 270008208.086525 ROMANIA | 1997 | 462816544.328151 ROMANIA | 1996 | 466936601.142166 ROMANIA | 1995 | 461825533.537393 ROMANIA | 1994 | 453686020.819763 ROMANIA | 1993 | 461811101.489578 ROMANIA | 1992 | 463838799.121105 RUSSIA | 1998 | 276345037.888265 RUSSIA | 1997 | 473191944.667477 RUSSIA | 1996 | 471666713.984318 RUSSIA | 1995 | 478660824.537179 RUSSIA | 1994 | 463184413.323409 RUSSIA | 1993 | 474904940.614429 RUSSIA | 1992 | 476749187.75501 SAUDI ARABIA | 1998 | 271178658.918744 SAUDI ARABIA | 1997 | 463940393.452411 SAUDI ARABIA | 1996 | 466410213.255015 SAUDI ARABIA | 1995 | 464957593.481078 SAUDI ARABIA | 1994 | 470555452.705276 SAUDI ARABIA | 1993 | 461393185.75586 SAUDI ARABIA | 1992 | 470625153.368439 UNITED KINGDOM | 1998 | 272851228.019004 UNITED KINGDOM | 1997 | 466781307.301735 UNITED KINGDOM | 1996 | 470096133.780227 UNITED KINGDOM | 1995 | 466201653.909478 UNITED KINGDOM | 1994 | 466347034.756007 UNITED KINGDOM | 1993 | 473816931.836141 UNITED KINGDOM | 1992 | 466175072.523122 UNITED STATES | 1998 | 275089976.172988 UNITED STATES | 1997 | 473651489.642509 UNITED STATES | 1996 | 468447462.193411 UNITED STATES | 1995 | 462346761.126674 UNITED STATES | 1994 | 468118968.944136 UNITED STATES | 1993 | 479909234.926142 UNITED STATES | 1992 | 472902574.25641 VIETNAM | 1998 | 277136995.790574 VIETNAM | 1997 | 473612231.149654 VIETNAM | 1996 | 476840449.641554 VIETNAM | 1995 | 469185361.600644 VIETNAM | 1994 | 468875938.626795 VIETNAM | 1993 | 466073175.62375 VIETNAM | 1992 | 476842399.269922 (175 rows) COMMIT; COMMIT