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 | 272660492.273191 ALGERIA | 1997 | 460358387.540137 ALGERIA | 1996 | 467516153.084665 ALGERIA | 1995 | 466482021.743344 ALGERIA | 1994 | 457797459.904047 ALGERIA | 1993 | 470622097.054677 ALGERIA | 1992 | 464866423.929297 ARGENTINA | 1998 | 276363712.274451 ARGENTINA | 1997 | 471709325.162573 ARGENTINA | 1996 | 475404671.923134 ARGENTINA | 1995 | 471633770.918615 ARGENTINA | 1994 | 466773850.624552 ARGENTINA | 1993 | 466473273.112904 ARGENTINA | 1992 | 469422610.58435 BRAZIL | 1998 | 271853511.468856 BRAZIL | 1997 | 464150355.152152 BRAZIL | 1996 | 468694324.309265 BRAZIL | 1995 | 470614743.015255 BRAZIL | 1994 | 463707210.933294 BRAZIL | 1993 | 462941007.55557 BRAZIL | 1992 | 471954140.44996 CANADA | 1998 | 274083873.710498 CANADA | 1997 | 478574506.777369 CANADA | 1996 | 472798097.005488 CANADA | 1995 | 478334074.949226 CANADA | 1994 | 480262971.767847 CANADA | 1993 | 483527228.293696 CANADA | 1992 | 484791588.856027 CHINA | 1998 | 280088646.906296 CHINA | 1997 | 468346149.597642 CHINA | 1996 | 477475146.258095 CHINA | 1995 | 474580695.06655 CHINA | 1994 | 471385223.211683 CHINA | 1993 | 478158043.87083 CHINA | 1992 | 476440077.951317 EGYPT | 1998 | 276027595.548105 EGYPT | 1997 | 458674816.210878 EGYPT | 1996 | 465001778.590035 EGYPT | 1995 | 465200649.014387 EGYPT | 1994 | 463121114.985024 EGYPT | 1993 | 460573176.853041 EGYPT | 1992 | 468829588.267951 ETHIOPIA | 1998 | 267175124.089948 ETHIOPIA | 1997 | 453649297.966297 ETHIOPIA | 1996 | 459311680.622128 ETHIOPIA | 1995 | 460974440.041283 ETHIOPIA | 1994 | 456649282.864893 ETHIOPIA | 1993 | 456653685.264257 ETHIOPIA | 1992 | 458110443.410161 FRANCE | 1998 | 271715644.172812 FRANCE | 1997 | 459716214.936635 FRANCE | 1996 | 460808021.323925 FRANCE | 1995 | 465521744.136524 FRANCE | 1994 | 456685522.436213 FRANCE | 1993 | 465441876.146328 FRANCE | 1992 | 460051584.302767 GERMANY | 1998 | 277712612.180624 GERMANY | 1997 | 475981039.678484 GERMANY | 1996 | 469124733.947642 GERMANY | 1995 | 471015729.370583 GERMANY | 1994 | 473941219.522722 GERMANY | 1993 | 466710103.752051 GERMANY | 1992 | 461937058.112771 INDIA | 1998 | 270356050.723617 INDIA | 1997 | 469020260.104381 INDIA | 1996 | 476691539.296669 INDIA | 1995 | 466689717.778783 INDIA | 1994 | 463657304.655366 INDIA | 1993 | 463955492.013884 INDIA | 1992 | 467008870.362721 INDONESIA | 1998 | 273323537.066857 INDONESIA | 1997 | 457070728.141033 INDONESIA | 1996 | 459803018.840052 INDONESIA | 1995 | 465074380.320333 INDONESIA | 1994 | 455060923.705828 INDONESIA | 1993 | 461169954.003787 INDONESIA | 1992 | 468762697.929683 IRAN | 1998 | 272701693.81419 IRAN | 1997 | 462113846.341034 IRAN | 1996 | 461803115.123277 IRAN | 1995 | 461067386.015564 IRAN | 1994 | 467722924.995081 IRAN | 1993 | 462428263.656265 IRAN | 1992 | 469052570.050696 IRAQ | 1998 | 280397259.790083 IRAQ | 1997 | 472368701.858878 IRAQ | 1996 | 480352330.946636 IRAQ | 1995 | 477091215.135259 IRAQ | 1994 | 481771509.521271 IRAQ | 1993 | 467342941.17335 IRAQ | 1992 | 478615727.057412 JAPAN | 1998 | 274972664.864826 JAPAN | 1997 | 468555254.203739 JAPAN | 1996 | 458213615.382752 JAPAN | 1995 | 461607489.271151 JAPAN | 1994 | 462908033.553484 JAPAN | 1993 | 462626234.8525 JAPAN | 1992 | 463473372.178952 JORDAN | 1998 | 274448421.176081 JORDAN | 1997 | 468897004.249202 JORDAN | 1996 | 460472389.151361 JORDAN | 1995 | 465251143.834435 JORDAN | 1994 | 458426066.928548 JORDAN | 1993 | 460770843.157446 JORDAN | 1992 | 459154476.64143 KENYA | 1998 | 272912954.380661 KENYA | 1997 | 471682620.747226 KENYA | 1996 | 474438447.95142 KENYA | 1995 | 473921055.442361 KENYA | 1994 | 469454330.812043 KENYA | 1993 | 479461653.187932 KENYA | 1992 | 473737862.224338 MOROCCO | 1998 | 278347898.443514 MOROCCO | 1997 | 475968414.920222 MOROCCO | 1996 | 477600168.728018 MOROCCO | 1995 | 474857265.143922 MOROCCO | 1994 | 474168302.92221 MOROCCO | 1993 | 473652716.483258 MOROCCO | 1992 | 469207421.883654 MOZAMBIQUE | 1998 | 270945880.861923 MOZAMBIQUE | 1997 | 453803749.628122 MOZAMBIQUE | 1996 | 459466822.901966 MOZAMBIQUE | 1995 | 462224298.212757 MOZAMBIQUE | 1994 | 459307345.130553 MOZAMBIQUE | 1993 | 458852529.803171 MOZAMBIQUE | 1992 | 451477460.620496 PERU | 1998 | 275376383.356627 PERU | 1997 | 464792378.496335 PERU | 1996 | 467534204.840086 PERU | 1995 | 470698349.002642 PERU | 1994 | 461402336.712539 PERU | 1993 | 463213532.053943 PERU | 1992 | 466897293.686849 ROMANIA | 1998 | 277154357.869853 ROMANIA | 1997 | 469711128.06216 ROMANIA | 1996 | 471525952.557499 ROMANIA | 1995 | 476208613.528603 ROMANIA | 1994 | 469680677.573179 ROMANIA | 1993 | 472866748.014121 ROMANIA | 1992 | 475360479.336863 RUSSIA | 1998 | 275019208.761915 RUSSIA | 1997 | 470770982.549957 RUSSIA | 1996 | 473894722.067395 RUSSIA | 1995 | 462599038.605661 RUSSIA | 1994 | 476124513.393731 RUSSIA | 1993 | 471050903.129934 RUSSIA | 1992 | 471677749.236379 SAUDI ARABIA | 1998 | 271796436.773163 SAUDI ARABIA | 1997 | 483670916.825634 SAUDI ARABIA | 1996 | 473140547.809282 SAUDI ARABIA | 1995 | 475854097.210292 SAUDI ARABIA | 1994 | 474306368.840925 SAUDI ARABIA | 1993 | 464591845.99821 SAUDI ARABIA | 1992 | 472060466.184262 UNITED KINGDOM | 1998 | 267121618.521505 UNITED KINGDOM | 1997 | 458992174.329845 UNITED KINGDOM | 1996 | 462663799.330451 UNITED KINGDOM | 1995 | 449772000.335287 UNITED KINGDOM | 1994 | 457207140.102096 UNITED KINGDOM | 1993 | 448670915.649616 UNITED KINGDOM | 1992 | 461104491.650792 UNITED STATES | 1998 | 271036681.115862 UNITED STATES | 1997 | 465033269.180957 UNITED STATES | 1996 | 464800593.694084 UNITED STATES | 1995 | 461266862.945969 UNITED STATES | 1994 | 473753539.514857 UNITED STATES | 1993 | 471339469.346251 UNITED STATES | 1992 | 462208676.478763 VIETNAM | 1998 | 275047259.605835 VIETNAM | 1997 | 468883692.114013 VIETNAM | 1996 | 484266168.002403 VIETNAM | 1995 | 472023146.462964 VIETNAM | 1994 | 478231200.910006 VIETNAM | 1993 | 468301338.500318 VIETNAM | 1992 | 470262337.701151 (175 rows) COMMIT; COMMIT