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 '%ghost%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272584024.423872 ALGERIA | 1997 | 460467683.776971 ALGERIA | 1996 | 467072103.673926 ALGERIA | 1995 | 466052522.796933 ALGERIA | 1994 | 457577011.587752 ALGERIA | 1993 | 471020467.557303 ALGERIA | 1992 | 465300833.094896 ARGENTINA | 1998 | 276528981.754889 ARGENTINA | 1997 | 471571479.684948 ARGENTINA | 1996 | 475897976.01704 ARGENTINA | 1995 | 471502637.835533 ARGENTINA | 1994 | 466597006.463039 ARGENTINA | 1993 | 466957712.753002 ARGENTINA | 1992 | 469250433.695713 BRAZIL | 1998 | 272109813.320213 BRAZIL | 1997 | 464405860.013477 BRAZIL | 1996 | 468494655.369613 BRAZIL | 1995 | 470606503.116318 BRAZIL | 1994 | 463184521.946632 BRAZIL | 1993 | 462880489.717495 BRAZIL | 1992 | 472299078.793584 CANADA | 1998 | 274026922.299953 CANADA | 1997 | 479145574.674467 CANADA | 1996 | 473089336.584653 CANADA | 1995 | 478824553.232878 CANADA | 1994 | 480661916.650623 CANADA | 1993 | 483124642.235876 CANADA | 1992 | 484301753.662587 CHINA | 1998 | 280153684.972411 CHINA | 1997 | 468620968.797948 CHINA | 1996 | 477369239.88184 CHINA | 1995 | 474944980.633176 CHINA | 1994 | 471682403.935641 CHINA | 1993 | 478467475.27118 CHINA | 1992 | 475544126.676581 EGYPT | 1998 | 276249949.20265 EGYPT | 1997 | 458654825.170545 EGYPT | 1996 | 465364109.094351 EGYPT | 1995 | 465203664.143076 EGYPT | 1994 | 463278225.521534 EGYPT | 1993 | 460163322.815725 EGYPT | 1992 | 469043367.497149 ETHIOPIA | 1998 | 267349465.129664 ETHIOPIA | 1997 | 453822163.398699 ETHIOPIA | 1996 | 458519723.467847 ETHIOPIA | 1995 | 460671499.345675 ETHIOPIA | 1994 | 456926922.308688 ETHIOPIA | 1993 | 456502916.46309 ETHIOPIA | 1992 | 458362221.127664 FRANCE | 1998 | 271454323.367772 FRANCE | 1997 | 460102087.700053 FRANCE | 1996 | 460671130.98264 FRANCE | 1995 | 466116279.028661 FRANCE | 1994 | 457252136.033957 FRANCE | 1993 | 464727455.924465 FRANCE | 1992 | 460305729.424811 GERMANY | 1998 | 277667347.740466 GERMANY | 1997 | 475904370.658284 GERMANY | 1996 | 468555239.252042 GERMANY | 1995 | 470942674.967609 GERMANY | 1994 | 473824613.760744 GERMANY | 1993 | 466783856.239219 GERMANY | 1992 | 461985651.606263 INDIA | 1998 | 270399528.73825 INDIA | 1997 | 468930823.294838 INDIA | 1996 | 476868146.929627 INDIA | 1995 | 466137963.79604 INDIA | 1994 | 463798421.80543 INDIA | 1993 | 463657410.12719 INDIA | 1992 | 466726919.622186 INDONESIA | 1998 | 273558175.422702 INDONESIA | 1997 | 456431977.639516 INDONESIA | 1996 | 459706486.92961 INDONESIA | 1995 | 464739258.691188 INDONESIA | 1994 | 455083573.628295 INDONESIA | 1993 | 460940153.872046 INDONESIA | 1992 | 469144992.79463 IRAN | 1998 | 272425730.856431 IRAN | 1997 | 461962527.191614 IRAN | 1996 | 461899991.869525 IRAN | 1995 | 461052169.758837 IRAN | 1994 | 467628470.432096 IRAN | 1993 | 462306615.890228 IRAN | 1992 | 469054957.459105 IRAQ | 1998 | 280510853.205 IRAQ | 1997 | 472761205.948352 IRAQ | 1996 | 480351846.913672 IRAQ | 1995 | 477414630.365864 IRAQ | 1994 | 481630356.227229 IRAQ | 1993 | 467247907.850667 IRAQ | 1992 | 478503491.553936 JAPAN | 1998 | 274640033.015093 JAPAN | 1997 | 468656582.12251 JAPAN | 1996 | 458232300.455298 JAPAN | 1995 | 461355331.605564 JAPAN | 1994 | 462403015.731144 JAPAN | 1993 | 462464625.174557 JAPAN | 1992 | 463744339.326403 JORDAN | 1998 | 274644894.921776 JORDAN | 1997 | 468781703.924573 JORDAN | 1996 | 461161028.896178 JORDAN | 1995 | 465148162.799357 JORDAN | 1994 | 458524299.573832 JORDAN | 1993 | 460551485.59992 JORDAN | 1992 | 459012445.073989 KENYA | 1998 | 273669944.962629 KENYA | 1997 | 471322580.760095 KENYA | 1996 | 474488632.509833 KENYA | 1995 | 473356990.41466 KENYA | 1994 | 469368499.546705 KENYA | 1993 | 479327250.992854 KENYA | 1992 | 473818541.811208 MOROCCO | 1998 | 278131363.182819 MOROCCO | 1997 | 475740751.303478 MOROCCO | 1996 | 476818778.826611 MOROCCO | 1995 | 474933095.598793 MOROCCO | 1994 | 474333222.995618 MOROCCO | 1993 | 473573504.419338 MOROCCO | 1992 | 469358321.554204 MOZAMBIQUE | 1998 | 271390602.896273 MOZAMBIQUE | 1997 | 453444621.818831 MOZAMBIQUE | 1996 | 459128567.090312 MOZAMBIQUE | 1995 | 462314057.0947 MOZAMBIQUE | 1994 | 459498857.72861 MOZAMBIQUE | 1993 | 459013720.184152 MOZAMBIQUE | 1992 | 451801154.03808 PERU | 1998 | 275143230.245057 PERU | 1997 | 465148998.061823 PERU | 1996 | 467049437.679819 PERU | 1995 | 470781735.737303 PERU | 1994 | 461226628.637269 PERU | 1993 | 463004469.839069 PERU | 1992 | 467052035.792874 ROMANIA | 1998 | 277114388.771573 ROMANIA | 1997 | 469641310.245586 ROMANIA | 1996 | 471718217.772427 ROMANIA | 1995 | 476267425.621904 ROMANIA | 1994 | 469652170.095569 ROMANIA | 1993 | 472351301.5348 ROMANIA | 1992 | 475498280.443645 RUSSIA | 1998 | 275002943.889508 RUSSIA | 1997 | 470943775.229786 RUSSIA | 1996 | 473710700.690573 RUSSIA | 1995 | 462412945.188684 RUSSIA | 1994 | 475903154.186487 RUSSIA | 1993 | 471296782.359296 RUSSIA | 1992 | 470980305.884928 SAUDI ARABIA | 1998 | 271930421.734008 SAUDI ARABIA | 1997 | 483676905.73015 SAUDI ARABIA | 1996 | 472936134.860858 SAUDI ARABIA | 1995 | 475404330.371928 SAUDI ARABIA | 1994 | 473719191.739826 SAUDI ARABIA | 1993 | 464911435.869856 SAUDI ARABIA | 1992 | 472727509.636181 UNITED KINGDOM | 1998 | 266679194.686554 UNITED KINGDOM | 1997 | 459178448.732907 UNITED KINGDOM | 1996 | 462886951.565864 UNITED KINGDOM | 1995 | 449702044.268971 UNITED KINGDOM | 1994 | 456976697.899344 UNITED KINGDOM | 1993 | 449219399.260116 UNITED KINGDOM | 1992 | 460696528.936984 UNITED STATES | 1998 | 271352033.438399 UNITED STATES | 1997 | 465419469.033221 UNITED STATES | 1996 | 464721496.25958 UNITED STATES | 1995 | 461645894.779051 UNITED STATES | 1994 | 473403246.500543 UNITED STATES | 1993 | 471575939.736001 UNITED STATES | 1992 | 461923632.544378 VIETNAM | 1998 | 275308037.517344 VIETNAM | 1997 | 468960547.27212 VIETNAM | 1996 | 484209086.798774 VIETNAM | 1995 | 471347201.877861 VIETNAM | 1994 | 477624453.485355 VIETNAM | 1993 | 468834947.58644 VIETNAM | 1992 | 470361702.081518 (175 rows) COMMIT; COMMIT