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 '%navy%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267983493.094306 ALGERIA | 1997 | 459794763.162623 ALGERIA | 1996 | 458929873.147978 ALGERIA | 1995 | 465215552.835112 ALGERIA | 1994 | 452849823.533523 ALGERIA | 1993 | 450664039.931316 ALGERIA | 1992 | 458047667.593414 ARGENTINA | 1998 | 278588675.267134 ARGENTINA | 1997 | 478499181.806603 ARGENTINA | 1996 | 484327221.174112 ARGENTINA | 1995 | 474014223.303702 ARGENTINA | 1994 | 479236941.411702 ARGENTINA | 1993 | 472889974.192775 ARGENTINA | 1992 | 468012825.345627 BRAZIL | 1998 | 268921954.640945 BRAZIL | 1997 | 465221923.234505 BRAZIL | 1996 | 464217836.497968 BRAZIL | 1995 | 464575247.320132 BRAZIL | 1994 | 460731949.124864 BRAZIL | 1993 | 462011355.420406 BRAZIL | 1992 | 461853866.897131 CANADA | 1998 | 275791836.825777 CANADA | 1997 | 472871681.486559 CANADA | 1996 | 467705565.295982 CANADA | 1995 | 469030226.051572 CANADA | 1994 | 478287017.962963 CANADA | 1993 | 475243578.221822 CANADA | 1992 | 473177810.723236 CHINA | 1998 | 265548253.516007 CHINA | 1997 | 459190456.392954 CHINA | 1996 | 448149640.944982 CHINA | 1995 | 459689264.209585 CHINA | 1994 | 459642878.118595 CHINA | 1993 | 459718324.572798 CHINA | 1992 | 462619418.222312 EGYPT | 1998 | 272705655.749671 EGYPT | 1997 | 462838542.687238 EGYPT | 1996 | 458381995.048067 EGYPT | 1995 | 464715090.49983 EGYPT | 1994 | 467618765.05242 EGYPT | 1993 | 468479993.490612 EGYPT | 1992 | 464529381.656034 ETHIOPIA | 1998 | 268067284.173406 ETHIOPIA | 1997 | 451292089.829474 ETHIOPIA | 1996 | 449336681.731711 ETHIOPIA | 1995 | 453202374.978035 ETHIOPIA | 1994 | 455748100.547291 ETHIOPIA | 1993 | 454770608.116771 ETHIOPIA | 1992 | 454563379.94901 FRANCE | 1998 | 264368985.226844 FRANCE | 1997 | 457030762.384832 FRANCE | 1996 | 455369091.217802 FRANCE | 1995 | 456907164.212072 FRANCE | 1994 | 450565037.338158 FRANCE | 1993 | 453111284.91366 FRANCE | 1992 | 454681263.677292 GERMANY | 1998 | 276582595.883529 GERMANY | 1997 | 464092954.94877 GERMANY | 1996 | 471705586.830094 GERMANY | 1995 | 476897955.71048 GERMANY | 1994 | 477455981.86128 GERMANY | 1993 | 473104781.78317 GERMANY | 1992 | 475055165.258696 INDIA | 1998 | 276635281.782973 INDIA | 1997 | 478774349.029457 INDIA | 1996 | 472015138.18833 INDIA | 1995 | 471113209.13056 INDIA | 1994 | 479016368.653257 INDIA | 1993 | 477914654.543743 INDIA | 1992 | 474781358.789329 INDONESIA | 1998 | 264648572.709175 INDONESIA | 1997 | 454111987.63232 INDONESIA | 1996 | 455494513.125579 INDONESIA | 1995 | 450887072.73537 INDONESIA | 1994 | 460517528.094722 INDONESIA | 1993 | 455471812.826051 INDONESIA | 1992 | 450341809.306512 IRAN | 1998 | 267179979.135442 IRAN | 1997 | 465473710.038506 IRAN | 1996 | 463364654.947616 IRAN | 1995 | 461559795.570426 IRAN | 1994 | 463608809.998495 IRAN | 1993 | 465035254.658091 IRAN | 1992 | 462916815.973925 IRAQ | 1998 | 271584582.598965 IRAQ | 1997 | 463060208.2716 IRAQ | 1996 | 464850347.781776 IRAQ | 1995 | 462077110.260869 IRAQ | 1994 | 463518174.092722 IRAQ | 1993 | 467387816.850037 IRAQ | 1992 | 461021436.509151 JAPAN | 1998 | 268160828.699245 JAPAN | 1997 | 463886825.403831 JAPAN | 1996 | 462261689.477194 JAPAN | 1995 | 458068908.241113 JAPAN | 1994 | 458630999.75365 JAPAN | 1993 | 464095182.732513 JAPAN | 1992 | 460303204.624 JORDAN | 1998 | 271827853.173808 JORDAN | 1997 | 459904355.940992 JORDAN | 1996 | 456324619.217595 JORDAN | 1995 | 459198406.646443 JORDAN | 1994 | 451992544.212455 JORDAN | 1993 | 463519031.626307 JORDAN | 1992 | 460604683.538119 KENYA | 1998 | 278651902.434185 KENYA | 1997 | 475902659.209443 KENYA | 1996 | 475344677.709339 KENYA | 1995 | 477490077.063758 KENYA | 1994 | 468714613.225572 KENYA | 1993 | 473334216.570627 KENYA | 1992 | 462551111.075974 MOROCCO | 1998 | 272548080.29539 MOROCCO | 1997 | 471615097.058947 MOROCCO | 1996 | 464880202.531054 MOROCCO | 1995 | 466895386.227861 MOROCCO | 1994 | 470437009.364858 MOROCCO | 1993 | 456349173.017711 MOROCCO | 1992 | 467196617.794976 MOZAMBIQUE | 1998 | 271474683.651303 MOZAMBIQUE | 1997 | 457986467.463289 MOZAMBIQUE | 1996 | 461449373.9001 MOZAMBIQUE | 1995 | 459030649.868713 MOZAMBIQUE | 1994 | 468768158.819242 MOZAMBIQUE | 1993 | 463896602.678723 MOZAMBIQUE | 1992 | 467051982.157542 PERU | 1998 | 273130099.369418 PERU | 1997 | 467014769.098378 PERU | 1996 | 461437612.002677 PERU | 1995 | 454145755.910446 PERU | 1994 | 465673830.445524 PERU | 1993 | 463468029.382173 PERU | 1992 | 473438256.883651 ROMANIA | 1998 | 271726963.0924 ROMANIA | 1997 | 460898918.011408 ROMANIA | 1996 | 471020210.988985 ROMANIA | 1995 | 470100591.847274 ROMANIA | 1994 | 464907655.947123 ROMANIA | 1993 | 457200918.322177 ROMANIA | 1992 | 467721745.075258 RUSSIA | 1998 | 277127250.808451 RUSSIA | 1997 | 479093902.559705 RUSSIA | 1996 | 477744456.099442 RUSSIA | 1995 | 468710720.2258 RUSSIA | 1994 | 468767182.486761 RUSSIA | 1993 | 475256294.229144 RUSSIA | 1992 | 468839500.151832 SAUDI ARABIA | 1998 | 279471983.036218 SAUDI ARABIA | 1997 | 465706287.695591 SAUDI ARABIA | 1996 | 465923526.559041 SAUDI ARABIA | 1995 | 479101215.720036 SAUDI ARABIA | 1994 | 472316507.808956 SAUDI ARABIA | 1993 | 462523960.909194 SAUDI ARABIA | 1992 | 469286654.445343 UNITED KINGDOM | 1998 | 271994609.376298 UNITED KINGDOM | 1997 | 463704598.139719 UNITED KINGDOM | 1996 | 467185354.812647 UNITED KINGDOM | 1995 | 458904069.010036 UNITED KINGDOM | 1994 | 460058071.001419 UNITED KINGDOM | 1993 | 468568550.971888 UNITED KINGDOM | 1992 | 461735251.264899 UNITED STATES | 1998 | 281303567.870393 UNITED STATES | 1997 | 471904483.545916 UNITED STATES | 1996 | 470935342.446619 UNITED STATES | 1995 | 470912267.531901 UNITED STATES | 1994 | 472571876.398835 UNITED STATES | 1993 | 459835121.897296 UNITED STATES | 1992 | 471605373.240924 VIETNAM | 1998 | 273162216.673661 VIETNAM | 1997 | 454186536.173212 VIETNAM | 1996 | 455440814.674415 VIETNAM | 1995 | 463162263.252393 VIETNAM | 1994 | 455615420.555542 VIETNAM | 1993 | 458753226.781365 VIETNAM | 1992 | 459665085.909572 (175 rows) COMMIT; COMMIT