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 | 270301494.991734 ALGERIA | 1997 | 459265411.509354 ALGERIA | 1996 | 470079984.191851 ALGERIA | 1995 | 462197545.789187 ALGERIA | 1994 | 465811390.05397 ALGERIA | 1993 | 462043620.237637 ALGERIA | 1992 | 465241917.061372 ARGENTINA | 1998 | 272178181.095825 ARGENTINA | 1997 | 468120202.624994 ARGENTINA | 1996 | 475971806.904419 ARGENTINA | 1995 | 470110927.04852 ARGENTINA | 1994 | 475881521.513238 ARGENTINA | 1993 | 472882871.746062 ARGENTINA | 1992 | 477511185.40173 BRAZIL | 1998 | 276470126.208199 BRAZIL | 1997 | 465201242.602608 BRAZIL | 1996 | 469630110.959413 BRAZIL | 1995 | 461067541.21466 BRAZIL | 1994 | 468784853.981002 BRAZIL | 1993 | 461603249.035143 BRAZIL | 1992 | 464715141.847369 CANADA | 1998 | 280595533.502738 CANADA | 1997 | 474688251.897941 CANADA | 1996 | 481876846.260556 CANADA | 1995 | 476081831.132548 CANADA | 1994 | 480153632.553186 CANADA | 1993 | 477733618.083076 CANADA | 1992 | 471472562.368326 CHINA | 1998 | 270727097.039551 CHINA | 1997 | 457658147.325585 CHINA | 1996 | 466576132.254083 CHINA | 1995 | 456585022.244585 CHINA | 1994 | 462184572.537849 CHINA | 1993 | 464404518.478428 CHINA | 1992 | 454644847.962486 EGYPT | 1998 | 279510928.626657 EGYPT | 1997 | 462516750.143217 EGYPT | 1996 | 462521522.636617 EGYPT | 1995 | 455926074.598594 EGYPT | 1994 | 461559163.46246 EGYPT | 1993 | 461928625.570523 EGYPT | 1992 | 465485940.975675 ETHIOPIA | 1998 | 274212976.883859 ETHIOPIA | 1997 | 466825756.915108 ETHIOPIA | 1996 | 468266519.257956 ETHIOPIA | 1995 | 469638869.214939 ETHIOPIA | 1994 | 471033936.148834 ETHIOPIA | 1993 | 461175907.226139 ETHIOPIA | 1992 | 465642446.952582 FRANCE | 1998 | 264952874.865242 FRANCE | 1997 | 454171419.459215 FRANCE | 1996 | 464806738.699807 FRANCE | 1995 | 455603997.325444 FRANCE | 1994 | 459511478.282911 FRANCE | 1993 | 453810626.689646 FRANCE | 1992 | 460725509.931337 GERMANY | 1998 | 274872870.29671 GERMANY | 1997 | 475234248.20433 GERMANY | 1996 | 479194276.946991 GERMANY | 1995 | 481105690.947087 GERMANY | 1994 | 475789942.440674 GERMANY | 1993 | 480313105.025722 GERMANY | 1992 | 479064529.752049 INDIA | 1998 | 282377240.570409 INDIA | 1997 | 483494042.039257 INDIA | 1996 | 484265662.691759 INDIA | 1995 | 474843492.776194 INDIA | 1994 | 485193499.910361 INDIA | 1993 | 481247876.255474 INDIA | 1992 | 487182965.034822 INDONESIA | 1998 | 269617289.677192 INDONESIA | 1997 | 460506927.690009 INDONESIA | 1996 | 466904922.757727 INDONESIA | 1995 | 462553163.934656 INDONESIA | 1994 | 467317373.291624 INDONESIA | 1993 | 466959902.835473 INDONESIA | 1992 | 470684094.543301 IRAN | 1998 | 271571409.279817 IRAN | 1997 | 478945625.016459 IRAN | 1996 | 471225245.48943 IRAN | 1995 | 474580827.936186 IRAN | 1994 | 464916729.460176 IRAN | 1993 | 469087915.816885 IRAN | 1992 | 478055452.232018 IRAQ | 1998 | 280959606.451122 IRAQ | 1997 | 468335023.061503 IRAQ | 1996 | 482870677.185564 IRAQ | 1995 | 471950291.573279 IRAQ | 1994 | 463053306.313922 IRAQ | 1993 | 481685732.612358 IRAQ | 1992 | 472468710.907319 JAPAN | 1998 | 272919669.501773 JAPAN | 1997 | 457047429.727432 JAPAN | 1996 | 467245837.969116 JAPAN | 1995 | 464767882.673203 JAPAN | 1994 | 462467832.648801 JAPAN | 1993 | 458135072.279273 JAPAN | 1992 | 458298928.253436 JORDAN | 1998 | 260322467.664173 JORDAN | 1997 | 450875895.324678 JORDAN | 1996 | 452696349.318085 JORDAN | 1995 | 459357633.092888 JORDAN | 1994 | 451722971.952514 JORDAN | 1993 | 460327893.634262 JORDAN | 1992 | 450189504.34248 KENYA | 1998 | 276080799.483157 KENYA | 1997 | 475028505.566024 KENYA | 1996 | 472236953.655179 KENYA | 1995 | 473923059.950282 KENYA | 1994 | 468410959.760657 KENYA | 1993 | 473255821.431987 KENYA | 1992 | 476017450.258128 MOROCCO | 1998 | 272288671.568904 MOROCCO | 1997 | 465649334.747219 MOROCCO | 1996 | 463759628.661477 MOROCCO | 1995 | 464275999.85598 MOROCCO | 1994 | 456492491.613212 MOROCCO | 1993 | 466108283.888741 MOROCCO | 1992 | 464492453.173321 MOZAMBIQUE | 1998 | 270177400.856012 MOZAMBIQUE | 1997 | 467711999.313604 MOZAMBIQUE | 1996 | 460375307.048535 MOZAMBIQUE | 1995 | 469499785.782855 MOZAMBIQUE | 1994 | 458624734.808696 MOZAMBIQUE | 1993 | 464589886.628067 MOZAMBIQUE | 1992 | 460260702.279748 PERU | 1998 | 270622145.435457 PERU | 1997 | 464140689.616569 PERU | 1996 | 466427334.688868 PERU | 1995 | 460943160.383645 PERU | 1994 | 458105842.878468 PERU | 1993 | 464779133.916715 PERU | 1992 | 463556677.80648 ROMANIA | 1998 | 276687376.254679 ROMANIA | 1997 | 472235042.218075 ROMANIA | 1996 | 471812539.727738 ROMANIA | 1995 | 472625606.288436 ROMANIA | 1994 | 469526221.40854 ROMANIA | 1993 | 473480799.785971 ROMANIA | 1992 | 475284259.696172 RUSSIA | 1998 | 267313904.863036 RUSSIA | 1997 | 468613918.339608 RUSSIA | 1996 | 464676392.508184 RUSSIA | 1995 | 462255563.985336 RUSSIA | 1994 | 464827321.243941 RUSSIA | 1993 | 464585074.834852 RUSSIA | 1992 | 462842255.266018 SAUDI ARABIA | 1998 | 274903523.425091 SAUDI ARABIA | 1997 | 457812261.496634 SAUDI ARABIA | 1996 | 467953030.912546 SAUDI ARABIA | 1995 | 465281272.299948 SAUDI ARABIA | 1994 | 469599273.778922 SAUDI ARABIA | 1993 | 464011853.625175 SAUDI ARABIA | 1992 | 468423484.935053 UNITED KINGDOM | 1998 | 268184043.031205 UNITED KINGDOM | 1997 | 460592303.916362 UNITED KINGDOM | 1996 | 465698621.292408 UNITED KINGDOM | 1995 | 467392843.215622 UNITED KINGDOM | 1994 | 462420822.467322 UNITED KINGDOM | 1993 | 470327084.884854 UNITED KINGDOM | 1992 | 459013011.853871 UNITED STATES | 1998 | 267648600.786261 UNITED STATES | 1997 | 463478925.703339 UNITED STATES | 1996 | 468136904.407082 UNITED STATES | 1995 | 468019203.136079 UNITED STATES | 1994 | 474864681.274451 UNITED STATES | 1993 | 461956107.248632 UNITED STATES | 1992 | 459766846.956216 VIETNAM | 1998 | 269958873.5459 VIETNAM | 1997 | 463060119.127332 VIETNAM | 1996 | 462441795.517262 VIETNAM | 1995 | 461759262.698856 VIETNAM | 1994 | 466427164.168365 VIETNAM | 1993 | 464789467.640954 VIETNAM | 1992 | 476464050.575256 (175 rows) COMMIT; COMMIT