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 '%frosted%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267608019.317997 ALGERIA | 1997 | 453966811.546067 ALGERIA | 1996 | 449153811.879869 ALGERIA | 1995 | 450288750.47032 ALGERIA | 1994 | 453048270.486972 ALGERIA | 1993 | 446648611.951457 ALGERIA | 1992 | 449869618.903748 ARGENTINA | 1998 | 280066160.992527 ARGENTINA | 1997 | 471235076.433891 ARGENTINA | 1996 | 472900822.719033 ARGENTINA | 1995 | 473137600.960515 ARGENTINA | 1994 | 473190436.872791 ARGENTINA | 1993 | 466244430.578727 ARGENTINA | 1992 | 464586110.650645 BRAZIL | 1998 | 271471478.083772 BRAZIL | 1997 | 461963916.013803 BRAZIL | 1996 | 465212749.439869 BRAZIL | 1995 | 458547811.706101 BRAZIL | 1994 | 460384258.009997 BRAZIL | 1993 | 461820354.500576 BRAZIL | 1992 | 466994083.589475 CANADA | 1998 | 285325797.070663 CANADA | 1997 | 485401210.823047 CANADA | 1996 | 486230001.444099 CANADA | 1995 | 489859753.372023 CANADA | 1994 | 493605475.941282 CANADA | 1993 | 484898372.292612 CANADA | 1992 | 489152075.799301 CHINA | 1998 | 266141039.784888 CHINA | 1997 | 465745812.199372 CHINA | 1996 | 472112506.170699 CHINA | 1995 | 464625100.167968 CHINA | 1994 | 464852121.405394 CHINA | 1993 | 466370405.792297 CHINA | 1992 | 467222928.051342 EGYPT | 1998 | 278203420.703891 EGYPT | 1997 | 474180032.032644 EGYPT | 1996 | 489085596.730324 EGYPT | 1995 | 483934347.542859 EGYPT | 1994 | 480674828.102414 EGYPT | 1993 | 480986535.930434 EGYPT | 1992 | 474062964.271509 ETHIOPIA | 1998 | 265876500.609432 ETHIOPIA | 1997 | 456978732.163764 ETHIOPIA | 1996 | 462649003.229528 ETHIOPIA | 1995 | 467363021.572716 ETHIOPIA | 1994 | 458248292.939248 ETHIOPIA | 1993 | 466609906.265217 ETHIOPIA | 1992 | 467970583.681336 FRANCE | 1998 | 268666971.023924 FRANCE | 1997 | 448211783.124414 FRANCE | 1996 | 458769819.277389 FRANCE | 1995 | 456813343.778497 FRANCE | 1994 | 455481347.112606 FRANCE | 1993 | 460199264.019275 FRANCE | 1992 | 459237702.619271 GERMANY | 1998 | 278453004.564476 GERMANY | 1997 | 478663145.63926 GERMANY | 1996 | 473813484.56959 GERMANY | 1995 | 476760809.3626 GERMANY | 1994 | 471832490.274372 GERMANY | 1993 | 469456303.337039 GERMANY | 1992 | 472221214.651597 INDIA | 1998 | 280557475.687694 INDIA | 1997 | 476571308.413056 INDIA | 1996 | 484575876.597505 INDIA | 1995 | 471241502.331173 INDIA | 1994 | 472090882.391056 INDIA | 1993 | 476289630.086807 INDIA | 1992 | 477736885.251074 INDONESIA | 1998 | 268516202.978208 INDONESIA | 1997 | 460883695.771845 INDONESIA | 1996 | 470221593.932074 INDONESIA | 1995 | 460813398.487455 INDONESIA | 1994 | 465367823.959141 INDONESIA | 1993 | 466830604.179178 INDONESIA | 1992 | 458001255.739312 IRAN | 1998 | 271108011.333769 IRAN | 1997 | 461740823.350041 IRAN | 1996 | 469545791.880374 IRAN | 1995 | 471007526.622602 IRAN | 1994 | 461821108.665122 IRAN | 1993 | 456263099.745074 IRAN | 1992 | 464978028.655844 IRAQ | 1998 | 281082158.544865 IRAQ | 1997 | 474825846.483448 IRAQ | 1996 | 476860097.159743 IRAQ | 1995 | 482937711.648812 IRAQ | 1994 | 479691736.515974 IRAQ | 1993 | 478690771.172296 IRAQ | 1992 | 482175454.233722 JAPAN | 1998 | 277549099.734552 JAPAN | 1997 | 468163663.186574 JAPAN | 1996 | 465485291.573746 JAPAN | 1995 | 466800628.559697 JAPAN | 1994 | 465944733.330805 JAPAN | 1993 | 462837696.256947 JAPAN | 1992 | 468102135.483103 JORDAN | 1998 | 265701861.296586 JORDAN | 1997 | 459418438.009322 JORDAN | 1996 | 457567097.028067 JORDAN | 1995 | 458143816.97163 JORDAN | 1994 | 459027113.689557 JORDAN | 1993 | 457545787.645536 JORDAN | 1992 | 454263597.742916 KENYA | 1998 | 277295561.341455 KENYA | 1997 | 465226183.716695 KENYA | 1996 | 467432592.696556 KENYA | 1995 | 463577782.618261 KENYA | 1994 | 463192354.509374 KENYA | 1993 | 464323011.915104 KENYA | 1992 | 465378918.52983 MOROCCO | 1998 | 274586809.019509 MOROCCO | 1997 | 464257968.720869 MOROCCO | 1996 | 463655296.640036 MOROCCO | 1995 | 464346316.148349 MOROCCO | 1994 | 463593430.031183 MOROCCO | 1993 | 468374822.130082 MOROCCO | 1992 | 467397116.451547 MOZAMBIQUE | 1998 | 274132388.26484 MOZAMBIQUE | 1997 | 454920069.693467 MOZAMBIQUE | 1996 | 459735772.132701 MOZAMBIQUE | 1995 | 458988088.775783 MOZAMBIQUE | 1994 | 454079784.624283 MOZAMBIQUE | 1993 | 451047683.744032 MOZAMBIQUE | 1992 | 457865487.559543 PERU | 1998 | 273406526.028143 PERU | 1997 | 456504756.914847 PERU | 1996 | 463945357.749962 PERU | 1995 | 462054977.142653 PERU | 1994 | 457220955.163342 PERU | 1993 | 459304772.926124 PERU | 1992 | 462941762.792733 ROMANIA | 1998 | 279957110.88076 ROMANIA | 1997 | 469430126.492428 ROMANIA | 1996 | 475859361.646923 ROMANIA | 1995 | 472438007.216572 ROMANIA | 1994 | 474206535.378247 ROMANIA | 1993 | 477809611.781972 ROMANIA | 1992 | 472596508.791901 RUSSIA | 1998 | 275933827.903924 RUSSIA | 1997 | 475406074.910836 RUSSIA | 1996 | 476121451.836905 RUSSIA | 1995 | 473802083.361787 RUSSIA | 1994 | 468944408.490217 RUSSIA | 1993 | 470361570.965149 RUSSIA | 1992 | 478535652.689355 SAUDI ARABIA | 1998 | 279423319.081715 SAUDI ARABIA | 1997 | 468954760.831863 SAUDI ARABIA | 1996 | 471994831.102787 SAUDI ARABIA | 1995 | 475670001.31562 SAUDI ARABIA | 1994 | 473430476.467051 SAUDI ARABIA | 1993 | 475504540.735056 SAUDI ARABIA | 1992 | 472167148.505215 UNITED KINGDOM | 1998 | 271736455.886755 UNITED KINGDOM | 1997 | 456399667.622868 UNITED KINGDOM | 1996 | 464078480.050217 UNITED KINGDOM | 1995 | 461491236.576183 UNITED KINGDOM | 1994 | 464979278.127246 UNITED KINGDOM | 1993 | 459776242.374379 UNITED KINGDOM | 1992 | 463086154.425039 UNITED STATES | 1998 | 275183894.664065 UNITED STATES | 1997 | 468032367.981393 UNITED STATES | 1996 | 473058387.310452 UNITED STATES | 1995 | 472251918.650828 UNITED STATES | 1994 | 474832815.794453 UNITED STATES | 1993 | 467071505.144306 UNITED STATES | 1992 | 476402532.920894 VIETNAM | 1998 | 272568467.201674 VIETNAM | 1997 | 463270628.271557 VIETNAM | 1996 | 469061175.542836 VIETNAM | 1995 | 465144461.002513 VIETNAM | 1994 | 465122665.391221 VIETNAM | 1993 | 472772615.439701 VIETNAM | 1992 | 465807862.477455 (175 rows) COMMIT; COMMIT