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 '%turquoise%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 260626528.696222 ALGERIA | 1997 | 456748859.150707 ALGERIA | 1996 | 452761122.611228 ALGERIA | 1995 | 445846726.674765 ALGERIA | 1994 | 453162887.96692 ALGERIA | 1993 | 442501462.102999 ALGERIA | 1992 | 448672637.73183 ARGENTINA | 1998 | 267210568.673486 ARGENTINA | 1997 | 459673964.810327 ARGENTINA | 1996 | 469769417.075994 ARGENTINA | 1995 | 474370509.891871 ARGENTINA | 1994 | 467697644.937324 ARGENTINA | 1993 | 461180375.608789 ARGENTINA | 1992 | 464981545.417135 BRAZIL | 1998 | 266393013.317432 BRAZIL | 1997 | 464247772.729343 BRAZIL | 1996 | 466021230.763819 BRAZIL | 1995 | 460447315.079704 BRAZIL | 1994 | 461844874.583899 BRAZIL | 1993 | 455694731.596196 BRAZIL | 1992 | 466768106.989871 CANADA | 1998 | 278783979.45185 CANADA | 1997 | 481729845.141934 CANADA | 1996 | 486061192.568339 CANADA | 1995 | 480630849.044819 CANADA | 1994 | 484721602.296975 CANADA | 1993 | 486658069.788589 CANADA | 1992 | 489362523.817173 CHINA | 1998 | 269752113.523882 CHINA | 1997 | 444332619.851054 CHINA | 1996 | 451350270.195136 CHINA | 1995 | 448532108.53343 CHINA | 1994 | 445141524.19467 CHINA | 1993 | 454710733.222611 CHINA | 1992 | 450995913.144173 EGYPT | 1998 | 269273893.45214 EGYPT | 1997 | 466468585.776209 EGYPT | 1996 | 469510054.848281 EGYPT | 1995 | 462735503.031132 EGYPT | 1994 | 458004390.990383 EGYPT | 1993 | 470114234.491858 EGYPT | 1992 | 464414900.379245 ETHIOPIA | 1998 | 264195768.922553 ETHIOPIA | 1997 | 452294608.960848 ETHIOPIA | 1996 | 456371121.989079 ETHIOPIA | 1995 | 456698947.424004 ETHIOPIA | 1994 | 453555957.188689 ETHIOPIA | 1993 | 455021436.653297 ETHIOPIA | 1992 | 460448705.05335 FRANCE | 1998 | 268008453.093791 FRANCE | 1997 | 459291971.874823 FRANCE | 1996 | 464873967.994768 FRANCE | 1995 | 461330086.602764 FRANCE | 1994 | 456997457.47322 FRANCE | 1993 | 461944521.100149 FRANCE | 1992 | 456841830.921866 GERMANY | 1998 | 276877855.402394 GERMANY | 1997 | 474878710.808808 GERMANY | 1996 | 478653280.478353 GERMANY | 1995 | 476652817.711633 GERMANY | 1994 | 473697980.748363 GERMANY | 1993 | 471469218.169241 GERMANY | 1992 | 470166427.325476 INDIA | 1998 | 270166099.924565 INDIA | 1997 | 471900356.645646 INDIA | 1996 | 473797046.586881 INDIA | 1995 | 468729987.562045 INDIA | 1994 | 464518680.624721 INDIA | 1993 | 463393891.339952 INDIA | 1992 | 466420411.988042 INDONESIA | 1998 | 270963913.679216 INDONESIA | 1997 | 458376200.088096 INDONESIA | 1996 | 462253959.066018 INDONESIA | 1995 | 463786490.881158 INDONESIA | 1994 | 461473559.540989 INDONESIA | 1993 | 463856874.603431 INDONESIA | 1992 | 465173570.125695 IRAN | 1998 | 270197783.56266 IRAN | 1997 | 459925998.081485 IRAN | 1996 | 464516293.499862 IRAN | 1995 | 461278854.818866 IRAN | 1994 | 464949086.614022 IRAN | 1993 | 466876136.268958 IRAN | 1992 | 458370224.721285 IRAQ | 1998 | 280247884.26907 IRAQ | 1997 | 477093479.374264 IRAQ | 1996 | 476024822.342643 IRAQ | 1995 | 483769373.954941 IRAQ | 1994 | 483304526.825841 IRAQ | 1993 | 476583664.232896 IRAQ | 1992 | 480255273.568257 JAPAN | 1998 | 270887438.799889 JAPAN | 1997 | 463667321.844362 JAPAN | 1996 | 466652839.452397 JAPAN | 1995 | 465378971.236425 JAPAN | 1994 | 460424506.567726 JAPAN | 1993 | 454571365.232844 JAPAN | 1992 | 456356945.116904 JORDAN | 1998 | 267412207.15816 JORDAN | 1997 | 447367254.909312 JORDAN | 1996 | 452333149.700142 JORDAN | 1995 | 452205021.824207 JORDAN | 1994 | 458011681.715226 JORDAN | 1993 | 456122014.575746 JORDAN | 1992 | 456665430.234464 KENYA | 1998 | 272428085.341714 KENYA | 1997 | 471565712.626745 KENYA | 1996 | 470370178.707268 KENYA | 1995 | 463144372.41538 KENYA | 1994 | 471286474.039147 KENYA | 1993 | 470940894.385226 KENYA | 1992 | 467271219.375095 MOROCCO | 1998 | 269976711.994412 MOROCCO | 1997 | 466812966.77554 MOROCCO | 1996 | 471732319.951973 MOROCCO | 1995 | 467666677.697013 MOROCCO | 1994 | 463980232.64218 MOROCCO | 1993 | 459774442.384319 MOROCCO | 1992 | 470623939.247298 MOZAMBIQUE | 1998 | 276229470.465517 MOZAMBIQUE | 1997 | 462875937.666882 MOZAMBIQUE | 1996 | 462184171.299322 MOZAMBIQUE | 1995 | 465327439.386435 MOZAMBIQUE | 1994 | 466367677.478287 MOZAMBIQUE | 1993 | 468473313.912649 MOZAMBIQUE | 1992 | 457982761.37294 PERU | 1998 | 272304444.217933 PERU | 1997 | 462289191.43438 PERU | 1996 | 456939937.008106 PERU | 1995 | 463373062.730433 PERU | 1994 | 465410272.345114 PERU | 1993 | 466664430.566591 PERU | 1992 | 459141766.109683 ROMANIA | 1998 | 271866793.407049 ROMANIA | 1997 | 458953716.130696 ROMANIA | 1996 | 455433624.530423 ROMANIA | 1995 | 465171671.258512 ROMANIA | 1994 | 467230119.401734 ROMANIA | 1993 | 457800239.905631 ROMANIA | 1992 | 468964113.271289 RUSSIA | 1998 | 279494278.558132 RUSSIA | 1997 | 475856505.967896 RUSSIA | 1996 | 471164860.141166 RUSSIA | 1995 | 470368452.813758 RUSSIA | 1994 | 469806798.306689 RUSSIA | 1993 | 473195741.098058 RUSSIA | 1992 | 472801145.682996 SAUDI ARABIA | 1998 | 271397364.226349 SAUDI ARABIA | 1997 | 466565001.140741 SAUDI ARABIA | 1996 | 468785317.02841 SAUDI ARABIA | 1995 | 467307405.471055 SAUDI ARABIA | 1994 | 469519853.495194 SAUDI ARABIA | 1993 | 465534417.673922 SAUDI ARABIA | 1992 | 468168045.983702 UNITED KINGDOM | 1998 | 268934192.063279 UNITED KINGDOM | 1997 | 456055802.457382 UNITED KINGDOM | 1996 | 456204242.863153 UNITED KINGDOM | 1995 | 458416201.233894 UNITED KINGDOM | 1994 | 453453502.820745 UNITED KINGDOM | 1993 | 458116541.210249 UNITED KINGDOM | 1992 | 460963996.583233 UNITED STATES | 1998 | 282525443.551556 UNITED STATES | 1997 | 473181497.302739 UNITED STATES | 1996 | 482413635.327591 UNITED STATES | 1995 | 476815275.146135 UNITED STATES | 1994 | 473599351.150557 UNITED STATES | 1993 | 475103385.982744 UNITED STATES | 1992 | 488263023.806585 VIETNAM | 1998 | 271282636.056374 VIETNAM | 1997 | 464350302.752818 VIETNAM | 1996 | 476057353.646809 VIETNAM | 1995 | 466764270.610528 VIETNAM | 1994 | 466949797.193379 VIETNAM | 1993 | 473884012.643378 VIETNAM | 1992 | 464946184.415281 (175 rows) COMMIT; COMMIT