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 '%deep%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272504266.675478 ALGERIA | 1997 | 457335807.996531 ALGERIA | 1996 | 463854385.344928 ALGERIA | 1995 | 453472649.943012 ALGERIA | 1994 | 453358886.668238 ALGERIA | 1993 | 453114521.515239 ALGERIA | 1992 | 460933795.391387 ARGENTINA | 1998 | 284660965.066312 ARGENTINA | 1997 | 480060288.277859 ARGENTINA | 1996 | 477144084.143061 ARGENTINA | 1995 | 480106924.616812 ARGENTINA | 1994 | 476442583.74486 ARGENTINA | 1993 | 483269068.272787 ARGENTINA | 1992 | 485060052.796431 BRAZIL | 1998 | 277829942.358503 BRAZIL | 1997 | 454634629.632735 BRAZIL | 1996 | 462777518.194517 BRAZIL | 1995 | 465613609.158468 BRAZIL | 1994 | 459980645.010666 BRAZIL | 1993 | 461593142.054404 BRAZIL | 1992 | 462784081.592528 CANADA | 1998 | 272695786.80262 CANADA | 1997 | 469732107.836599 CANADA | 1996 | 478684628.785972 CANADA | 1995 | 465868062.182121 CANADA | 1994 | 475419349.393198 CANADA | 1993 | 469241861.009163 CANADA | 1992 | 469081642.766907 CHINA | 1998 | 274433388.544266 CHINA | 1997 | 459403378.330877 CHINA | 1996 | 467512270.88746 CHINA | 1995 | 459835648.451648 CHINA | 1994 | 465477863.982635 CHINA | 1993 | 462052873.241715 CHINA | 1992 | 469435315.839842 EGYPT | 1998 | 270367437.19569 EGYPT | 1997 | 459647469.733407 EGYPT | 1996 | 466027420.745233 EGYPT | 1995 | 463378657.473484 EGYPT | 1994 | 464713668.638754 EGYPT | 1993 | 467209692.377551 EGYPT | 1992 | 461094673.135003 ETHIOPIA | 1998 | 260521937.502244 ETHIOPIA | 1997 | 451758094.31228 ETHIOPIA | 1996 | 449201500.64454 ETHIOPIA | 1995 | 449664087.809531 ETHIOPIA | 1994 | 447903744.53449 ETHIOPIA | 1993 | 453912969.330515 ETHIOPIA | 1992 | 463216082.406324 FRANCE | 1998 | 264610202.195067 FRANCE | 1997 | 456281343.053506 FRANCE | 1996 | 455529691.472465 FRANCE | 1995 | 457032399.748203 FRANCE | 1994 | 455150106.533927 FRANCE | 1993 | 460939011.685047 FRANCE | 1992 | 448709623.407358 GERMANY | 1998 | 274246613.727116 GERMANY | 1997 | 486079826.487086 GERMANY | 1996 | 477026595.375984 GERMANY | 1995 | 487623489.515394 GERMANY | 1994 | 476491203.688988 GERMANY | 1993 | 478594629.664733 GERMANY | 1992 | 475459598.195648 INDIA | 1998 | 277226670.824202 INDIA | 1997 | 479402085.995502 INDIA | 1996 | 474898761.165599 INDIA | 1995 | 476827250.845511 INDIA | 1994 | 465068812.372678 INDIA | 1993 | 482106974.872288 INDIA | 1992 | 474098273.570639 INDONESIA | 1998 | 271818532.274898 INDONESIA | 1997 | 462459306.989712 INDONESIA | 1996 | 462932131.353551 INDONESIA | 1995 | 454213006.796504 INDONESIA | 1994 | 457855384.523201 INDONESIA | 1993 | 459571247.018219 INDONESIA | 1992 | 457874426.048224 IRAN | 1998 | 264850186.521334 IRAN | 1997 | 460860443.474368 IRAN | 1996 | 466301685.261032 IRAN | 1995 | 470209167.09577 IRAN | 1994 | 468131923.140313 IRAN | 1993 | 466233692.278124 IRAN | 1992 | 466915748.004803 IRAQ | 1998 | 271747238.462298 IRAQ | 1997 | 464068247.997693 IRAQ | 1996 | 469767970.74735 IRAQ | 1995 | 467329904.922977 IRAQ | 1994 | 467926399.922757 IRAQ | 1993 | 468776703.772927 IRAQ | 1992 | 472127284.023057 JAPAN | 1998 | 269799244.922843 JAPAN | 1997 | 465651623.135834 JAPAN | 1996 | 457925296.441356 JAPAN | 1995 | 458778258.935626 JAPAN | 1994 | 468861800.026135 JAPAN | 1993 | 467566860.02237 JAPAN | 1992 | 463814343.775672 JORDAN | 1998 | 269754621.99518 JORDAN | 1997 | 461739955.594036 JORDAN | 1996 | 454908179.192875 JORDAN | 1995 | 463644878.112383 JORDAN | 1994 | 462900692.584603 JORDAN | 1993 | 460999999.664048 JORDAN | 1992 | 458753466.48453 KENYA | 1998 | 275436057.213673 KENYA | 1997 | 466739636.917949 KENYA | 1996 | 469535594.496191 KENYA | 1995 | 463766340.42466 KENYA | 1994 | 469223769.875619 KENYA | 1993 | 474100558.493528 KENYA | 1992 | 476213392.458108 MOROCCO | 1998 | 275552343.350449 MOROCCO | 1997 | 469182770.090158 MOROCCO | 1996 | 463219436.139352 MOROCCO | 1995 | 464486220.374523 MOROCCO | 1994 | 464096120.533885 MOROCCO | 1993 | 460935057.633087 MOROCCO | 1992 | 463022106.470391 MOZAMBIQUE | 1998 | 274847864.150346 MOZAMBIQUE | 1997 | 463889388.757513 MOZAMBIQUE | 1996 | 468713432.599907 MOZAMBIQUE | 1995 | 469076601.988326 MOZAMBIQUE | 1994 | 471983618.806956 MOZAMBIQUE | 1993 | 458179307.565181 MOZAMBIQUE | 1992 | 467526078.060924 PERU | 1998 | 270244500.062158 PERU | 1997 | 460105065.149528 PERU | 1996 | 466835262.81606 PERU | 1995 | 459308511.628024 PERU | 1994 | 456265600.117667 PERU | 1993 | 457468425.437692 PERU | 1992 | 462486516.076787 ROMANIA | 1998 | 268952014.615868 ROMANIA | 1997 | 464307988.335976 ROMANIA | 1996 | 464923379.323059 ROMANIA | 1995 | 454851415.74743 ROMANIA | 1994 | 462707286.89295 ROMANIA | 1993 | 460281804.427636 ROMANIA | 1992 | 464633434.663914 RUSSIA | 1998 | 276546790.013165 RUSSIA | 1997 | 474465075.4258 RUSSIA | 1996 | 473372253.644357 RUSSIA | 1995 | 473494590.211641 RUSSIA | 1994 | 465910079.452567 RUSSIA | 1993 | 472012024.851418 RUSSIA | 1992 | 474369844.73326 SAUDI ARABIA | 1998 | 274890198.712914 SAUDI ARABIA | 1997 | 473108165.273238 SAUDI ARABIA | 1996 | 475927842.651561 SAUDI ARABIA | 1995 | 471132314.580509 SAUDI ARABIA | 1994 | 468701959.590109 SAUDI ARABIA | 1993 | 469315846.865858 SAUDI ARABIA | 1992 | 467477929.405996 UNITED KINGDOM | 1998 | 271857283.935994 UNITED KINGDOM | 1997 | 462960375.750373 UNITED KINGDOM | 1996 | 462995343.689223 UNITED KINGDOM | 1995 | 469078997.039963 UNITED KINGDOM | 1994 | 464697508.377015 UNITED KINGDOM | 1993 | 467398441.680393 UNITED KINGDOM | 1992 | 463623247.345343 UNITED STATES | 1998 | 276683996.018189 UNITED STATES | 1997 | 471838219.29708 UNITED STATES | 1996 | 475810057.657031 UNITED STATES | 1995 | 467613798.859715 UNITED STATES | 1994 | 470218056.664156 UNITED STATES | 1993 | 465314166.92137 UNITED STATES | 1992 | 463807982.540244 VIETNAM | 1998 | 278543494.099544 VIETNAM | 1997 | 469419544.722092 VIETNAM | 1996 | 473423761.690283 VIETNAM | 1995 | 464060224.303666 VIETNAM | 1994 | 472247779.689124 VIETNAM | 1993 | 469077723.99058 VIETNAM | 1992 | 470852734.277287 (175 rows) COMMIT; COMMIT