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 '%sienna%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270641767.341404 ALGERIA | 1997 | 452703176.489434 ALGERIA | 1996 | 464275237.869301 ALGERIA | 1995 | 460254910.640702 ALGERIA | 1994 | 459951875.852085 ALGERIA | 1993 | 456498021.357302 ALGERIA | 1992 | 464878123.868479 ARGENTINA | 1998 | 279447259.491374 ARGENTINA | 1997 | 474974646.811015 ARGENTINA | 1996 | 471713460.027887 ARGENTINA | 1995 | 469493484.928234 ARGENTINA | 1994 | 470825878.453187 ARGENTINA | 1993 | 469357827.296753 ARGENTINA | 1992 | 478706089.242096 BRAZIL | 1998 | 279300886.835507 BRAZIL | 1997 | 473610046.763471 BRAZIL | 1996 | 475063940.36072 BRAZIL | 1995 | 467551286.970444 BRAZIL | 1994 | 466755395.746917 BRAZIL | 1993 | 467878881.771277 BRAZIL | 1992 | 483996057.312994 CANADA | 1998 | 278707108.515257 CANADA | 1997 | 475872917.239188 CANADA | 1996 | 480225307.184091 CANADA | 1995 | 473517284.900837 CANADA | 1994 | 481723392.809382 CANADA | 1993 | 473852980.564547 CANADA | 1992 | 479174958.331997 CHINA | 1998 | 274845387.823005 CHINA | 1997 | 469115797.959828 CHINA | 1996 | 467984848.024995 CHINA | 1995 | 475181810.994404 CHINA | 1994 | 471262455.17195 CHINA | 1993 | 471807317.325032 CHINA | 1992 | 468822271.738748 EGYPT | 1998 | 269544725.495338 EGYPT | 1997 | 465221057.060798 EGYPT | 1996 | 464710300.193039 EGYPT | 1995 | 458662715.853246 EGYPT | 1994 | 458872517.074577 EGYPT | 1993 | 463189504.197782 EGYPT | 1992 | 468453740.378518 ETHIOPIA | 1998 | 272065224.757355 ETHIOPIA | 1997 | 457475278.330531 ETHIOPIA | 1996 | 461070948.427624 ETHIOPIA | 1995 | 471232235.234712 ETHIOPIA | 1994 | 455250136.990991 ETHIOPIA | 1993 | 468742070.12754 ETHIOPIA | 1992 | 461737434.888669 FRANCE | 1998 | 273371549.99914 FRANCE | 1997 | 464369829.709356 FRANCE | 1996 | 472698246.919842 FRANCE | 1995 | 465611412.869664 FRANCE | 1994 | 462299575.565653 FRANCE | 1993 | 463806945.925159 FRANCE | 1992 | 472358462.689167 GERMANY | 1998 | 276494584.135688 GERMANY | 1997 | 473853892.209708 GERMANY | 1996 | 484245347.812019 GERMANY | 1995 | 477701813.263033 GERMANY | 1994 | 475861211.020322 GERMANY | 1993 | 473864197.007532 GERMANY | 1992 | 480956395.23086 INDIA | 1998 | 271580422.991257 INDIA | 1997 | 475751881.038768 INDIA | 1996 | 465706081.269838 INDIA | 1995 | 478597006.929447 INDIA | 1994 | 477288276.826653 INDIA | 1993 | 472034696.882553 INDIA | 1992 | 483266539.214333 INDONESIA | 1998 | 273121695.437738 INDONESIA | 1997 | 465093605.415592 INDONESIA | 1996 | 468977691.427206 INDONESIA | 1995 | 458129228.799465 INDONESIA | 1994 | 465222362.161542 INDONESIA | 1993 | 463628590.008345 INDONESIA | 1992 | 459186953.562569 IRAN | 1998 | 268071740.641621 IRAN | 1997 | 466990531.02273 IRAN | 1996 | 476399686.702588 IRAN | 1995 | 463690772.92422 IRAN | 1994 | 461982073.117062 IRAN | 1993 | 461517424.925285 IRAN | 1992 | 464411746.048059 IRAQ | 1998 | 275524621.430745 IRAQ | 1997 | 471344509.418967 IRAQ | 1996 | 472702727.456363 IRAQ | 1995 | 472190511.31332 IRAQ | 1994 | 473905678.328968 IRAQ | 1993 | 463550808.029856 IRAQ | 1992 | 471200905.769698 JAPAN | 1998 | 274844396.47798 JAPAN | 1997 | 473519031.896472 JAPAN | 1996 | 476947412.068185 JAPAN | 1995 | 463348579.40166 JAPAN | 1994 | 469828339.251756 JAPAN | 1993 | 465874854.46225 JAPAN | 1992 | 473122638.884833 JORDAN | 1998 | 270061109.547233 JORDAN | 1997 | 464375878.796191 JORDAN | 1996 | 462757777.160378 JORDAN | 1995 | 467399065.992244 JORDAN | 1994 | 463536376.382056 JORDAN | 1993 | 466462159.523167 JORDAN | 1992 | 461465108.841101 KENYA | 1998 | 275859449.673751 KENYA | 1997 | 465968926.469832 KENYA | 1996 | 476849805.094292 KENYA | 1995 | 467558298.862893 KENYA | 1994 | 472344373.690657 KENYA | 1993 | 476579244.618352 KENYA | 1992 | 471197971.715485 MOROCCO | 1998 | 273867477.78581 MOROCCO | 1997 | 475096043.58039 MOROCCO | 1996 | 471531526.463092 MOROCCO | 1995 | 466592645.959444 MOROCCO | 1994 | 470438429.949282 MOROCCO | 1993 | 473226439.667201 MOROCCO | 1992 | 467117310.921833 MOZAMBIQUE | 1998 | 271003884.703671 MOZAMBIQUE | 1997 | 462160400.276184 MOZAMBIQUE | 1996 | 459846050.387573 MOZAMBIQUE | 1995 | 460563080.379193 MOZAMBIQUE | 1994 | 461937523.04871 MOZAMBIQUE | 1993 | 451910424.182258 MOZAMBIQUE | 1992 | 455735427.052356 PERU | 1998 | 273699334.719884 PERU | 1997 | 467016966.722302 PERU | 1996 | 469893721.24415 PERU | 1995 | 468812345.26275 PERU | 1994 | 469948730.008402 PERU | 1993 | 466138978.061592 PERU | 1992 | 476604906.24715 ROMANIA | 1998 | 273605368.288689 ROMANIA | 1997 | 469954862.357735 ROMANIA | 1996 | 468294844.383735 ROMANIA | 1995 | 467485661.941908 ROMANIA | 1994 | 471432825.821945 ROMANIA | 1993 | 473220311.15035 ROMANIA | 1992 | 467623331.51596 RUSSIA | 1998 | 273497014.950224 RUSSIA | 1997 | 464829215.035121 RUSSIA | 1996 | 473710571.049976 RUSSIA | 1995 | 463602938.853776 RUSSIA | 1994 | 474091432.429743 RUSSIA | 1993 | 463071716.023741 RUSSIA | 1992 | 471936708.857522 SAUDI ARABIA | 1998 | 278624901.052913 SAUDI ARABIA | 1997 | 463637337.403694 SAUDI ARABIA | 1996 | 474257664.073665 SAUDI ARABIA | 1995 | 470316072.727525 SAUDI ARABIA | 1994 | 466575965.000574 SAUDI ARABIA | 1993 | 470359918.002868 SAUDI ARABIA | 1992 | 471258995.790435 UNITED KINGDOM | 1998 | 268686202.244581 UNITED KINGDOM | 1997 | 464372449.652125 UNITED KINGDOM | 1996 | 465213418.144673 UNITED KINGDOM | 1995 | 457915157.094567 UNITED KINGDOM | 1994 | 465461964.83417 UNITED KINGDOM | 1993 | 464823905.305528 UNITED KINGDOM | 1992 | 462122663.21347 UNITED STATES | 1998 | 276232875.576204 UNITED STATES | 1997 | 480388348.120366 UNITED STATES | 1996 | 489359534.778897 UNITED STATES | 1995 | 474181604.8659 UNITED STATES | 1994 | 475363560.327172 UNITED STATES | 1993 | 487193297.402477 UNITED STATES | 1992 | 478603325.588019 VIETNAM | 1998 | 282072882.713743 VIETNAM | 1997 | 461663417.373984 VIETNAM | 1996 | 475937828.317473 VIETNAM | 1995 | 466001745.379583 VIETNAM | 1994 | 465050665.976055 VIETNAM | 1993 | 466056769.994609 VIETNAM | 1992 | 470241080.619291 (175 rows) COMMIT; COMMIT