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 '%tomato%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261984760.335577 ALGERIA | 1997 | 453600993.928298 ALGERIA | 1996 | 456611890.354013 ALGERIA | 1995 | 461644164.262535 ALGERIA | 1994 | 453654883.899066 ALGERIA | 1993 | 457754917.232633 ALGERIA | 1992 | 465020280.265774 ARGENTINA | 1998 | 282488626.577616 ARGENTINA | 1997 | 470268765.359449 ARGENTINA | 1996 | 468820368.715636 ARGENTINA | 1995 | 465044935.759569 ARGENTINA | 1994 | 471815697.234844 ARGENTINA | 1993 | 465720643.065832 ARGENTINA | 1992 | 472023125.358237 BRAZIL | 1998 | 283521101.280094 BRAZIL | 1997 | 474869672.936754 BRAZIL | 1996 | 469174125.074477 BRAZIL | 1995 | 476334007.013498 BRAZIL | 1994 | 469144804.482146 BRAZIL | 1993 | 478859052.750377 BRAZIL | 1992 | 472835768.384951 CANADA | 1998 | 277745934.112454 CANADA | 1997 | 477007020.48736 CANADA | 1996 | 485708137.247079 CANADA | 1995 | 480355961.595576 CANADA | 1994 | 483610508.070869 CANADA | 1993 | 484921095.724331 CANADA | 1992 | 481538584.138632 CHINA | 1998 | 277374420.889748 CHINA | 1997 | 475493309.496885 CHINA | 1996 | 473404894.028082 CHINA | 1995 | 478381712.565045 CHINA | 1994 | 469315748.925956 CHINA | 1993 | 479855187.119654 CHINA | 1992 | 485605694.734112 EGYPT | 1998 | 274423019.519209 EGYPT | 1997 | 466460308.212483 EGYPT | 1996 | 476930535.946561 EGYPT | 1995 | 461695063.609827 EGYPT | 1994 | 461880277.9067 EGYPT | 1993 | 463972178.63083 EGYPT | 1992 | 461531232.001926 ETHIOPIA | 1998 | 275296075.670075 ETHIOPIA | 1997 | 463141281.698222 ETHIOPIA | 1996 | 464880896.128724 ETHIOPIA | 1995 | 458496270.172398 ETHIOPIA | 1994 | 473611373.528997 ETHIOPIA | 1993 | 472985433.406204 ETHIOPIA | 1992 | 465996492.004482 FRANCE | 1998 | 267334295.062213 FRANCE | 1997 | 457566730.50786 FRANCE | 1996 | 461374280.464195 FRANCE | 1995 | 465298780.407673 FRANCE | 1994 | 472038156.939588 FRANCE | 1993 | 453602075.610271 FRANCE | 1992 | 462670088.122298 GERMANY | 1998 | 273272364.542125 GERMANY | 1997 | 478583667.588498 GERMANY | 1996 | 469047261.032038 GERMANY | 1995 | 467191472.45039 GERMANY | 1994 | 470841156.108389 GERMANY | 1993 | 464229272.416867 GERMANY | 1992 | 470385377.472562 INDIA | 1998 | 282171159.476415 INDIA | 1997 | 482054536.083102 INDIA | 1996 | 485690756.823161 INDIA | 1995 | 490659805.031188 INDIA | 1994 | 492945385.641708 INDIA | 1993 | 490546781.725238 INDIA | 1992 | 480062356.296802 INDONESIA | 1998 | 270156042.399672 INDONESIA | 1997 | 474052054.051467 INDONESIA | 1996 | 472457146.10288 INDONESIA | 1995 | 468447416.936219 INDONESIA | 1994 | 476592245.881546 INDONESIA | 1993 | 464166207.050802 INDONESIA | 1992 | 459578727.25515 IRAN | 1998 | 273941308.283438 IRAN | 1997 | 471577614.376323 IRAN | 1996 | 469764819.789037 IRAN | 1995 | 472372348.117609 IRAN | 1994 | 469819866.507116 IRAN | 1993 | 467466504.358639 IRAN | 1992 | 469499416.498061 IRAQ | 1998 | 277667688.682571 IRAQ | 1997 | 471664916.119528 IRAQ | 1996 | 473290216.476926 IRAQ | 1995 | 470533312.527545 IRAQ | 1994 | 472241905.865242 IRAQ | 1993 | 479540832.983297 IRAQ | 1992 | 476311132.11103 JAPAN | 1998 | 265642759.997298 JAPAN | 1997 | 466901699.784865 JAPAN | 1996 | 475401724.932125 JAPAN | 1995 | 465966726.761854 JAPAN | 1994 | 475820548.701007 JAPAN | 1993 | 467130189.880286 JAPAN | 1992 | 467673409.852794 JORDAN | 1998 | 269857636.265883 JORDAN | 1997 | 466556351.782878 JORDAN | 1996 | 465570159.857839 JORDAN | 1995 | 462692458.481675 JORDAN | 1994 | 468161879.854834 JORDAN | 1993 | 468741109.581528 JORDAN | 1992 | 466145962.342992 KENYA | 1998 | 281218765.878591 KENYA | 1997 | 479784996.885583 KENYA | 1996 | 472999780.785185 KENYA | 1995 | 477508883.881798 KENYA | 1994 | 485691656.486117 KENYA | 1993 | 474062570.376553 KENYA | 1992 | 472633049.951645 MOROCCO | 1998 | 273594920.328133 MOROCCO | 1997 | 476927833.820373 MOROCCO | 1996 | 472402611.894213 MOROCCO | 1995 | 473480363.113058 MOROCCO | 1994 | 480322025.824782 MOROCCO | 1993 | 463626281.64025 MOROCCO | 1992 | 479273279.368224 MOZAMBIQUE | 1998 | 267690017.497021 MOZAMBIQUE | 1997 | 452788015.612943 MOZAMBIQUE | 1996 | 459651219.678404 MOZAMBIQUE | 1995 | 458147300.728563 MOZAMBIQUE | 1994 | 457790244.561359 MOZAMBIQUE | 1993 | 460035799.171741 MOZAMBIQUE | 1992 | 458796018.633712 PERU | 1998 | 266317554.343029 PERU | 1997 | 470822749.6855 PERU | 1996 | 471136336.742889 PERU | 1995 | 470041438.320057 PERU | 1994 | 464000828.388015 PERU | 1993 | 469985010.803523 PERU | 1992 | 464447423.841528 ROMANIA | 1998 | 278288648.773069 ROMANIA | 1997 | 472070165.166423 ROMANIA | 1996 | 483557339.638152 ROMANIA | 1995 | 477048517.164383 ROMANIA | 1994 | 465885505.582208 ROMANIA | 1993 | 475681144.029945 ROMANIA | 1992 | 470656740.727973 RUSSIA | 1998 | 279473904.925513 RUSSIA | 1997 | 480225978.005303 RUSSIA | 1996 | 476470520.873424 RUSSIA | 1995 | 475540907.587832 RUSSIA | 1994 | 471547785.535709 RUSSIA | 1993 | 476535956.840611 RUSSIA | 1992 | 479376679.604195 SAUDI ARABIA | 1998 | 273509966.990266 SAUDI ARABIA | 1997 | 467463760.099087 SAUDI ARABIA | 1996 | 475285824.007539 SAUDI ARABIA | 1995 | 476776351.295844 SAUDI ARABIA | 1994 | 476655734.386499 SAUDI ARABIA | 1993 | 480739383.244072 SAUDI ARABIA | 1992 | 476450459.148765 UNITED KINGDOM | 1998 | 271549980.333224 UNITED KINGDOM | 1997 | 466852699.154415 UNITED KINGDOM | 1996 | 463537059.443443 UNITED KINGDOM | 1995 | 463594437.23634 UNITED KINGDOM | 1994 | 460567201.255616 UNITED KINGDOM | 1993 | 466263697.381105 UNITED KINGDOM | 1992 | 456543008.9308 UNITED STATES | 1998 | 280133600.022026 UNITED STATES | 1997 | 476749655.09923 UNITED STATES | 1996 | 474250908.381718 UNITED STATES | 1995 | 475338504.442073 UNITED STATES | 1994 | 477430933.555454 UNITED STATES | 1993 | 472398144.738955 UNITED STATES | 1992 | 476786566.783321 VIETNAM | 1998 | 275744559.187094 VIETNAM | 1997 | 464125731.453539 VIETNAM | 1996 | 468375002.850192 VIETNAM | 1995 | 465957611.48472 VIETNAM | 1994 | 470012384.133272 VIETNAM | 1993 | 471898274.5752 VIETNAM | 1992 | 459897668.296826 (175 rows) COMMIT; COMMIT