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.27319 ALGERIA | 1997 | 460358387.540135 ALGERIA | 1996 | 467516153.084666 ALGERIA | 1995 | 466482021.743347 ALGERIA | 1994 | 457797459.904048 ALGERIA | 1993 | 470622097.054678 ALGERIA | 1992 | 464866423.929295 ARGENTINA | 1998 | 276363712.274449 ARGENTINA | 1997 | 471709325.162572 ARGENTINA | 1996 | 475404671.923133 ARGENTINA | 1995 | 471633770.918613 ARGENTINA | 1994 | 466773850.624548 ARGENTINA | 1993 | 466473273.112902 ARGENTINA | 1992 | 469422610.584352 BRAZIL | 1998 | 271853511.468855 BRAZIL | 1997 | 464150355.152151 BRAZIL | 1996 | 468694324.309266 BRAZIL | 1995 | 470614743.015254 BRAZIL | 1994 | 463707210.933293 BRAZIL | 1993 | 462941007.55557 BRAZIL | 1992 | 471954140.44996 CANADA | 1998 | 274083873.710497 CANADA | 1997 | 478574506.777367 CANADA | 1996 | 472798097.005488 CANADA | 1995 | 478334074.949226 CANADA | 1994 | 480262971.767846 CANADA | 1993 | 483527228.293699 CANADA | 1992 | 484791588.856026 CHINA | 1998 | 280088646.906295 CHINA | 1997 | 468346149.597643 CHINA | 1996 | 477475146.258099 CHINA | 1995 | 474580695.066549 CHINA | 1994 | 471385223.211683 CHINA | 1993 | 478158043.870829 CHINA | 1992 | 476440077.951317 EGYPT | 1998 | 276027595.548104 EGYPT | 1997 | 458674816.210879 EGYPT | 1996 | 465001778.590031 EGYPT | 1995 | 465200649.014388 EGYPT | 1994 | 463121114.985024 EGYPT | 1993 | 460573176.853042 EGYPT | 1992 | 468829588.267954 ETHIOPIA | 1998 | 267175124.089948 ETHIOPIA | 1997 | 453649297.966298 ETHIOPIA | 1996 | 459311680.622127 ETHIOPIA | 1995 | 460974440.041286 ETHIOPIA | 1994 | 456649282.864893 ETHIOPIA | 1993 | 456653685.264259 ETHIOPIA | 1992 | 458110443.410161 FRANCE | 1998 | 271715644.172812 FRANCE | 1997 | 459716214.936638 FRANCE | 1996 | 460808021.323926 FRANCE | 1995 | 465521744.136524 FRANCE | 1994 | 456685522.436211 FRANCE | 1993 | 465441876.146328 FRANCE | 1992 | 460051584.302766 GERMANY | 1998 | 277712612.180622 GERMANY | 1997 | 475981039.678485 GERMANY | 1996 | 469124733.947643 GERMANY | 1995 | 471015729.370584 GERMANY | 1994 | 473941219.522719 GERMANY | 1993 | 466710103.752053 GERMANY | 1992 | 461937058.112768 INDIA | 1998 | 270356050.723617 INDIA | 1997 | 469020260.104381 INDIA | 1996 | 476691539.296672 INDIA | 1995 | 466689717.778784 INDIA | 1994 | 463657304.655365 INDIA | 1993 | 463955492.013881 INDIA | 1992 | 467008870.362719 INDONESIA | 1998 | 273323537.066857 INDONESIA | 1997 | 457070728.141035 INDONESIA | 1996 | 459803018.840053 INDONESIA | 1995 | 465074380.320334 INDONESIA | 1994 | 455060923.705827 INDONESIA | 1993 | 461169954.003786 INDONESIA | 1992 | 468762697.929683 IRAN | 1998 | 272701693.814191 IRAN | 1997 | 462113846.341032 IRAN | 1996 | 461803115.12328 IRAN | 1995 | 461067386.015564 IRAN | 1994 | 467722924.995084 IRAN | 1993 | 462428263.656266 IRAN | 1992 | 469052570.050694 IRAQ | 1998 | 280397259.790084 IRAQ | 1997 | 472368701.858876 IRAQ | 1996 | 480352330.946634 IRAQ | 1995 | 477091215.135258 IRAQ | 1994 | 481771509.521273 IRAQ | 1993 | 467342941.17335 IRAQ | 1992 | 478615727.057415 JAPAN | 1998 | 274972664.864827 JAPAN | 1997 | 468555254.203741 JAPAN | 1996 | 458213615.382752 JAPAN | 1995 | 461607489.271156 JAPAN | 1994 | 462908033.553486 JAPAN | 1993 | 462626234.852502 JAPAN | 1992 | 463473372.178953 JORDAN | 1998 | 274448421.176081 JORDAN | 1997 | 468897004.249202 JORDAN | 1996 | 460472389.151361 JORDAN | 1995 | 465251143.834431 JORDAN | 1994 | 458426066.928548 JORDAN | 1993 | 460770843.157444 JORDAN | 1992 | 459154476.641431 KENYA | 1998 | 272912954.380663 KENYA | 1997 | 471682620.747228 KENYA | 1996 | 474438447.951422 KENYA | 1995 | 473921055.44236 KENYA | 1994 | 469454330.812041 KENYA | 1993 | 479461653.187933 KENYA | 1992 | 473737862.224338 MOROCCO | 1998 | 278347898.443515 MOROCCO | 1997 | 475968414.920221 MOROCCO | 1996 | 477600168.728018 MOROCCO | 1995 | 474857265.143922 MOROCCO | 1994 | 474168302.922214 MOROCCO | 1993 | 473652716.483256 MOROCCO | 1992 | 469207421.883652 MOZAMBIQUE | 1998 | 270945880.861923 MOZAMBIQUE | 1997 | 453803749.628122 MOZAMBIQUE | 1996 | 459466822.901966 MOZAMBIQUE | 1995 | 462224298.212756 MOZAMBIQUE | 1994 | 459307345.130553 MOZAMBIQUE | 1993 | 458852529.803171 MOZAMBIQUE | 1992 | 451477460.620496 PERU | 1998 | 275376383.356626 PERU | 1997 | 464792378.496336 PERU | 1996 | 467534204.840086 PERU | 1995 | 470698349.00264 PERU | 1994 | 461402336.712539 PERU | 1993 | 463213532.053942 PERU | 1992 | 466897293.686853 ROMANIA | 1998 | 277154357.869854 ROMANIA | 1997 | 469711128.06216 ROMANIA | 1996 | 471525952.557497 ROMANIA | 1995 | 476208613.528603 ROMANIA | 1994 | 469680677.573179 ROMANIA | 1993 | 472866748.014121 ROMANIA | 1992 | 475360479.336865 RUSSIA | 1998 | 275019208.761915 RUSSIA | 1997 | 470770982.549957 RUSSIA | 1996 | 473894722.067396 RUSSIA | 1995 | 462599038.605664 RUSSIA | 1994 | 476124513.393729 RUSSIA | 1993 | 471050903.129933 RUSSIA | 1992 | 471677749.236381 SAUDI ARABIA | 1998 | 271796436.773165 SAUDI ARABIA | 1997 | 483670916.825634 SAUDI ARABIA | 1996 | 473140547.80928 SAUDI ARABIA | 1995 | 475854097.210292 SAUDI ARABIA | 1994 | 474306368.840921 SAUDI ARABIA | 1993 | 464591845.99821 SAUDI ARABIA | 1992 | 472060466.184263 UNITED KINGDOM | 1998 | 267121618.521506 UNITED KINGDOM | 1997 | 458992174.329848 UNITED KINGDOM | 1996 | 462663799.330453 UNITED KINGDOM | 1995 | 449772000.335287 UNITED KINGDOM | 1994 | 457207140.102096 UNITED KINGDOM | 1993 | 448670915.649618 UNITED KINGDOM | 1992 | 461104491.650793 UNITED STATES | 1998 | 271036681.115861 UNITED STATES | 1997 | 465033269.180952 UNITED STATES | 1996 | 464800593.694085 UNITED STATES | 1995 | 461266862.945967 UNITED STATES | 1994 | 473753539.514858 UNITED STATES | 1993 | 471339469.346251 UNITED STATES | 1992 | 462208676.478763 VIETNAM | 1998 | 275047259.605835 VIETNAM | 1997 | 468883692.114012 VIETNAM | 1996 | 484266168.002401 VIETNAM | 1995 | 472023146.462966 VIETNAM | 1994 | 478231200.910005 VIETNAM | 1993 | 468301338.50032 VIETNAM | 1992 | 470262337.701151 (175 rows) COMMIT; COMMIT