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 '%seashell%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267030097.509885 ALGERIA | 1997 | 465785937.266547 ALGERIA | 1996 | 457808213.685864 ALGERIA | 1995 | 458202576.676118 ALGERIA | 1994 | 457366458.558993 ALGERIA | 1993 | 464137512.72353 ALGERIA | 1992 | 458002400.54724 ARGENTINA | 1998 | 280079670.413071 ARGENTINA | 1997 | 473391374.342526 ARGENTINA | 1996 | 462341993.072744 ARGENTINA | 1995 | 464249789.812936 ARGENTINA | 1994 | 471077395.193893 ARGENTINA | 1993 | 465167832.13946 ARGENTINA | 1992 | 466245547.216661 BRAZIL | 1998 | 274178404.34708 BRAZIL | 1997 | 476035310.716665 BRAZIL | 1996 | 471551100.124953 BRAZIL | 1995 | 466198308.418047 BRAZIL | 1994 | 470878188.926677 BRAZIL | 1993 | 477604619.656995 BRAZIL | 1992 | 473342631.932799 CANADA | 1998 | 271313409.016598 CANADA | 1997 | 474991204.472308 CANADA | 1996 | 472557824.768025 CANADA | 1995 | 476307936.277948 CANADA | 1994 | 481179840.45564 CANADA | 1993 | 462902520.674216 CANADA | 1992 | 471876369.730172 CHINA | 1998 | 271532604.940537 CHINA | 1997 | 476033154.608333 CHINA | 1996 | 470624133.105825 CHINA | 1995 | 464648871.839141 CHINA | 1994 | 465501481.844007 CHINA | 1993 | 476707083.466645 CHINA | 1992 | 474113496.621966 EGYPT | 1998 | 276153030.947821 EGYPT | 1997 | 473537131.899446 EGYPT | 1996 | 468819290.179958 EGYPT | 1995 | 465994549.677889 EGYPT | 1994 | 457147803.015841 EGYPT | 1993 | 473642147.767651 EGYPT | 1992 | 467963800.797613 ETHIOPIA | 1998 | 267503252.950686 ETHIOPIA | 1997 | 454619584.009791 ETHIOPIA | 1996 | 459968090.117643 ETHIOPIA | 1995 | 463767731.29842 ETHIOPIA | 1994 | 457695576.601827 ETHIOPIA | 1993 | 462936365.079616 ETHIOPIA | 1992 | 464292885.936266 FRANCE | 1998 | 269015079.715401 FRANCE | 1997 | 456546237.218226 FRANCE | 1996 | 460496076.787829 FRANCE | 1995 | 473331927.753756 FRANCE | 1994 | 459799113.452107 FRANCE | 1993 | 467955096.338063 FRANCE | 1992 | 469453579.580098 GERMANY | 1998 | 277911170.756946 GERMANY | 1997 | 480163374.924276 GERMANY | 1996 | 481227123.005244 GERMANY | 1995 | 481505575.659256 GERMANY | 1994 | 469275929.081641 GERMANY | 1993 | 481363339.779504 GERMANY | 1992 | 477325675.830458 INDIA | 1998 | 266464575.895752 INDIA | 1997 | 470055801.823054 INDIA | 1996 | 465367038.557322 INDIA | 1995 | 465130413.252848 INDIA | 1994 | 466876682.010559 INDIA | 1993 | 466979229.988993 INDIA | 1992 | 473990804.136843 INDONESIA | 1998 | 270634156.173116 INDONESIA | 1997 | 456732790.802168 INDONESIA | 1996 | 460808491.166853 INDONESIA | 1995 | 459396988.924718 INDONESIA | 1994 | 460039601.308127 INDONESIA | 1993 | 463731996.881098 INDONESIA | 1992 | 461853959.338144 IRAN | 1998 | 273036338.17377 IRAN | 1997 | 460632240.599433 IRAN | 1996 | 470300672.225065 IRAN | 1995 | 462835471.357547 IRAN | 1994 | 459073536.655087 IRAN | 1993 | 453203782.311705 IRAN | 1992 | 462279982.137312 IRAQ | 1998 | 275783642.606555 IRAQ | 1997 | 473779434.133016 IRAQ | 1996 | 475879147.400093 IRAQ | 1995 | 473032148.186892 IRAQ | 1994 | 478867479.115419 IRAQ | 1993 | 466347187.412948 IRAQ | 1992 | 479525137.908716 JAPAN | 1998 | 273398951.716696 JAPAN | 1997 | 467963437.221511 JAPAN | 1996 | 470742321.855499 JAPAN | 1995 | 475066851.194208 JAPAN | 1994 | 474901690.105914 JAPAN | 1993 | 477394913.096078 JAPAN | 1992 | 469569250.088849 JORDAN | 1998 | 273827691.08609 JORDAN | 1997 | 461547094.448356 JORDAN | 1996 | 459826502.40905 JORDAN | 1995 | 457183195.405901 JORDAN | 1994 | 464736420.299176 JORDAN | 1993 | 463259443.449662 JORDAN | 1992 | 455532270.188018 KENYA | 1998 | 280215242.569921 KENYA | 1997 | 477054724.129053 KENYA | 1996 | 471554617.916418 KENYA | 1995 | 472752750.249744 KENYA | 1994 | 468882314.538147 KENYA | 1993 | 475756676.461085 KENYA | 1992 | 480825920.536877 MOROCCO | 1998 | 279018752.040669 MOROCCO | 1997 | 479769560.539741 MOROCCO | 1996 | 475088871.551102 MOROCCO | 1995 | 484055002.334191 MOROCCO | 1994 | 482725109.9422 MOROCCO | 1993 | 478602130.528246 MOROCCO | 1992 | 475493448.211727 MOZAMBIQUE | 1998 | 273507359.648532 MOZAMBIQUE | 1997 | 462284169.854942 MOZAMBIQUE | 1996 | 467360050.418389 MOZAMBIQUE | 1995 | 459379502.866542 MOZAMBIQUE | 1994 | 460510373.204527 MOZAMBIQUE | 1993 | 466485160.641792 MOZAMBIQUE | 1992 | 458915183.798887 PERU | 1998 | 274328395.852778 PERU | 1997 | 469033389.622051 PERU | 1996 | 468075705.594616 PERU | 1995 | 458370561.819629 PERU | 1994 | 469000719.214605 PERU | 1993 | 464505596.393602 PERU | 1992 | 466747940.834539 ROMANIA | 1998 | 269167325.383169 ROMANIA | 1997 | 460868004.852288 ROMANIA | 1996 | 473361646.098282 ROMANIA | 1995 | 472020928.315166 ROMANIA | 1994 | 469948799.313677 ROMANIA | 1993 | 465504664.594335 ROMANIA | 1992 | 454667843.842259 RUSSIA | 1998 | 283816791.394224 RUSSIA | 1997 | 482632133.922358 RUSSIA | 1996 | 480870003.371269 RUSSIA | 1995 | 479537108.518438 RUSSIA | 1994 | 475612411.90878 RUSSIA | 1993 | 484985327.537571 RUSSIA | 1992 | 482460439.226135 SAUDI ARABIA | 1998 | 275530259.350217 SAUDI ARABIA | 1997 | 471893301.063851 SAUDI ARABIA | 1996 | 471117910.964864 SAUDI ARABIA | 1995 | 465673643.834858 SAUDI ARABIA | 1994 | 472409392.687751 SAUDI ARABIA | 1993 | 460449463.936506 SAUDI ARABIA | 1992 | 469747019.590926 UNITED KINGDOM | 1998 | 273293683.274235 UNITED KINGDOM | 1997 | 461272399.511882 UNITED KINGDOM | 1996 | 464172559.618632 UNITED KINGDOM | 1995 | 466408536.816226 UNITED KINGDOM | 1994 | 463751918.478738 UNITED KINGDOM | 1993 | 468978910.449491 UNITED KINGDOM | 1992 | 466300117.657729 UNITED STATES | 1998 | 271973008.822721 UNITED STATES | 1997 | 464178597.990495 UNITED STATES | 1996 | 472265927.756561 UNITED STATES | 1995 | 470115463.724559 UNITED STATES | 1994 | 462220771.767494 UNITED STATES | 1993 | 465025259.393525 UNITED STATES | 1992 | 460166623.352073 VIETNAM | 1998 | 274821903.821382 VIETNAM | 1997 | 468654652.63678 VIETNAM | 1996 | 471431290.725215 VIETNAM | 1995 | 461155119.664918 VIETNAM | 1994 | 465558985.776979 VIETNAM | 1993 | 471814629.644834 VIETNAM | 1992 | 463742759.081756 (175 rows) COMMIT; COMMIT