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 '%firebrick%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264936470.66068 ALGERIA | 1997 | 458180720.920921 ALGERIA | 1996 | 462578724.093595 ALGERIA | 1995 | 457964682.70812 ALGERIA | 1994 | 461339852.798292 ALGERIA | 1993 | 461570430.192504 ALGERIA | 1992 | 465467828.718271 ARGENTINA | 1998 | 274044183.753993 ARGENTINA | 1997 | 474471869.546698 ARGENTINA | 1996 | 461481903.256365 ARGENTINA | 1995 | 471799047.152055 ARGENTINA | 1994 | 470465996.645973 ARGENTINA | 1993 | 464499491.825422 ARGENTINA | 1992 | 467148590.406493 BRAZIL | 1998 | 281004826.028916 BRAZIL | 1997 | 477326387.703484 BRAZIL | 1996 | 472274508.383865 BRAZIL | 1995 | 470901804.194206 BRAZIL | 1994 | 473310712.205811 BRAZIL | 1993 | 469092024.434693 BRAZIL | 1992 | 477540730.785176 CANADA | 1998 | 277638549.740662 CANADA | 1997 | 475326814.114289 CANADA | 1996 | 473804569.127291 CANADA | 1995 | 471692424.210787 CANADA | 1994 | 471203186.676158 CANADA | 1993 | 476116170.805852 CANADA | 1992 | 471636388.916515 CHINA | 1998 | 268525342.577057 CHINA | 1997 | 459855328.577641 CHINA | 1996 | 460208680.884635 CHINA | 1995 | 455962437.9817 CHINA | 1994 | 454982178.659318 CHINA | 1993 | 456324836.880976 CHINA | 1992 | 455802411.668612 EGYPT | 1998 | 270581485.427347 EGYPT | 1997 | 474420759.718634 EGYPT | 1996 | 475699884.618814 EGYPT | 1995 | 472078994.603448 EGYPT | 1994 | 469361456.955117 EGYPT | 1993 | 472460625.356658 EGYPT | 1992 | 473778134.577251 ETHIOPIA | 1998 | 268623867.878923 ETHIOPIA | 1997 | 451117866.717348 ETHIOPIA | 1996 | 461222342.01374 ETHIOPIA | 1995 | 456757124.75623 ETHIOPIA | 1994 | 461427763.234519 ETHIOPIA | 1993 | 460455589.216977 ETHIOPIA | 1992 | 458006147.743117 FRANCE | 1998 | 270339191.148786 FRANCE | 1997 | 457713257.257278 FRANCE | 1996 | 463678977.844586 FRANCE | 1995 | 452644335.697619 FRANCE | 1994 | 462330927.243513 FRANCE | 1993 | 451720141.702009 FRANCE | 1992 | 459790989.115852 GERMANY | 1998 | 280348271.275044 GERMANY | 1997 | 481167282.314404 GERMANY | 1996 | 475828219.144693 GERMANY | 1995 | 482505365.66042 GERMANY | 1994 | 480416136.955112 GERMANY | 1993 | 479812854.963679 GERMANY | 1992 | 481173361.741458 INDIA | 1998 | 279372844.243739 INDIA | 1997 | 469931488.782249 INDIA | 1996 | 468528318.367272 INDIA | 1995 | 477814172.528268 INDIA | 1994 | 470852262.215266 INDIA | 1993 | 469938101.933455 INDIA | 1992 | 478340266.110478 INDONESIA | 1998 | 267471733.111023 INDONESIA | 1997 | 464968529.569363 INDONESIA | 1996 | 464561924.773166 INDONESIA | 1995 | 465630142.910876 INDONESIA | 1994 | 467818716.189562 INDONESIA | 1993 | 464930841.77196 INDONESIA | 1992 | 468275642.317156 IRAN | 1998 | 273834390.835476 IRAN | 1997 | 474909843.131484 IRAN | 1996 | 470235852.633939 IRAN | 1995 | 465244661.71704 IRAN | 1994 | 471378797.172139 IRAN | 1993 | 470534434.317115 IRAN | 1992 | 475152239.716576 IRAQ | 1998 | 277759655.756897 IRAQ | 1997 | 469942428.933569 IRAQ | 1996 | 476160395.535207 IRAQ | 1995 | 483552779.118043 IRAQ | 1994 | 471777782.216534 IRAQ | 1993 | 477397403.853204 IRAQ | 1992 | 477110237.094399 JAPAN | 1998 | 275398083.906067 JAPAN | 1997 | 474064005.27086 JAPAN | 1996 | 473938315.187226 JAPAN | 1995 | 472086404.986937 JAPAN | 1994 | 477324303.614887 JAPAN | 1993 | 467215061.873124 JAPAN | 1992 | 468954712.283382 JORDAN | 1998 | 261950005.780559 JORDAN | 1997 | 450471268.895027 JORDAN | 1996 | 455606809.036929 JORDAN | 1995 | 446478646.174214 JORDAN | 1994 | 456441881.806884 JORDAN | 1993 | 455497142.580852 JORDAN | 1992 | 451524441.738582 KENYA | 1998 | 275032800.848174 KENYA | 1997 | 468505249.818749 KENYA | 1996 | 477214932.892632 KENYA | 1995 | 468829290.73477 KENYA | 1994 | 475934769.999746 KENYA | 1993 | 468836768.269634 KENYA | 1992 | 484423112.377838 MOROCCO | 1998 | 275168482.231283 MOROCCO | 1997 | 468635549.73691 MOROCCO | 1996 | 471712262.26601 MOROCCO | 1995 | 465001011.943574 MOROCCO | 1994 | 469850283.171733 MOROCCO | 1993 | 468539558.619546 MOROCCO | 1992 | 474182232.567528 MOZAMBIQUE | 1998 | 267045968.969538 MOZAMBIQUE | 1997 | 453462160.32256 MOZAMBIQUE | 1996 | 469023937.50739 MOZAMBIQUE | 1995 | 458377156.307486 MOZAMBIQUE | 1994 | 452139863.691701 MOZAMBIQUE | 1993 | 462617824.496374 MOZAMBIQUE | 1992 | 461530114.390221 PERU | 1998 | 275115068.618363 PERU | 1997 | 468785753.246265 PERU | 1996 | 470085227.174824 PERU | 1995 | 466589328.698717 PERU | 1994 | 460455163.87709 PERU | 1993 | 470813896.091511 PERU | 1992 | 468024987.898824 ROMANIA | 1998 | 273866917.619281 ROMANIA | 1997 | 461534605.195955 ROMANIA | 1996 | 467998794.93945 ROMANIA | 1995 | 464554588.543415 ROMANIA | 1994 | 463589990.511124 ROMANIA | 1993 | 469420947.438714 ROMANIA | 1992 | 469332015.067798 RUSSIA | 1998 | 278548642.498773 RUSSIA | 1997 | 469478879.731372 RUSSIA | 1996 | 475368988.222416 RUSSIA | 1995 | 472016465.942236 RUSSIA | 1994 | 468042844.613111 RUSSIA | 1993 | 469994161.118744 RUSSIA | 1992 | 462571014.122693 SAUDI ARABIA | 1998 | 285021572.258159 SAUDI ARABIA | 1997 | 472537877.126689 SAUDI ARABIA | 1996 | 477295017.945018 SAUDI ARABIA | 1995 | 476904236.605064 SAUDI ARABIA | 1994 | 469768490.414978 SAUDI ARABIA | 1993 | 474127226.135632 SAUDI ARABIA | 1992 | 472371860.909657 UNITED KINGDOM | 1998 | 272224461.551759 UNITED KINGDOM | 1997 | 466007466.953225 UNITED KINGDOM | 1996 | 464528542.693466 UNITED KINGDOM | 1995 | 462684728.727635 UNITED KINGDOM | 1994 | 464029035.999274 UNITED KINGDOM | 1993 | 464128975.224946 UNITED KINGDOM | 1992 | 468417257.456406 UNITED STATES | 1998 | 271839540.438835 UNITED STATES | 1997 | 457154363.943043 UNITED STATES | 1996 | 463920002.648643 UNITED STATES | 1995 | 468417118.185883 UNITED STATES | 1994 | 471883690.175602 UNITED STATES | 1993 | 467514121.456448 UNITED STATES | 1992 | 464521791.834489 VIETNAM | 1998 | 277879648.630092 VIETNAM | 1997 | 464672116.483678 VIETNAM | 1996 | 466158334.285617 VIETNAM | 1995 | 468484626.557665 VIETNAM | 1994 | 464412941.046434 VIETNAM | 1993 | 471784890.536773 VIETNAM | 1992 | 460902321.486408 (175 rows) COMMIT; COMMIT