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 | 261610591.087803 ALGERIA | 1997 | 453353374.713331 ALGERIA | 1996 | 456195085.767088 ALGERIA | 1995 | 460627782.690297 ALGERIA | 1994 | 453069944.04035 ALGERIA | 1993 | 457058652.033558 ALGERIA | 1992 | 464536745.501597 ARGENTINA | 1998 | 282283038.741523 ARGENTINA | 1997 | 469521558.226801 ARGENTINA | 1996 | 468751745.687471 ARGENTINA | 1995 | 464413133.55565 ARGENTINA | 1994 | 471411905.135057 ARGENTINA | 1993 | 465378648.62976 ARGENTINA | 1992 | 471725249.607933 BRAZIL | 1998 | 283048954.683611 BRAZIL | 1997 | 474291868.44904 BRAZIL | 1996 | 469011825.369363 BRAZIL | 1995 | 475890522.086929 BRAZIL | 1994 | 468532530.260841 BRAZIL | 1993 | 477952513.040995 BRAZIL | 1992 | 472283560.090272 CANADA | 1998 | 277643831.831056 CANADA | 1997 | 476491068.261962 CANADA | 1996 | 485579221.383599 CANADA | 1995 | 480036732.244722 CANADA | 1994 | 483204394.712316 CANADA | 1993 | 484380770.872259 CANADA | 1992 | 480701779.647132 CHINA | 1998 | 277094431.244527 CHINA | 1997 | 475380625.173202 CHINA | 1996 | 472789495.820597 CHINA | 1995 | 478080860.690507 CHINA | 1994 | 468949740.353002 CHINA | 1993 | 478751332.122841 CHINA | 1992 | 484983055.42287 EGYPT | 1998 | 274344107.764568 EGYPT | 1997 | 465883801.928409 EGYPT | 1996 | 476311894.467359 EGYPT | 1995 | 461233415.775496 EGYPT | 1994 | 461758063.844808 EGYPT | 1993 | 463361547.054814 EGYPT | 1992 | 461167934.871291 ETHIOPIA | 1998 | 275179434.773086 ETHIOPIA | 1997 | 462488811.943046 ETHIOPIA | 1996 | 464418593.324653 ETHIOPIA | 1995 | 457762423.202143 ETHIOPIA | 1994 | 472733638.807809 ETHIOPIA | 1993 | 472716138.362895 ETHIOPIA | 1992 | 465777075.997699 FRANCE | 1998 | 266950507.521228 FRANCE | 1997 | 457094358.856127 FRANCE | 1996 | 461323154.346874 FRANCE | 1995 | 464740470.840582 FRANCE | 1994 | 471998920.099915 FRANCE | 1993 | 453139796.340181 FRANCE | 1992 | 462398471.961717 GERMANY | 1998 | 273034726.545026 GERMANY | 1997 | 478193199.647012 GERMANY | 1996 | 468595335.323338 GERMANY | 1995 | 466610184.411424 GERMANY | 1994 | 470555177.973176 GERMANY | 1993 | 463952007.353094 GERMANY | 1992 | 469857545.343844 INDIA | 1998 | 281578828.17782 INDIA | 1997 | 481779274.009321 INDIA | 1996 | 484789872.799955 INDIA | 1995 | 489992792.593692 INDIA | 1994 | 492639217.610755 INDIA | 1993 | 489924170.299145 INDIA | 1992 | 479453777.250555 INDONESIA | 1998 | 269699851.273223 INDONESIA | 1997 | 473727597.701606 INDONESIA | 1996 | 471880452.8071 INDONESIA | 1995 | 468229409.791934 INDONESIA | 1994 | 476294482.444179 INDONESIA | 1993 | 463616857.164013 INDONESIA | 1992 | 459203188.044915 IRAN | 1998 | 273581491.218262 IRAN | 1997 | 471620227.822023 IRAN | 1996 | 469143746.306836 IRAN | 1995 | 471726336.654956 IRAN | 1994 | 469100753.417075 IRAN | 1993 | 466620605.034789 IRAN | 1992 | 469329207.591913 IRAQ | 1998 | 277386918.223198 IRAQ | 1997 | 471478916.560425 IRAQ | 1996 | 472746146.03853 IRAQ | 1995 | 469792023.835252 IRAQ | 1994 | 471614872.664637 IRAQ | 1993 | 479014480.949415 IRAQ | 1992 | 475703635.642259 JAPAN | 1998 | 265366065.792776 JAPAN | 1997 | 466540692.196073 JAPAN | 1996 | 474956685.313042 JAPAN | 1995 | 465247064.831179 JAPAN | 1994 | 475281477.064198 JAPAN | 1993 | 466808210.065539 JAPAN | 1992 | 467192417.42011 JORDAN | 1998 | 269679843.531466 JORDAN | 1997 | 465918872.55314 JORDAN | 1996 | 465152373.36251 JORDAN | 1995 | 462119476.613705 JORDAN | 1994 | 467786709.134434 JORDAN | 1993 | 468330008.969958 JORDAN | 1992 | 465827725.861222 KENYA | 1998 | 280675747.583454 KENYA | 1997 | 479794100.93918 KENYA | 1996 | 472775675.506418 KENYA | 1995 | 476781823.340983 KENYA | 1994 | 485375437.271002 KENYA | 1993 | 473589441.809795 KENYA | 1992 | 472397188.789267 MOROCCO | 1998 | 273143787.565472 MOROCCO | 1997 | 476351962.52125 MOROCCO | 1996 | 471735461.583932 MOROCCO | 1995 | 472923994.555521 MOROCCO | 1994 | 480062879.612073 MOROCCO | 1993 | 463144115.817551 MOROCCO | 1992 | 478377383.030398 MOZAMBIQUE | 1998 | 267420326.591589 MOZAMBIQUE | 1997 | 452530785.958149 MOZAMBIQUE | 1996 | 459086634.884211 MOZAMBIQUE | 1995 | 457688947.340216 MOZAMBIQUE | 1994 | 457184523.508472 MOZAMBIQUE | 1993 | 459482622.994616 MOZAMBIQUE | 1992 | 458690452.120975 PERU | 1998 | 266173056.996278 PERU | 1997 | 470076207.991342 PERU | 1996 | 470946722.200059 PERU | 1995 | 469619954.839147 PERU | 1994 | 463279723.978286 PERU | 1993 | 469202109.134354 PERU | 1992 | 464261997.584952 ROMANIA | 1998 | 277816932.767713 ROMANIA | 1997 | 471435928.385113 ROMANIA | 1996 | 482984510.504012 ROMANIA | 1995 | 476209355.990655 ROMANIA | 1994 | 465064264.273468 ROMANIA | 1993 | 475315634.698743 ROMANIA | 1992 | 470354673.414359 RUSSIA | 1998 | 279155156.216966 RUSSIA | 1997 | 480115774.785545 RUSSIA | 1996 | 475742435.41306 RUSSIA | 1995 | 475028893.645129 RUSSIA | 1994 | 471437102.637531 RUSSIA | 1993 | 475875942.746452 RUSSIA | 1992 | 478988912.086527 SAUDI ARABIA | 1998 | 273492309.254886 SAUDI ARABIA | 1997 | 467429937.072198 SAUDI ARABIA | 1996 | 474902141.72602 SAUDI ARABIA | 1995 | 476322759.382306 SAUDI ARABIA | 1994 | 476260608.150367 SAUDI ARABIA | 1993 | 480547903.995595 SAUDI ARABIA | 1992 | 475953470.909499 UNITED KINGDOM | 1998 | 271371561.607759 UNITED KINGDOM | 1997 | 466458832.95156 UNITED KINGDOM | 1996 | 463140580.067019 UNITED KINGDOM | 1995 | 462879693.041532 UNITED KINGDOM | 1994 | 460478807.566473 UNITED KINGDOM | 1993 | 465607543.762586 UNITED KINGDOM | 1992 | 455757175.352833 UNITED STATES | 1998 | 280120004.30142 UNITED STATES | 1997 | 476637331.217653 UNITED STATES | 1996 | 473791434.944224 UNITED STATES | 1995 | 474521342.416346 UNITED STATES | 1994 | 477005639.44734 UNITED STATES | 1993 | 471839605.777827 UNITED STATES | 1992 | 476189462.550274 VIETNAM | 1998 | 275412910.780817 VIETNAM | 1997 | 463292401.995225 VIETNAM | 1996 | 467793595.757866 VIETNAM | 1995 | 465310457.100177 VIETNAM | 1994 | 469195525.691128 VIETNAM | 1993 | 471466964.417932 VIETNAM | 1992 | 459743569.761181 (175 rows) COMMIT; COMMIT