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 | 267776635.310472 ALGERIA | 1997 | 459683630.730406 ALGERIA | 1996 | 458466957.024735 ALGERIA | 1995 | 464458067.943097 ALGERIA | 1994 | 453106831.7271 ALGERIA | 1993 | 450052513.140884 ALGERIA | 1992 | 457010592.359971 ARGENTINA | 1998 | 278178539.546787 ARGENTINA | 1997 | 478192038.41789 ARGENTINA | 1996 | 482722239.220135 ARGENTINA | 1995 | 473330937.616158 ARGENTINA | 1994 | 477914154.110251 ARGENTINA | 1993 | 471448855.335789 ARGENTINA | 1992 | 468273351.197814 BRAZIL | 1998 | 268798390.600575 BRAZIL | 1997 | 465049036.284047 BRAZIL | 1996 | 463792371.3027 BRAZIL | 1995 | 464107058.722539 BRAZIL | 1994 | 460393339.29838 BRAZIL | 1993 | 462014499.329099 BRAZIL | 1992 | 461505574.796828 CANADA | 1998 | 275785436.975957 CANADA | 1997 | 472287060.039364 CANADA | 1996 | 467027721.514263 CANADA | 1995 | 468660396.874209 CANADA | 1994 | 477719321.345375 CANADA | 1993 | 474865563.636932 CANADA | 1992 | 473129341.511048 CHINA | 1998 | 265527918.883599 CHINA | 1997 | 458282926.81046 CHINA | 1996 | 447859106.112764 CHINA | 1995 | 459484379.921017 CHINA | 1994 | 459457044.365456 CHINA | 1993 | 458674609.329092 CHINA | 1992 | 461921531.537765 EGYPT | 1998 | 271711205.981867 EGYPT | 1997 | 463135916.91027 EGYPT | 1996 | 458687663.49158 EGYPT | 1995 | 464019114.623789 EGYPT | 1994 | 466799650.665498 EGYPT | 1993 | 468225100.540433 EGYPT | 1992 | 464454014.083348 ETHIOPIA | 1998 | 267707325.538328 ETHIOPIA | 1997 | 450843904.565444 ETHIOPIA | 1996 | 448507582.14116 ETHIOPIA | 1995 | 452854342.029656 ETHIOPIA | 1994 | 454528291.838116 ETHIOPIA | 1993 | 454632479.235316 ETHIOPIA | 1992 | 454398992.096969 FRANCE | 1998 | 264407089.037293 FRANCE | 1997 | 456634712.831942 FRANCE | 1996 | 455684238.370891 FRANCE | 1995 | 456855007.294633 FRANCE | 1994 | 450100515.56747 FRANCE | 1993 | 452058566.3626 FRANCE | 1992 | 454263885.515698 GERMANY | 1998 | 275888019.501634 GERMANY | 1997 | 463499848.665563 GERMANY | 1996 | 471607448.996347 GERMANY | 1995 | 476262497.400392 GERMANY | 1994 | 476902768.460438 GERMANY | 1993 | 472842829.911033 GERMANY | 1992 | 473885018.684074 INDIA | 1998 | 275799886.942147 INDIA | 1997 | 478019023.523506 INDIA | 1996 | 471847915.605477 INDIA | 1995 | 470158099.015246 INDIA | 1994 | 478318741.662787 INDIA | 1993 | 478257928.252252 INDIA | 1992 | 474382887.989912 INDONESIA | 1998 | 264929374.468309 INDONESIA | 1997 | 453764021.990162 INDONESIA | 1996 | 454971838.620513 INDONESIA | 1995 | 449907543.334423 INDONESIA | 1994 | 460051813.503592 INDONESIA | 1993 | 455514963.180155 INDONESIA | 1992 | 449688398.754118 IRAN | 1998 | 266510115.078879 IRAN | 1997 | 465234829.570066 IRAN | 1996 | 463245549.167397 IRAN | 1995 | 461097724.927822 IRAN | 1994 | 463380036.32962 IRAN | 1993 | 464316951.068692 IRAN | 1992 | 461937544.188759 IRAQ | 1998 | 270874614.471412 IRAQ | 1997 | 462806999.713926 IRAQ | 1996 | 464985506.208328 IRAQ | 1995 | 461509085.93939 IRAQ | 1994 | 463373910.114507 IRAQ | 1993 | 467190962.915582 IRAQ | 1992 | 460183656.922234 JAPAN | 1998 | 267846089.555779 JAPAN | 1997 | 462890381.337235 JAPAN | 1996 | 462493756.183677 JAPAN | 1995 | 457591995.399785 JAPAN | 1994 | 458519022.255367 JAPAN | 1993 | 463650118.155646 JAPAN | 1992 | 459360892.955278 JORDAN | 1998 | 271784529.884124 JORDAN | 1997 | 459512015.015459 JORDAN | 1996 | 456078930.960239 JORDAN | 1995 | 458748116.244361 JORDAN | 1994 | 450983095.684949 JORDAN | 1993 | 463314016.29565 JORDAN | 1992 | 459503626.4346 KENYA | 1998 | 278625635.94555 KENYA | 1997 | 475446803.477496 KENYA | 1996 | 474832543.420978 KENYA | 1995 | 477188281.397199 KENYA | 1994 | 467373986.930295 KENYA | 1993 | 472409826.916857 KENYA | 1992 | 462069129.039987 MOROCCO | 1998 | 272687774.753349 MOROCCO | 1997 | 470467354.186778 MOROCCO | 1996 | 464455077.151196 MOROCCO | 1995 | 466644342.116387 MOROCCO | 1994 | 470023245.99622 MOROCCO | 1993 | 455288840.605655 MOROCCO | 1992 | 467458455.92861 MOZAMBIQUE | 1998 | 270862650.360974 MOZAMBIQUE | 1997 | 457210698.957893 MOZAMBIQUE | 1996 | 460885519.295604 MOZAMBIQUE | 1995 | 458562851.884062 MOZAMBIQUE | 1994 | 468019673.624367 MOZAMBIQUE | 1993 | 463850723.822573 MOZAMBIQUE | 1992 | 466044133.194571 PERU | 1998 | 272916907.090912 PERU | 1997 | 466734476.714147 PERU | 1996 | 461135942.954106 PERU | 1995 | 453198898.225829 PERU | 1994 | 465831696.39358 PERU | 1993 | 463608543.304405 PERU | 1992 | 472455002.394275 ROMANIA | 1998 | 271676249.855429 ROMANIA | 1997 | 460513362.763223 ROMANIA | 1996 | 470358062.756065 ROMANIA | 1995 | 469855515.075972 ROMANIA | 1994 | 465214287.249179 ROMANIA | 1993 | 456975333.075404 ROMANIA | 1992 | 467586930.696377 RUSSIA | 1998 | 276831698.361415 RUSSIA | 1997 | 478632915.324838 RUSSIA | 1996 | 477243907.021073 RUSSIA | 1995 | 467611586.735675 RUSSIA | 1994 | 467636868.498933 RUSSIA | 1993 | 475199091.437307 RUSSIA | 1992 | 469068088.056764 SAUDI ARABIA | 1998 | 279348061.760418 SAUDI ARABIA | 1997 | 464745565.989238 SAUDI ARABIA | 1996 | 465348593.935527 SAUDI ARABIA | 1995 | 478755165.017421 SAUDI ARABIA | 1994 | 471376926.88433 SAUDI ARABIA | 1993 | 461899675.879836 SAUDI ARABIA | 1992 | 469894903.877088 UNITED KINGDOM | 1998 | 271831829.404035 UNITED KINGDOM | 1997 | 463977142.467773 UNITED KINGDOM | 1996 | 466935693.921971 UNITED KINGDOM | 1995 | 458812541.110673 UNITED KINGDOM | 1994 | 459452839.165893 UNITED KINGDOM | 1993 | 468472828.393178 UNITED KINGDOM | 1992 | 461181753.191288 UNITED STATES | 1998 | 281207631.021676 UNITED STATES | 1997 | 471328703.384292 UNITED STATES | 1996 | 471048395.335469 UNITED STATES | 1995 | 470463434.062091 UNITED STATES | 1994 | 472465488.911915 UNITED STATES | 1993 | 460151612.364151 UNITED STATES | 1992 | 472002674.790791 VIETNAM | 1998 | 272363623.850436 VIETNAM | 1997 | 453962706.371756 VIETNAM | 1996 | 454899156.491992 VIETNAM | 1995 | 462616801.134085 VIETNAM | 1994 | 456096250.753046 VIETNAM | 1993 | 457730140.054559 VIETNAM | 1992 | 458859830.689314 (175 rows) COMMIT; COMMIT