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 | 270251167.395495 ALGERIA | 1997 | 459863583.296134 ALGERIA | 1996 | 470455243.797796 ALGERIA | 1995 | 463254586.27754 ALGERIA | 1994 | 466388925.822233 ALGERIA | 1993 | 462421985.223201 ALGERIA | 1992 | 465393058.119094 ARGENTINA | 1998 | 272729093.346072 ARGENTINA | 1997 | 468614384.051548 ARGENTINA | 1996 | 476494664.185128 ARGENTINA | 1995 | 470922286.604015 ARGENTINA | 1994 | 476626332.167794 ARGENTINA | 1993 | 473375746.675958 ARGENTINA | 1992 | 478730086.596276 BRAZIL | 1998 | 275947269.523096 BRAZIL | 1997 | 465998951.636504 BRAZIL | 1996 | 469687819.601559 BRAZIL | 1995 | 462511170.227405 BRAZIL | 1994 | 469460152.317082 BRAZIL | 1993 | 461914724.380354 BRAZIL | 1992 | 464939161.198386 CANADA | 1998 | 281480015.27778 CANADA | 1997 | 475314857.854043 CANADA | 1996 | 482025779.001154 CANADA | 1995 | 476126120.77961 CANADA | 1994 | 480832653.566854 CANADA | 1993 | 479136097.480264 CANADA | 1992 | 471318023.725747 CHINA | 1998 | 270746007.025245 CHINA | 1997 | 457560239.065874 CHINA | 1996 | 466226007.80682 CHINA | 1995 | 457082637.679906 CHINA | 1994 | 462878073.269827 CHINA | 1993 | 464204097.221799 CHINA | 1992 | 454619917.978136 EGYPT | 1998 | 279348534.497934 EGYPT | 1997 | 463589932.439852 EGYPT | 1996 | 462695435.566607 EGYPT | 1995 | 456294758.265621 EGYPT | 1994 | 461863118.965965 EGYPT | 1993 | 462638257.976448 EGYPT | 1992 | 465437258.980995 ETHIOPIA | 1998 | 274714176.228477 ETHIOPIA | 1997 | 466985083.881185 ETHIOPIA | 1996 | 468909210.954828 ETHIOPIA | 1995 | 470436586.505375 ETHIOPIA | 1994 | 471674090.244796 ETHIOPIA | 1993 | 461570797.284014 ETHIOPIA | 1992 | 467027924.338308 FRANCE | 1998 | 265165691.237408 FRANCE | 1997 | 454457479.691791 FRANCE | 1996 | 466500245.809475 FRANCE | 1995 | 456063448.080331 FRANCE | 1994 | 460164786.263332 FRANCE | 1993 | 454718428.951569 FRANCE | 1992 | 461335626.861784 GERMANY | 1998 | 275354602.350576 GERMANY | 1997 | 475155483.999992 GERMANY | 1996 | 479352542.080909 GERMANY | 1995 | 481185554.781159 GERMANY | 1994 | 475815390.347355 GERMANY | 1993 | 480557560.244231 GERMANY | 1992 | 480008864.1878 INDIA | 1998 | 282270160.773743 INDIA | 1997 | 483929609.633091 INDIA | 1996 | 484394062.126797 INDIA | 1995 | 475298885.000543 INDIA | 1994 | 485450896.528382 INDIA | 1993 | 482001014.745957 INDIA | 1992 | 487401318.27556 INDONESIA | 1998 | 270117248.449219 INDONESIA | 1997 | 461196225.505409 INDONESIA | 1996 | 467171848.67793 INDONESIA | 1995 | 462564586.682869 INDONESIA | 1994 | 468228370.3093 INDONESIA | 1993 | 467169758.02631 INDONESIA | 1992 | 470445264.119438 IRAN | 1998 | 272005345.63957 IRAN | 1997 | 480050139.725028 IRAN | 1996 | 471876263.606513 IRAN | 1995 | 474345637.441961 IRAN | 1994 | 466171007.622918 IRAN | 1993 | 468844634.346076 IRAN | 1992 | 478664590.914793 IRAQ | 1998 | 281059109.227546 IRAQ | 1997 | 469423954.017784 IRAQ | 1996 | 483157905.661335 IRAQ | 1995 | 472905656.198469 IRAQ | 1994 | 463627613.373251 IRAQ | 1993 | 481439702.195902 IRAQ | 1992 | 473039798.053502 JAPAN | 1998 | 273326546.378662 JAPAN | 1997 | 456954843.347264 JAPAN | 1996 | 467424132.878969 JAPAN | 1995 | 465569281.452555 JAPAN | 1994 | 463579926.162414 JAPAN | 1993 | 457858579.698363 JAPAN | 1992 | 458558305.03567 JORDAN | 1998 | 260490541.704372 JORDAN | 1997 | 451099884.498129 JORDAN | 1996 | 453264814.510136 JORDAN | 1995 | 459647087.877311 JORDAN | 1994 | 452189384.730299 JORDAN | 1993 | 460605629.507154 JORDAN | 1992 | 450903426.42991 KENYA | 1998 | 276602239.003955 KENYA | 1997 | 475820459.474282 KENYA | 1996 | 472416445.610235 KENYA | 1995 | 475054265.946719 KENYA | 1994 | 469670490.737126 KENYA | 1993 | 473918519.396902 KENYA | 1992 | 475753522.822682 MOROCCO | 1998 | 272949651.605072 MOROCCO | 1997 | 466192298.96102 MOROCCO | 1996 | 464663269.560023 MOROCCO | 1995 | 464684929.513831 MOROCCO | 1994 | 457272874.203853 MOROCCO | 1993 | 467130122.728167 MOROCCO | 1992 | 464964921.759354 MOZAMBIQUE | 1998 | 270948501.820353 MOZAMBIQUE | 1997 | 467607516.073098 MOZAMBIQUE | 1996 | 460792009.550411 MOZAMBIQUE | 1995 | 469902836.325323 MOZAMBIQUE | 1994 | 458898870.267973 MOZAMBIQUE | 1993 | 464867996.262702 MOZAMBIQUE | 1992 | 460191982.49278 PERU | 1998 | 271652613.473854 PERU | 1997 | 464583330.492716 PERU | 1996 | 466371086.176272 PERU | 1995 | 461536762.870985 PERU | 1994 | 458162702.15784 PERU | 1993 | 464296992.77314 PERU | 1992 | 464014507.205989 ROMANIA | 1998 | 277201355.979393 ROMANIA | 1997 | 472194583.767761 ROMANIA | 1996 | 471528570.873975 ROMANIA | 1995 | 473153440.615015 ROMANIA | 1994 | 470281240.950385 ROMANIA | 1993 | 473858648.844208 ROMANIA | 1992 | 475627929.888907 RUSSIA | 1998 | 267690449.040843 RUSSIA | 1997 | 468296275.264237 RUSSIA | 1996 | 465481164.280742 RUSSIA | 1995 | 462386246.267342 RUSSIA | 1994 | 466013019.86313 RUSSIA | 1993 | 465838191.718371 RUSSIA | 1992 | 463420119.132411 SAUDI ARABIA | 1998 | 274683134.54652 SAUDI ARABIA | 1997 | 458527942.889694 SAUDI ARABIA | 1996 | 468501476.406812 SAUDI ARABIA | 1995 | 466075042.45698 SAUDI ARABIA | 1994 | 470306608.388508 SAUDI ARABIA | 1993 | 464781866.175894 SAUDI ARABIA | 1992 | 468770502.049285 UNITED KINGDOM | 1998 | 268751306.709846 UNITED KINGDOM | 1997 | 461671816.102304 UNITED KINGDOM | 1996 | 465483418.585287 UNITED KINGDOM | 1995 | 468390639.400111 UNITED KINGDOM | 1994 | 462566883.626777 UNITED KINGDOM | 1993 | 471154469.71751 UNITED KINGDOM | 1992 | 459342374.236454 UNITED STATES | 1998 | 267963683.536102 UNITED STATES | 1997 | 463948684.258541 UNITED STATES | 1996 | 468815962.343548 UNITED STATES | 1995 | 468875588.690934 UNITED STATES | 1994 | 475200760.820646 UNITED STATES | 1993 | 462411086.09484 UNITED STATES | 1992 | 460138302.274963 VIETNAM | 1998 | 270302290.51451 VIETNAM | 1997 | 463235617.458548 VIETNAM | 1996 | 463123313.28424 VIETNAM | 1995 | 461539214.04172 VIETNAM | 1994 | 467378158.849289 VIETNAM | 1993 | 465300863.806543 VIETNAM | 1992 | 476678323.565112 (175 rows) COMMIT; COMMIT