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 '%khaki%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270290181.994679 ALGERIA | 1997 | 459340445.896821 ALGERIA | 1996 | 470059966.565452 ALGERIA | 1995 | 462379912.156983 ALGERIA | 1994 | 465759351.981562 ALGERIA | 1993 | 461818495.727252 ALGERIA | 1992 | 465378998.228514 ARGENTINA | 1998 | 272265296.355446 ARGENTINA | 1997 | 468613919.447435 ARGENTINA | 1996 | 475860945.497786 ARGENTINA | 1995 | 470126735.523398 ARGENTINA | 1994 | 475854014.047882 ARGENTINA | 1993 | 473121614.98393 ARGENTINA | 1992 | 477627639.872263 BRAZIL | 1998 | 276342473.683263 BRAZIL | 1997 | 465445494.718238 BRAZIL | 1996 | 469847501.437226 BRAZIL | 1995 | 461430168.876067 BRAZIL | 1994 | 468720874.219924 BRAZIL | 1993 | 461593317.737105 BRAZIL | 1992 | 464809551.265856 CANADA | 1998 | 280666562.375749 CANADA | 1997 | 474282555.530564 CANADA | 1996 | 481997406.072857 CANADA | 1995 | 475626037.103965 CANADA | 1994 | 480353194.034952 CANADA | 1993 | 477732956.401127 CANADA | 1992 | 471765506.400035 CHINA | 1998 | 270580589.642954 CHINA | 1997 | 457675023.115364 CHINA | 1996 | 466673088.931223 CHINA | 1995 | 456585766.568665 CHINA | 1994 | 462060505.907007 CHINA | 1993 | 464344807.558772 CHINA | 1992 | 454627116.217297 EGYPT | 1998 | 279467573.322906 EGYPT | 1997 | 462528823.236652 EGYPT | 1996 | 462388011.7191 EGYPT | 1995 | 456046337.027002 EGYPT | 1994 | 461408998.443831 EGYPT | 1993 | 462120303.702051 EGYPT | 1992 | 465570316.37136 ETHIOPIA | 1998 | 274146964.160115 ETHIOPIA | 1997 | 466759470.116247 ETHIOPIA | 1996 | 468306600.620808 ETHIOPIA | 1995 | 469681294.983485 ETHIOPIA | 1994 | 471266949.456358 ETHIOPIA | 1993 | 461627404.355047 ETHIOPIA | 1992 | 465901018.318279 FRANCE | 1998 | 264695671.725195 FRANCE | 1997 | 454333429.176454 FRANCE | 1996 | 464990087.594305 FRANCE | 1995 | 455774646.178305 FRANCE | 1994 | 459382440.4533 FRANCE | 1993 | 453950613.071477 FRANCE | 1992 | 460717206.907894 GERMANY | 1998 | 274999411.310147 GERMANY | 1997 | 475339615.288942 GERMANY | 1996 | 479156184.798307 GERMANY | 1995 | 481458857.880632 GERMANY | 1994 | 475526588.828721 GERMANY | 1993 | 480504259.963538 GERMANY | 1992 | 478919284.699035 INDIA | 1998 | 282240515.93319 INDIA | 1997 | 483233789.250954 INDIA | 1996 | 484347914.265914 INDIA | 1995 | 474858852.708012 INDIA | 1994 | 485170708.66714 INDIA | 1993 | 481247346.790119 INDIA | 1992 | 486903614.809413 INDONESIA | 1998 | 269824826.700697 INDONESIA | 1997 | 460842838.194238 INDONESIA | 1996 | 466598510.598301 INDONESIA | 1995 | 462724567.48827 INDONESIA | 1994 | 467527804.824078 INDONESIA | 1993 | 466977281.418357 INDONESIA | 1992 | 470700819.105744 IRAN | 1998 | 271348174.163381 IRAN | 1997 | 478642928.226026 IRAN | 1996 | 471400176.71888 IRAN | 1995 | 474558575.219389 IRAN | 1994 | 464579213.890376 IRAN | 1993 | 468933453.886387 IRAN | 1992 | 477895319.18459 IRAQ | 1998 | 280897642.180206 IRAQ | 1997 | 468152323.629446 IRAQ | 1996 | 483218111.036695 IRAQ | 1995 | 472170238.434237 IRAQ | 1994 | 463019325.018363 IRAQ | 1993 | 481521666.918631 IRAQ | 1992 | 472332943.83844 JAPAN | 1998 | 273020917.960922 JAPAN | 1997 | 457235376.297131 JAPAN | 1996 | 467099858.791524 JAPAN | 1995 | 464509892.731305 JAPAN | 1994 | 462704889.680484 JAPAN | 1993 | 458255100.534752 JAPAN | 1992 | 458305886.13924 JORDAN | 1998 | 260317455.69741 JORDAN | 1997 | 451252245.853768 JORDAN | 1996 | 452988498.676972 JORDAN | 1995 | 459353208.506486 JORDAN | 1994 | 451535317.010793 JORDAN | 1993 | 460383574.10757 JORDAN | 1992 | 450101689.131035 KENYA | 1998 | 276143660.405075 KENYA | 1997 | 475417650.251191 KENYA | 1996 | 472393270.423814 KENYA | 1995 | 473691053.933475 KENYA | 1994 | 468175968.91189 KENYA | 1993 | 473160420.320616 KENYA | 1992 | 475907929.633718 MOROCCO | 1998 | 272135169.934819 MOROCCO | 1997 | 465524754.515152 MOROCCO | 1996 | 463958454.310149 MOROCCO | 1995 | 464160038.454315 MOROCCO | 1994 | 456185269.541297 MOROCCO | 1993 | 466209270.008105 MOROCCO | 1992 | 464504819.689395 MOZAMBIQUE | 1998 | 270071509.592652 MOZAMBIQUE | 1997 | 467929984.751637 MOZAMBIQUE | 1996 | 460554057.50804 MOZAMBIQUE | 1995 | 469355144.790141 MOZAMBIQUE | 1994 | 458380068.192791 MOZAMBIQUE | 1993 | 464607091.148031 MOZAMBIQUE | 1992 | 460136732.931987 PERU | 1998 | 270546399.906937 PERU | 1997 | 464222876.783627 PERU | 1996 | 466501681.60162 PERU | 1995 | 461080703.84284 PERU | 1994 | 458021034.999985 PERU | 1993 | 464950341.865657 PERU | 1992 | 463801388.629273 ROMANIA | 1998 | 276331545.986427 ROMANIA | 1997 | 472069971.739763 ROMANIA | 1996 | 472121864.6837 ROMANIA | 1995 | 472430748.129509 ROMANIA | 1994 | 469911818.334646 ROMANIA | 1993 | 473862664.163273 ROMANIA | 1992 | 475316039.513842 RUSSIA | 1998 | 267267292.445746 RUSSIA | 1997 | 468474281.202694 RUSSIA | 1996 | 464801343.334032 RUSSIA | 1995 | 462133332.442432 RUSSIA | 1994 | 464568277.743492 RUSSIA | 1993 | 464643418.056667 RUSSIA | 1992 | 462536104.807379 SAUDI ARABIA | 1998 | 274843290.375884 SAUDI ARABIA | 1997 | 457830475.256838 SAUDI ARABIA | 1996 | 468111119.881391 SAUDI ARABIA | 1995 | 465380624.712379 SAUDI ARABIA | 1994 | 469375084.262613 SAUDI ARABIA | 1993 | 464152407.975379 SAUDI ARABIA | 1992 | 468292626.04878 UNITED KINGDOM | 1998 | 268283598.135441 UNITED KINGDOM | 1997 | 460497894.500109 UNITED KINGDOM | 1996 | 465529160.667264 UNITED KINGDOM | 1995 | 467111370.099808 UNITED KINGDOM | 1994 | 462650785.290725 UNITED KINGDOM | 1993 | 470403534.057799 UNITED KINGDOM | 1992 | 459077136.543872 UNITED STATES | 1998 | 267610659.998138 UNITED STATES | 1997 | 463348994.826746 UNITED STATES | 1996 | 468194320.735918 UNITED STATES | 1995 | 467905824.101102 UNITED STATES | 1994 | 474692827.385266 UNITED STATES | 1993 | 462030172.608443 UNITED STATES | 1992 | 459406993.453381 VIETNAM | 1998 | 269847694.936223 VIETNAM | 1997 | 463142999.664912 VIETNAM | 1996 | 462505247.716467 VIETNAM | 1995 | 461545576.944296 VIETNAM | 1994 | 466136471.015528 VIETNAM | 1993 | 464745076.903892 VIETNAM | 1992 | 476385592.228892 (175 rows) COMMIT; COMMIT