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 '%purple%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 274346530.592207 ALGERIA | 1997 | 460255979.896241 ALGERIA | 1996 | 458925696.879267 ALGERIA | 1995 | 463623507.957162 ALGERIA | 1994 | 466727059.21982 ALGERIA | 1993 | 461247787.627584 ALGERIA | 1992 | 469968574.39292 ARGENTINA | 1998 | 274100525.72948 ARGENTINA | 1997 | 470488955.041344 ARGENTINA | 1996 | 474551951.340678 ARGENTINA | 1995 | 469664374.871959 ARGENTINA | 1994 | 464196168.139848 ARGENTINA | 1993 | 478955919.140653 ARGENTINA | 1992 | 476018681.921332 BRAZIL | 1998 | 273927446.854264 BRAZIL | 1997 | 471200822.472793 BRAZIL | 1996 | 466957102.135567 BRAZIL | 1995 | 469535504.957763 BRAZIL | 1994 | 473009817.337335 BRAZIL | 1993 | 467059809.362182 BRAZIL | 1992 | 468731149.978567 CANADA | 1998 | 280577000.412374 CANADA | 1997 | 475480558.66571 CANADA | 1996 | 482189387.173337 CANADA | 1995 | 473529355.32146 CANADA | 1994 | 477388257.513196 CANADA | 1993 | 472717772.424778 CANADA | 1992 | 476107363.601343 CHINA | 1998 | 274993908.134598 CHINA | 1997 | 461810602.960774 CHINA | 1996 | 475116910.166213 CHINA | 1995 | 472320306.561447 CHINA | 1994 | 463210685.897904 CHINA | 1993 | 472617052.507356 CHINA | 1992 | 471496199.21124 EGYPT | 1998 | 269865945.68575 EGYPT | 1997 | 458713732.15597 EGYPT | 1996 | 459761892.851067 EGYPT | 1995 | 464695710.856767 EGYPT | 1994 | 464784360.229164 EGYPT | 1993 | 462504286.969517 EGYPT | 1992 | 467968014.485348 ETHIOPIA | 1998 | 266753134.465804 ETHIOPIA | 1997 | 453733412.935933 ETHIOPIA | 1996 | 452000690.988576 ETHIOPIA | 1995 | 454922006.852334 ETHIOPIA | 1994 | 450078781.627368 ETHIOPIA | 1993 | 457956856.598336 ETHIOPIA | 1992 | 464433440.888252 FRANCE | 1998 | 265684189.097525 FRANCE | 1997 | 456583910.635227 FRANCE | 1996 | 461038022.26329 FRANCE | 1995 | 458376616.204202 FRANCE | 1994 | 454392071.65499 FRANCE | 1993 | 461922070.072583 FRANCE | 1992 | 458107501.097224 GERMANY | 1998 | 278113732.169669 GERMANY | 1997 | 484967876.239505 GERMANY | 1996 | 475857616.761011 GERMANY | 1995 | 477649185.58064 GERMANY | 1994 | 477277443.718327 GERMANY | 1993 | 470937470.133839 GERMANY | 1992 | 481551856.686729 INDIA | 1998 | 287328899.495107 INDIA | 1997 | 481595061.277341 INDIA | 1996 | 482310883.445515 INDIA | 1995 | 483389713.182856 INDIA | 1994 | 488160110.116566 INDIA | 1993 | 479466907.004414 INDIA | 1992 | 491686516.435328 INDONESIA | 1998 | 273526761.136861 INDONESIA | 1997 | 457359507.686038 INDONESIA | 1996 | 467243717.960182 INDONESIA | 1995 | 464208471.909689 INDONESIA | 1994 | 470469464.028071 INDONESIA | 1993 | 467386967.142046 INDONESIA | 1992 | 466544646.757901 IRAN | 1998 | 274983881.782149 IRAN | 1997 | 465579505.988123 IRAN | 1996 | 452585848.791658 IRAN | 1995 | 466723929.734318 IRAN | 1994 | 461670524.208641 IRAN | 1993 | 455557264.35292 IRAN | 1992 | 463217533.377821 IRAQ | 1998 | 281351009.967039 IRAQ | 1997 | 468460115.663389 IRAQ | 1996 | 477246924.989008 IRAQ | 1995 | 473110114.65642 IRAQ | 1994 | 466785651.971972 IRAQ | 1993 | 469613617.806271 IRAQ | 1992 | 478839731.455545 JAPAN | 1998 | 274702071.940194 JAPAN | 1997 | 462967415.404175 JAPAN | 1996 | 463557369.813904 JAPAN | 1995 | 465710804.301802 JAPAN | 1994 | 461656579.212369 JAPAN | 1993 | 465946150.266811 JAPAN | 1992 | 468244694.211651 JORDAN | 1998 | 273508290.207773 JORDAN | 1997 | 463364377.688271 JORDAN | 1996 | 468070511.100215 JORDAN | 1995 | 459018865.017723 JORDAN | 1994 | 466430739.836326 JORDAN | 1993 | 472424877.819548 JORDAN | 1992 | 467440918.304686 KENYA | 1998 | 280221992.599936 KENYA | 1997 | 475464965.011904 KENYA | 1996 | 473970292.276697 KENYA | 1995 | 476879195.284131 KENYA | 1994 | 472133948.76548 KENYA | 1993 | 477979742.31627 KENYA | 1992 | 478570861.63403 MOROCCO | 1998 | 278335213.26349 MOROCCO | 1997 | 477713023.428382 MOROCCO | 1996 | 480932170.15848 MOROCCO | 1995 | 483757192.112209 MOROCCO | 1994 | 483078335.12238 MOROCCO | 1993 | 475167133.189869 MOROCCO | 1992 | 480190602.029475 MOZAMBIQUE | 1998 | 263248533.359233 MOZAMBIQUE | 1997 | 454941321.562108 MOZAMBIQUE | 1996 | 460865765.289159 MOZAMBIQUE | 1995 | 458553646.37316 MOZAMBIQUE | 1994 | 453379198.172566 MOZAMBIQUE | 1993 | 459266459.950578 MOZAMBIQUE | 1992 | 454070681.741819 PERU | 1998 | 278473723.852208 PERU | 1997 | 456838294.703066 PERU | 1996 | 469064750.826038 PERU | 1995 | 459392465.844704 PERU | 1994 | 460535372.462631 PERU | 1993 | 462585455.229049 PERU | 1992 | 468220496.893885 ROMANIA | 1998 | 276884753.812429 ROMANIA | 1997 | 472071704.458805 ROMANIA | 1996 | 474243265.096206 ROMANIA | 1995 | 473134644.093544 ROMANIA | 1994 | 471393823.733945 ROMANIA | 1993 | 472533549.164392 ROMANIA | 1992 | 472830176.169858 RUSSIA | 1998 | 280305930.109182 RUSSIA | 1997 | 466991302.788456 RUSSIA | 1996 | 473735122.186312 RUSSIA | 1995 | 473663188.60954 RUSSIA | 1994 | 473193827.085351 RUSSIA | 1993 | 472224191.670245 RUSSIA | 1992 | 470599287.021494 SAUDI ARABIA | 1998 | 276665863.430856 SAUDI ARABIA | 1997 | 469513433.315993 SAUDI ARABIA | 1996 | 468012039.996267 SAUDI ARABIA | 1995 | 466295770.046134 SAUDI ARABIA | 1994 | 468941195.298273 SAUDI ARABIA | 1993 | 474051435.881732 SAUDI ARABIA | 1992 | 470720262.091697 UNITED KINGDOM | 1998 | 269580368.723623 UNITED KINGDOM | 1997 | 467850126.208949 UNITED KINGDOM | 1996 | 468002273.085739 UNITED KINGDOM | 1995 | 466367909.307409 UNITED KINGDOM | 1994 | 459776765.121371 UNITED KINGDOM | 1993 | 468848593.171349 UNITED KINGDOM | 1992 | 464674473.19233 UNITED STATES | 1998 | 272971558.923518 UNITED STATES | 1997 | 472355574.026355 UNITED STATES | 1996 | 474112036.44723 UNITED STATES | 1995 | 464603245.77949 UNITED STATES | 1994 | 477030633.777783 UNITED STATES | 1993 | 469001592.818236 UNITED STATES | 1992 | 471147969.9879 VIETNAM | 1998 | 279810692.515008 VIETNAM | 1997 | 467334446.689745 VIETNAM | 1996 | 471948043.817728 VIETNAM | 1995 | 471406240.660061 VIETNAM | 1994 | 474107211.369222 VIETNAM | 1993 | 477509338.691478 VIETNAM | 1992 | 475198000.365241 (175 rows) COMMIT; COMMIT