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 '%sienna%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270202795.025387 ALGERIA | 1997 | 451920394.61473 ALGERIA | 1996 | 464302512.513859 ALGERIA | 1995 | 459736439.299776 ALGERIA | 1994 | 459478593.296709 ALGERIA | 1993 | 455871206.472713 ALGERIA | 1992 | 464958853.088699 ARGENTINA | 1998 | 278925543.455026 ARGENTINA | 1997 | 474703182.058307 ARGENTINA | 1996 | 470503951.73241 ARGENTINA | 1995 | 468791352.737843 ARGENTINA | 1994 | 470101183.452567 ARGENTINA | 1993 | 468923748.177198 ARGENTINA | 1992 | 477521701.52744 BRAZIL | 1998 | 278632159.393396 BRAZIL | 1997 | 473342773.485893 BRAZIL | 1996 | 474493282.678974 BRAZIL | 1995 | 466666428.33803 BRAZIL | 1994 | 466559320.622735 BRAZIL | 1993 | 467287246.829031 BRAZIL | 1992 | 483963311.816031 CANADA | 1998 | 278824677.781138 CANADA | 1997 | 475598909.642376 CANADA | 1996 | 479522499.583321 CANADA | 1995 | 472446959.717818 CANADA | 1994 | 481320518.383946 CANADA | 1993 | 473308530.397729 CANADA | 1992 | 478673015.862576 CHINA | 1998 | 275108577.446212 CHINA | 1997 | 469048285.126288 CHINA | 1996 | 467706050.470755 CHINA | 1995 | 474831385.838323 CHINA | 1994 | 470461961.435642 CHINA | 1993 | 471393981.457796 CHINA | 1992 | 468442387.901469 EGYPT | 1998 | 268846497.294396 EGYPT | 1997 | 464443455.032303 EGYPT | 1996 | 464070611.358351 EGYPT | 1995 | 458348032.792235 EGYPT | 1994 | 458331231.934914 EGYPT | 1993 | 463307054.854915 EGYPT | 1992 | 468287566.486659 ETHIOPIA | 1998 | 271749212.495698 ETHIOPIA | 1997 | 456268252.374999 ETHIOPIA | 1996 | 461124773.145936 ETHIOPIA | 1995 | 470749925.127606 ETHIOPIA | 1994 | 454573469.313788 ETHIOPIA | 1993 | 468003528.609314 ETHIOPIA | 1992 | 461306646.751791 FRANCE | 1998 | 272952967.534271 FRANCE | 1997 | 463626014.795412 FRANCE | 1996 | 472365769.158417 FRANCE | 1995 | 465599954.311489 FRANCE | 1994 | 461798501.087489 FRANCE | 1993 | 463793621.443907 FRANCE | 1992 | 471654845.224577 GERMANY | 1998 | 275931712.292906 GERMANY | 1997 | 472709560.505511 GERMANY | 1996 | 484187442.444649 GERMANY | 1995 | 477772072.718456 GERMANY | 1994 | 475430429.361838 GERMANY | 1993 | 472904484.278295 GERMANY | 1992 | 481047205.302447 INDIA | 1998 | 271170696.87659 INDIA | 1997 | 475354106.853022 INDIA | 1996 | 465209126.142091 INDIA | 1995 | 477868454.572791 INDIA | 1994 | 476981284.976657 INDIA | 1993 | 471011556.193333 INDIA | 1992 | 482427454.313138 INDONESIA | 1998 | 272598250.962772 INDONESIA | 1997 | 464910955.909183 INDONESIA | 1996 | 468984655.752678 INDONESIA | 1995 | 458060776.454315 INDONESIA | 1994 | 464904473.25994 INDONESIA | 1993 | 463379060.601846 INDONESIA | 1992 | 459076352.443198 IRAN | 1998 | 267409739.156701 IRAN | 1997 | 466215514.186942 IRAN | 1996 | 475994135.566018 IRAN | 1995 | 463306721.373227 IRAN | 1994 | 460685924.696387 IRAN | 1993 | 461300347.814037 IRAN | 1992 | 463310214.473558 IRAQ | 1998 | 275270666.507831 IRAQ | 1997 | 471262776.835668 IRAQ | 1996 | 472091747.097517 IRAQ | 1995 | 471860871.172606 IRAQ | 1994 | 473321893.335309 IRAQ | 1993 | 463073125.49282 IRAQ | 1992 | 471197578.641239 JAPAN | 1998 | 274413794.806802 JAPAN | 1997 | 473220808.602237 JAPAN | 1996 | 476718611.151748 JAPAN | 1995 | 462932468.116991 JAPAN | 1994 | 468989522.288321 JAPAN | 1993 | 465647731.20123 JAPAN | 1992 | 472577841.305442 JORDAN | 1998 | 269524779.964042 JORDAN | 1997 | 464378561.502837 JORDAN | 1996 | 462294322.484461 JORDAN | 1995 | 466734531.05687 JORDAN | 1994 | 463753006.074033 JORDAN | 1993 | 466129284.41682 JORDAN | 1992 | 460816003.47413 KENYA | 1998 | 275387839.733209 KENYA | 1997 | 465932417.669212 KENYA | 1996 | 476055254.687877 KENYA | 1995 | 466350534.937948 KENYA | 1994 | 472029521.706455 KENYA | 1993 | 476286432.243264 KENYA | 1992 | 470987092.21002 MOROCCO | 1998 | 273782034.129612 MOROCCO | 1997 | 474966393.463261 MOROCCO | 1996 | 470899222.311095 MOROCCO | 1995 | 466571273.254892 MOROCCO | 1994 | 470181187.822822 MOROCCO | 1993 | 472665134.407471 MOROCCO | 1992 | 466534727.860343 MOZAMBIQUE | 1998 | 270646515.373765 MOZAMBIQUE | 1997 | 461354626.803687 MOZAMBIQUE | 1996 | 460097416.780911 MOZAMBIQUE | 1995 | 459678653.878046 MOZAMBIQUE | 1994 | 461450856.980884 MOZAMBIQUE | 1993 | 451918798.908636 MOZAMBIQUE | 1992 | 454989924.853889 PERU | 1998 | 273301258.936514 PERU | 1997 | 466978200.373174 PERU | 1996 | 469338290.975716 PERU | 1995 | 468831081.96136 PERU | 1994 | 469695423.564519 PERU | 1993 | 465472833.267091 PERU | 1992 | 476346591.031255 ROMANIA | 1998 | 273321858.538656 ROMANIA | 1997 | 470155718.139386 ROMANIA | 1996 | 467931879.18572 ROMANIA | 1995 | 466785495.933325 ROMANIA | 1994 | 471433497.286763 ROMANIA | 1993 | 473097763.895305 ROMANIA | 1992 | 467881239.041785 RUSSIA | 1998 | 273396708.670348 RUSSIA | 1997 | 464554467.436449 RUSSIA | 1996 | 472637694.419156 RUSSIA | 1995 | 462912259.581803 RUSSIA | 1994 | 473954198.587372 RUSSIA | 1993 | 462565546.517587 RUSSIA | 1992 | 471835310.115986 SAUDI ARABIA | 1998 | 278737717.264157 SAUDI ARABIA | 1997 | 462939778.410013 SAUDI ARABIA | 1996 | 473553141.497286 SAUDI ARABIA | 1995 | 469744720.388318 SAUDI ARABIA | 1994 | 465879872.331983 SAUDI ARABIA | 1993 | 469987930.858452 SAUDI ARABIA | 1992 | 471053011.959311 UNITED KINGDOM | 1998 | 268425858.404288 UNITED KINGDOM | 1997 | 463614043.693689 UNITED KINGDOM | 1996 | 464684214.790636 UNITED KINGDOM | 1995 | 458048202.404634 UNITED KINGDOM | 1994 | 464707985.419543 UNITED KINGDOM | 1993 | 464613197.242718 UNITED KINGDOM | 1992 | 461845835.281894 UNITED STATES | 1998 | 275943488.523439 UNITED STATES | 1997 | 480332400.155022 UNITED STATES | 1996 | 489128368.218129 UNITED STATES | 1995 | 474520958.568417 UNITED STATES | 1994 | 474611132.739987 UNITED STATES | 1993 | 486456733.141143 UNITED STATES | 1992 | 478464341.450852 VIETNAM | 1998 | 281990600.470972 VIETNAM | 1997 | 461049082.651053 VIETNAM | 1996 | 474861722.244807 VIETNAM | 1995 | 465700028.55035 VIETNAM | 1994 | 464242469.671282 VIETNAM | 1993 | 465673316.742682 VIETNAM | 1992 | 469593963.485365 (175 rows) COMMIT; COMMIT