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 '%lemon%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265366590.821614 ALGERIA | 1997 | 445938033.766602 ALGERIA | 1996 | 455466084.604459 ALGERIA | 1995 | 451331260.915183 ALGERIA | 1994 | 458041111.177314 ALGERIA | 1993 | 453033048.109071 ALGERIA | 1992 | 446269181.954993 ARGENTINA | 1998 | 276868660.636408 ARGENTINA | 1997 | 463135862.736999 ARGENTINA | 1996 | 463573616.375251 ARGENTINA | 1995 | 466497184.889419 ARGENTINA | 1994 | 469433151.281858 ARGENTINA | 1993 | 465439659.436224 ARGENTINA | 1992 | 458280119.621907 BRAZIL | 1998 | 280496273.721333 BRAZIL | 1997 | 478603168.943399 BRAZIL | 1996 | 479816508.17114 BRAZIL | 1995 | 465138035.028377 BRAZIL | 1994 | 464873048.872622 BRAZIL | 1993 | 473662453.221571 BRAZIL | 1992 | 474912838.252295 CANADA | 1998 | 276224166.463526 CANADA | 1997 | 474975756.425114 CANADA | 1996 | 480083297.263918 CANADA | 1995 | 476501558.824191 CANADA | 1994 | 480140759.827321 CANADA | 1993 | 473719016.307286 CANADA | 1992 | 475884749.913681 CHINA | 1998 | 266080366.925914 CHINA | 1997 | 466678377.239353 CHINA | 1996 | 468449945.091993 CHINA | 1995 | 461930426.970101 CHINA | 1994 | 457971262.742917 CHINA | 1993 | 464004828.775164 CHINA | 1992 | 462043093.360367 EGYPT | 1998 | 268045551.097888 EGYPT | 1997 | 458085749.758316 EGYPT | 1996 | 462302867.540313 EGYPT | 1995 | 454244328.052436 EGYPT | 1994 | 466730023.488235 EGYPT | 1993 | 471727482.405127 EGYPT | 1992 | 464434906.789443 ETHIOPIA | 1998 | 274925810.573087 ETHIOPIA | 1997 | 460395181.741374 ETHIOPIA | 1996 | 469694190.48361 ETHIOPIA | 1995 | 468434672.535928 ETHIOPIA | 1994 | 468639347.756915 ETHIOPIA | 1993 | 466329118.099978 ETHIOPIA | 1992 | 463334501.000783 FRANCE | 1998 | 268636167.53047 FRANCE | 1997 | 452295191.4519 FRANCE | 1996 | 466038135.920088 FRANCE | 1995 | 455581624.814927 FRANCE | 1994 | 460253814.015211 FRANCE | 1993 | 457919280.185933 FRANCE | 1992 | 450057720.24777 GERMANY | 1998 | 278284177.29901 GERMANY | 1997 | 478458811.275536 GERMANY | 1996 | 478000253.40244 GERMANY | 1995 | 474742085.367443 GERMANY | 1994 | 471865917.660587 GERMANY | 1993 | 465809266.11348 GERMANY | 1992 | 481756423.312363 INDIA | 1998 | 279144465.918226 INDIA | 1997 | 477430319.031785 INDIA | 1996 | 477520172.812954 INDIA | 1995 | 472755920.682111 INDIA | 1994 | 476215147.668099 INDIA | 1993 | 472012125.101504 INDIA | 1992 | 482181619.231159 INDONESIA | 1998 | 269591611.360033 INDONESIA | 1997 | 453008027.83708 INDONESIA | 1996 | 452166246.552182 INDONESIA | 1995 | 451817274.989253 INDONESIA | 1994 | 452832201.3257 INDONESIA | 1993 | 458911804.666566 INDONESIA | 1992 | 459743077.319085 IRAN | 1998 | 269531806.864344 IRAN | 1997 | 463023575.826157 IRAN | 1996 | 472235204.985385 IRAN | 1995 | 463449266.458774 IRAN | 1994 | 464893099.627088 IRAN | 1993 | 463481997.577737 IRAN | 1992 | 470918004.366209 IRAQ | 1998 | 279808060.399135 IRAQ | 1997 | 476091150.318083 IRAQ | 1996 | 472020887.121636 IRAQ | 1995 | 476461663.037989 IRAQ | 1994 | 476009666.337166 IRAQ | 1993 | 477239924.389679 IRAQ | 1992 | 472898898.61543 JAPAN | 1998 | 266530419.632142 JAPAN | 1997 | 463934466.832476 JAPAN | 1996 | 466025945.002307 JAPAN | 1995 | 465736536.842152 JAPAN | 1994 | 466988064.802834 JAPAN | 1993 | 466709254.282877 JAPAN | 1992 | 467751910.545435 JORDAN | 1998 | 273472079.460139 JORDAN | 1997 | 463180338.758382 JORDAN | 1996 | 469701636.948353 JORDAN | 1995 | 473158308.564581 JORDAN | 1994 | 465024698.134869 JORDAN | 1993 | 462492336.130341 JORDAN | 1992 | 455881104.562095 KENYA | 1998 | 274492675.513527 KENYA | 1997 | 465430387.242923 KENYA | 1996 | 468215220.4306 KENYA | 1995 | 466568970.041038 KENYA | 1994 | 465354801.850098 KENYA | 1993 | 469960899.361652 KENYA | 1992 | 471307076.371188 MOROCCO | 1998 | 270761049.827686 MOROCCO | 1997 | 464729123.573017 MOROCCO | 1996 | 465394583.74045 MOROCCO | 1995 | 464828965.401797 MOROCCO | 1994 | 473113008.335257 MOROCCO | 1993 | 467661613.575624 MOROCCO | 1992 | 466754493.596188 MOZAMBIQUE | 1998 | 268277628.941444 MOZAMBIQUE | 1997 | 464655127.769954 MOZAMBIQUE | 1996 | 466569499.792104 MOZAMBIQUE | 1995 | 464402653.152514 MOZAMBIQUE | 1994 | 456628041.97449 MOZAMBIQUE | 1993 | 455493467.364672 MOZAMBIQUE | 1992 | 459309283.891807 PERU | 1998 | 274136093.900661 PERU | 1997 | 471545186.934099 PERU | 1996 | 471367712.36308 PERU | 1995 | 472650293.966412 PERU | 1994 | 464283633.351397 PERU | 1993 | 471129250.262689 PERU | 1992 | 475381671.505787 ROMANIA | 1998 | 270416028.574012 ROMANIA | 1997 | 472367525.147661 ROMANIA | 1996 | 468641960.500606 ROMANIA | 1995 | 466151461.244579 ROMANIA | 1994 | 462241763.488308 ROMANIA | 1993 | 472917719.035752 ROMANIA | 1992 | 474010685.630017 RUSSIA | 1998 | 279073605.253739 RUSSIA | 1997 | 465491536.011205 RUSSIA | 1996 | 475489045.282847 RUSSIA | 1995 | 468636784.067697 RUSSIA | 1994 | 466707159.017277 RUSSIA | 1993 | 472497841.724326 RUSSIA | 1992 | 465406730.071578 SAUDI ARABIA | 1998 | 273844388.594616 SAUDI ARABIA | 1997 | 467733859.569178 SAUDI ARABIA | 1996 | 467667841.390819 SAUDI ARABIA | 1995 | 473424609.749364 SAUDI ARABIA | 1994 | 461999111.706361 SAUDI ARABIA | 1993 | 470660631.660937 SAUDI ARABIA | 1992 | 474786172.610888 UNITED KINGDOM | 1998 | 270668776.473123 UNITED KINGDOM | 1997 | 458818352.791484 UNITED KINGDOM | 1996 | 462181472.842619 UNITED KINGDOM | 1995 | 462296726.259901 UNITED KINGDOM | 1994 | 458541829.255259 UNITED KINGDOM | 1993 | 462127449.276257 UNITED KINGDOM | 1992 | 459677243.30093 UNITED STATES | 1998 | 273540093.345131 UNITED STATES | 1997 | 471961342.203694 UNITED STATES | 1996 | 480911426.683524 UNITED STATES | 1995 | 471462109.054006 UNITED STATES | 1994 | 473946001.921248 UNITED STATES | 1993 | 486848037.063884 UNITED STATES | 1992 | 477724445.209866 VIETNAM | 1998 | 269669800.283489 VIETNAM | 1997 | 462250386.249002 VIETNAM | 1996 | 472102780.89286 VIETNAM | 1995 | 466901521.70121 VIETNAM | 1994 | 471349406.526172 VIETNAM | 1993 | 470153436.045258 VIETNAM | 1992 | 466420218.361835 (175 rows) COMMIT; COMMIT