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 '%lawn%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 269148387.433127 ALGERIA | 1997 | 455413182.949561 ALGERIA | 1996 | 454474084.397281 ALGERIA | 1995 | 460552091.48612 ALGERIA | 1994 | 455241951.164579 ALGERIA | 1993 | 449511503.363289 ALGERIA | 1992 | 459839201.119876 ARGENTINA | 1998 | 277091974.758578 ARGENTINA | 1997 | 471686703.416591 ARGENTINA | 1996 | 466709402.744912 ARGENTINA | 1995 | 472257403.327147 ARGENTINA | 1994 | 470634418.034469 ARGENTINA | 1993 | 466177773.825534 ARGENTINA | 1992 | 468816190.274723 BRAZIL | 1998 | 272408451.865344 BRAZIL | 1997 | 468798829.08129 BRAZIL | 1996 | 466301125.911852 BRAZIL | 1995 | 465185739.302718 BRAZIL | 1994 | 472094448.943969 BRAZIL | 1993 | 466838793.420955 BRAZIL | 1992 | 474203794.656676 CANADA | 1998 | 278000793.600273 CANADA | 1997 | 471940234.449008 CANADA | 1996 | 469180526.752542 CANADA | 1995 | 476711894.782715 CANADA | 1994 | 485589562.766684 CANADA | 1993 | 472684934.275095 CANADA | 1992 | 478115343.203097 CHINA | 1998 | 276789735.327465 CHINA | 1997 | 470902436.837968 CHINA | 1996 | 467724767.129226 CHINA | 1995 | 477267238.861583 CHINA | 1994 | 472407129.750292 CHINA | 1993 | 460963684.751246 CHINA | 1992 | 470041894.13112 EGYPT | 1998 | 272066353.223979 EGYPT | 1997 | 458904409.07676 EGYPT | 1996 | 462146888.877953 EGYPT | 1995 | 463492759.755181 EGYPT | 1994 | 461827292.147531 EGYPT | 1993 | 466143228.646825 EGYPT | 1992 | 463606625.621384 ETHIOPIA | 1998 | 262395902.669361 ETHIOPIA | 1997 | 441635578.855745 ETHIOPIA | 1996 | 456077136.127663 ETHIOPIA | 1995 | 459301730.666961 ETHIOPIA | 1994 | 457495694.725743 ETHIOPIA | 1993 | 452357030.997298 ETHIOPIA | 1992 | 463663412.237436 FRANCE | 1998 | 266580646.228803 FRANCE | 1997 | 456524162.852019 FRANCE | 1996 | 464248114.239217 FRANCE | 1995 | 454634465.104617 FRANCE | 1994 | 455870504.237466 FRANCE | 1993 | 457712678.959904 FRANCE | 1992 | 457902510.744705 GERMANY | 1998 | 282046372.195873 GERMANY | 1997 | 470475993.290224 GERMANY | 1996 | 477577042.094326 GERMANY | 1995 | 470399180.532043 GERMANY | 1994 | 473103456.559382 GERMANY | 1993 | 476637740.530648 GERMANY | 1992 | 468908389.302164 INDIA | 1998 | 276483867.783325 INDIA | 1997 | 483610878.306764 INDIA | 1996 | 475677741.896366 INDIA | 1995 | 481359711.273665 INDIA | 1994 | 476902075.696128 INDIA | 1993 | 481221434.460415 INDIA | 1992 | 477491462.022596 INDONESIA | 1998 | 274489706.530638 INDONESIA | 1997 | 466595674.168077 INDONESIA | 1996 | 468025710.416964 INDONESIA | 1995 | 466411472.212426 INDONESIA | 1994 | 459753680.292468 INDONESIA | 1993 | 457330859.54739 INDONESIA | 1992 | 466663588.911656 IRAN | 1998 | 272930158.150951 IRAN | 1997 | 460060021.414361 IRAN | 1996 | 468733879.893259 IRAN | 1995 | 464091711.390668 IRAN | 1994 | 467610554.123213 IRAN | 1993 | 469521932.856474 IRAN | 1992 | 463326364.999172 IRAQ | 1998 | 263031776.558952 IRAQ | 1997 | 462191261.407128 IRAQ | 1996 | 466646895.394565 IRAQ | 1995 | 462737367.636241 IRAQ | 1994 | 453050477.1524 IRAQ | 1993 | 465194001.797167 IRAQ | 1992 | 463351053.138222 JAPAN | 1998 | 271556239.256719 JAPAN | 1997 | 470525312.635068 JAPAN | 1996 | 471736306.517154 JAPAN | 1995 | 462039027.225419 JAPAN | 1994 | 465872651.551523 JAPAN | 1993 | 463997985.689851 JAPAN | 1992 | 467307535.742983 JORDAN | 1998 | 270182996.260155 JORDAN | 1997 | 457399189.547935 JORDAN | 1996 | 455425689.284899 JORDAN | 1995 | 449836092.00263 JORDAN | 1994 | 456053125.703005 JORDAN | 1993 | 453914012.546369 JORDAN | 1992 | 456787095.332952 KENYA | 1998 | 275449157.24904 KENYA | 1997 | 476965510.96345 KENYA | 1996 | 478008797.435274 KENYA | 1995 | 477769279.517974 KENYA | 1994 | 473311965.347485 KENYA | 1993 | 483526759.323361 KENYA | 1992 | 471145141.060881 MOROCCO | 1998 | 271958810.393868 MOROCCO | 1997 | 466886082.171804 MOROCCO | 1996 | 465075269.432942 MOROCCO | 1995 | 471429962.757409 MOROCCO | 1994 | 467481037.237043 MOROCCO | 1993 | 472522805.976348 MOROCCO | 1992 | 460523642.77298 MOZAMBIQUE | 1998 | 270273768.381036 MOZAMBIQUE | 1997 | 458275083.66434 MOZAMBIQUE | 1996 | 459499789.909106 MOZAMBIQUE | 1995 | 462630237.39289 MOZAMBIQUE | 1994 | 459743258.323725 MOZAMBIQUE | 1993 | 460824282.509923 MOZAMBIQUE | 1992 | 461495694.366537 PERU | 1998 | 274037141.904864 PERU | 1997 | 471159069.039642 PERU | 1996 | 468454121.669818 PERU | 1995 | 468917347.638964 PERU | 1994 | 466450734.70303 PERU | 1993 | 472707896.89599 PERU | 1992 | 465609940.690263 ROMANIA | 1998 | 278377599.268388 ROMANIA | 1997 | 470875347.85925 ROMANIA | 1996 | 479188370.805125 ROMANIA | 1995 | 472777208.772219 ROMANIA | 1994 | 463284435.123213 ROMANIA | 1993 | 473018445.758905 ROMANIA | 1992 | 473452884.927057 RUSSIA | 1998 | 272286552.132671 RUSSIA | 1997 | 467588470.997075 RUSSIA | 1996 | 474404657.986593 RUSSIA | 1995 | 464049158.051274 RUSSIA | 1994 | 475092702.7184 RUSSIA | 1993 | 466573003.008228 RUSSIA | 1992 | 466803274.450274 SAUDI ARABIA | 1998 | 277021045.147173 SAUDI ARABIA | 1997 | 472061731.964446 SAUDI ARABIA | 1996 | 481921898.08524 SAUDI ARABIA | 1995 | 472252684.312789 SAUDI ARABIA | 1994 | 470250683.71859 SAUDI ARABIA | 1993 | 476914533.162436 SAUDI ARABIA | 1992 | 480227818.970607 UNITED KINGDOM | 1998 | 269472661.412838 UNITED KINGDOM | 1997 | 459351134.416405 UNITED KINGDOM | 1996 | 460495827.285854 UNITED KINGDOM | 1995 | 456072711.645141 UNITED KINGDOM | 1994 | 452298162.13997 UNITED KINGDOM | 1993 | 465187795.399524 UNITED KINGDOM | 1992 | 456571971.230742 UNITED STATES | 1998 | 275154714.456726 UNITED STATES | 1997 | 475849767.484841 UNITED STATES | 1996 | 471224818.80453 UNITED STATES | 1995 | 481271567.795528 UNITED STATES | 1994 | 472986638.983407 UNITED STATES | 1993 | 480640716.529882 UNITED STATES | 1992 | 473496993.908413 VIETNAM | 1998 | 275725116.571278 VIETNAM | 1997 | 463250395.417707 VIETNAM | 1996 | 469586766.975569 VIETNAM | 1995 | 461818769.729184 VIETNAM | 1994 | 469887098.110168 VIETNAM | 1993 | 463213808.491843 VIETNAM | 1992 | 471316548.535953 (175 rows) COMMIT; COMMIT