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.094307 ALGERIA | 1997 | 459794763.16262 ALGERIA | 1996 | 458929873.147981 ALGERIA | 1995 | 465215552.835115 ALGERIA | 1994 | 452849823.533525 ALGERIA | 1993 | 450664039.931318 ALGERIA | 1992 | 458047667.593415 ARGENTINA | 1998 | 278588675.267135 ARGENTINA | 1997 | 478499181.8066 ARGENTINA | 1996 | 484327221.174115 ARGENTINA | 1995 | 474014223.303701 ARGENTINA | 1994 | 479236941.411702 ARGENTINA | 1993 | 472889974.192776 ARGENTINA | 1992 | 468012825.345629 BRAZIL | 1998 | 268921954.640946 BRAZIL | 1997 | 465221923.234503 BRAZIL | 1996 | 464217836.497969 BRAZIL | 1995 | 464575247.320131 BRAZIL | 1994 | 460731949.124865 BRAZIL | 1993 | 462011355.420403 BRAZIL | 1992 | 461853866.897131 CANADA | 1998 | 275791836.825776 CANADA | 1997 | 472871681.486558 CANADA | 1996 | 467705565.295976 CANADA | 1995 | 469030226.051573 CANADA | 1994 | 478287017.962965 CANADA | 1993 | 475243578.221825 CANADA | 1992 | 473177810.723237 CHINA | 1998 | 265548253.516008 CHINA | 1997 | 459190456.392954 CHINA | 1996 | 448149640.944982 CHINA | 1995 | 459689264.209584 CHINA | 1994 | 459642878.118595 CHINA | 1993 | 459718324.572799 CHINA | 1992 | 462619418.222311 EGYPT | 1998 | 272705655.74967 EGYPT | 1997 | 462838542.687236 EGYPT | 1996 | 458381995.048065 EGYPT | 1995 | 464715090.49983 EGYPT | 1994 | 467618765.052417 EGYPT | 1993 | 468479993.490613 EGYPT | 1992 | 464529381.656032 ETHIOPIA | 1998 | 268067284.173407 ETHIOPIA | 1997 | 451292089.829474 ETHIOPIA | 1996 | 449336681.73171 ETHIOPIA | 1995 | 453202374.978034 ETHIOPIA | 1994 | 455748100.547289 ETHIOPIA | 1993 | 454770608.116772 ETHIOPIA | 1992 | 454563379.949008 FRANCE | 1998 | 264368985.226845 FRANCE | 1997 | 457030762.384833 FRANCE | 1996 | 455369091.217803 FRANCE | 1995 | 456907164.212071 FRANCE | 1994 | 450565037.338158 FRANCE | 1993 | 453111284.913661 FRANCE | 1992 | 454681263.677286 GERMANY | 1998 | 276582595.883528 GERMANY | 1997 | 464092954.94877 GERMANY | 1996 | 471705586.830095 GERMANY | 1995 | 476897955.71048 GERMANY | 1994 | 477455981.861277 GERMANY | 1993 | 473104781.783171 GERMANY | 1992 | 475055165.258693 INDIA | 1998 | 276635281.782974 INDIA | 1997 | 478774349.02946 INDIA | 1996 | 472015138.188329 INDIA | 1995 | 471113209.130562 INDIA | 1994 | 479016368.653256 INDIA | 1993 | 477914654.543741 INDIA | 1992 | 474781358.789331 INDONESIA | 1998 | 264648572.709176 INDONESIA | 1997 | 454111987.632318 INDONESIA | 1996 | 455494513.12558 INDONESIA | 1995 | 450887072.73537 INDONESIA | 1994 | 460517528.094723 INDONESIA | 1993 | 455471812.826046 INDONESIA | 1992 | 450341809.306513 IRAN | 1998 | 267179979.135441 IRAN | 1997 | 465473710.038505 IRAN | 1996 | 463364654.947619 IRAN | 1995 | 461559795.570426 IRAN | 1994 | 463608809.998495 IRAN | 1993 | 465035254.65809 IRAN | 1992 | 462916815.973924 IRAQ | 1998 | 271584582.598966 IRAQ | 1997 | 463060208.271601 IRAQ | 1996 | 464850347.781776 IRAQ | 1995 | 462077110.260868 IRAQ | 1994 | 463518174.092722 IRAQ | 1993 | 467387816.850039 IRAQ | 1992 | 461021436.509156 JAPAN | 1998 | 268160828.699246 JAPAN | 1997 | 463886825.403831 JAPAN | 1996 | 462261689.477197 JAPAN | 1995 | 458068908.241112 JAPAN | 1994 | 458630999.753651 JAPAN | 1993 | 464095182.732513 JAPAN | 1992 | 460303204.624 JORDAN | 1998 | 271827853.173808 JORDAN | 1997 | 459904355.940992 JORDAN | 1996 | 456324619.217597 JORDAN | 1995 | 459198406.646444 JORDAN | 1994 | 451992544.212456 JORDAN | 1993 | 463519031.626306 JORDAN | 1992 | 460604683.53812 KENYA | 1998 | 278651902.434184 KENYA | 1997 | 475902659.209442 KENYA | 1996 | 475344677.70934 KENYA | 1995 | 477490077.063756 KENYA | 1994 | 468714613.225571 KENYA | 1993 | 473334216.570626 KENYA | 1992 | 462551111.075975 MOROCCO | 1998 | 272548080.29539 MOROCCO | 1997 | 471615097.058946 MOROCCO | 1996 | 464880202.531053 MOROCCO | 1995 | 466895386.227863 MOROCCO | 1994 | 470437009.364856 MOROCCO | 1993 | 456349173.017711 MOROCCO | 1992 | 467196617.794974 MOZAMBIQUE | 1998 | 271474683.651305 MOZAMBIQUE | 1997 | 457986467.463291 MOZAMBIQUE | 1996 | 461449373.9001 MOZAMBIQUE | 1995 | 459030649.868712 MOZAMBIQUE | 1994 | 468768158.819242 MOZAMBIQUE | 1993 | 463896602.678721 MOZAMBIQUE | 1992 | 467051982.15754 PERU | 1998 | 273130099.369418 PERU | 1997 | 467014769.098378 PERU | 1996 | 461437612.002678 PERU | 1995 | 454145755.910449 PERU | 1994 | 465673830.445522 PERU | 1993 | 463468029.382169 PERU | 1992 | 473438256.883652 ROMANIA | 1998 | 271726963.0924 ROMANIA | 1997 | 460898918.011405 ROMANIA | 1996 | 471020210.988985 ROMANIA | 1995 | 470100591.847279 ROMANIA | 1994 | 464907655.947123 ROMANIA | 1993 | 457200918.32218 ROMANIA | 1992 | 467721745.075261 RUSSIA | 1998 | 277127250.808451 RUSSIA | 1997 | 479093902.559707 RUSSIA | 1996 | 477744456.09944 RUSSIA | 1995 | 468710720.2258 RUSSIA | 1994 | 468767182.486758 RUSSIA | 1993 | 475256294.229144 RUSSIA | 1992 | 468839500.151833 SAUDI ARABIA | 1998 | 279471983.036219 SAUDI ARABIA | 1997 | 465706287.695591 SAUDI ARABIA | 1996 | 465923526.55904 SAUDI ARABIA | 1995 | 479101215.720035 SAUDI ARABIA | 1994 | 472316507.808954 SAUDI ARABIA | 1993 | 462523960.909194 SAUDI ARABIA | 1992 | 469286654.445343 UNITED KINGDOM | 1998 | 271994609.376299 UNITED KINGDOM | 1997 | 463704598.139715 UNITED KINGDOM | 1996 | 467185354.812645 UNITED KINGDOM | 1995 | 458904069.010036 UNITED KINGDOM | 1994 | 460058071.00142 UNITED KINGDOM | 1993 | 468568550.971891 UNITED KINGDOM | 1992 | 461735251.264903 UNITED STATES | 1998 | 281303567.870392 UNITED STATES | 1997 | 471904483.545916 UNITED STATES | 1996 | 470935342.446617 UNITED STATES | 1995 | 470912267.531902 UNITED STATES | 1994 | 472571876.398836 UNITED STATES | 1993 | 459835121.897295 UNITED STATES | 1992 | 471605373.240924 VIETNAM | 1998 | 273162216.67366 VIETNAM | 1997 | 454186536.17321 VIETNAM | 1996 | 455440814.674414 VIETNAM | 1995 | 463162263.252393 VIETNAM | 1994 | 455615420.555543 VIETNAM | 1993 | 458753226.781366 VIETNAM | 1992 | 459665085.90957 (175 rows) COMMIT; COMMIT