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 | 261330959.423092 ALGERIA | 1997 | 453283074.066437 ALGERIA | 1996 | 456435471.08201 ALGERIA | 1995 | 461020192.29675 ALGERIA | 1994 | 452838549.73744 ALGERIA | 1993 | 457115538.37182 ALGERIA | 1992 | 464402409.906978 ARGENTINA | 1998 | 282237522.402012 ARGENTINA | 1997 | 469372057.916852 ARGENTINA | 1996 | 468875851.1178 ARGENTINA | 1995 | 464746449.202667 ARGENTINA | 1994 | 471708640.673593 ARGENTINA | 1993 | 465493936.196965 ARGENTINA | 1992 | 471635066.182124 BRAZIL | 1998 | 282785181.135309 BRAZIL | 1997 | 474192095.771234 BRAZIL | 1996 | 469130339.447564 BRAZIL | 1995 | 475932597.424037 BRAZIL | 1994 | 468474519.500072 BRAZIL | 1993 | 477240768.952304 BRAZIL | 1992 | 472396840.866567 CANADA | 1998 | 277476122.884601 CANADA | 1997 | 476238218.74704 CANADA | 1996 | 485771872.321609 CANADA | 1995 | 479949069.230691 CANADA | 1994 | 483484222.831717 CANADA | 1993 | 483952135.185903 CANADA | 1992 | 480806510.154754 CHINA | 1998 | 277033697.350787 CHINA | 1997 | 475268748.457822 CHINA | 1996 | 472144662.995489 CHINA | 1995 | 478273131.258283 CHINA | 1994 | 469169200.30167 CHINA | 1993 | 478718970.66254 CHINA | 1992 | 484964492.923027 EGYPT | 1998 | 274191985.812212 EGYPT | 1997 | 465377209.077563 EGYPT | 1996 | 475935996.616323 EGYPT | 1995 | 460936961.064771 EGYPT | 1994 | 461830496.29047 EGYPT | 1993 | 463514510.540736 EGYPT | 1992 | 460978130.211621 ETHIOPIA | 1998 | 275058615.108889 ETHIOPIA | 1997 | 462470467.470881 ETHIOPIA | 1996 | 464634937.7765 ETHIOPIA | 1995 | 457544892.303337 ETHIOPIA | 1994 | 472788529.714621 ETHIOPIA | 1993 | 472347000.73548 ETHIOPIA | 1992 | 465436373.089494 FRANCE | 1998 | 266903958.690347 FRANCE | 1997 | 457531476.544119 FRANCE | 1996 | 461140120.604162 FRANCE | 1995 | 464720841.830545 FRANCE | 1994 | 471645204.943456 FRANCE | 1993 | 453283364.795357 FRANCE | 1992 | 462386939.750345 GERMANY | 1998 | 273084512.942956 GERMANY | 1997 | 478256956.143692 GERMANY | 1996 | 469366403.178665 GERMANY | 1995 | 467051471.849322 GERMANY | 1994 | 470607222.297047 GERMANY | 1993 | 464144987.597355 GERMANY | 1992 | 470394876.877655 INDIA | 1998 | 281732391.813556 INDIA | 1997 | 481671045.626451 INDIA | 1996 | 484998474.158508 INDIA | 1995 | 490092605.393113 INDIA | 1994 | 492712371.496905 INDIA | 1993 | 489536916.566433 INDIA | 1992 | 479169947.831335 INDONESIA | 1998 | 269509274.323709 INDONESIA | 1997 | 474043045.836825 INDONESIA | 1996 | 472450962.504771 INDONESIA | 1995 | 468279396.890184 INDONESIA | 1994 | 475589084.083764 INDONESIA | 1993 | 463701747.179761 INDONESIA | 1992 | 459268140.828437 IRAN | 1998 | 274112740.773148 IRAN | 1997 | 471799076.948545 IRAN | 1996 | 469541655.285089 IRAN | 1995 | 471915099.284476 IRAN | 1994 | 469041257.750442 IRAN | 1993 | 466648533.84206 IRAN | 1992 | 469650788.309299 IRAQ | 1998 | 277549604.210666 IRAQ | 1997 | 471306720.227979 IRAQ | 1996 | 473274674.671914 IRAQ | 1995 | 469518149.688213 IRAQ | 1994 | 471442179.990073 IRAQ | 1993 | 478890393.007924 IRAQ | 1992 | 475316121.444688 JAPAN | 1998 | 264961308.763294 JAPAN | 1997 | 466663301.50299 JAPAN | 1996 | 475197423.391853 JAPAN | 1995 | 465201922.05443 JAPAN | 1994 | 475443822.239709 JAPAN | 1993 | 466748047.405479 JAPAN | 1992 | 467217125.534951 JORDAN | 1998 | 269529690.911324 JORDAN | 1997 | 465961453.011295 JORDAN | 1996 | 465176790.220805 JORDAN | 1995 | 461956968.170164 JORDAN | 1994 | 467729690.881901 JORDAN | 1993 | 468525394.409872 JORDAN | 1992 | 464755201.552896 KENYA | 1998 | 280946404.21945 KENYA | 1997 | 479904959.779195 KENYA | 1996 | 472192054.440042 KENYA | 1995 | 476711045.25996 KENYA | 1994 | 485560911.12654 KENYA | 1993 | 473821404.283861 KENYA | 1992 | 473000854.260493 MOROCCO | 1998 | 273372997.087188 MOROCCO | 1997 | 476121807.288334 MOROCCO | 1996 | 472029166.320199 MOROCCO | 1995 | 472519722.846629 MOROCCO | 1994 | 480624747.948412 MOROCCO | 1993 | 463242343.686889 MOROCCO | 1992 | 478605047.227667 MOZAMBIQUE | 1998 | 267650833.487665 MOZAMBIQUE | 1997 | 452752457.845041 MOZAMBIQUE | 1996 | 458901203.112367 MOZAMBIQUE | 1995 | 457977549.40383 MOZAMBIQUE | 1994 | 457571383.953772 MOZAMBIQUE | 1993 | 459659603.777969 MOZAMBIQUE | 1992 | 458441895.871391 PERU | 1998 | 266027167.947335 PERU | 1997 | 469904931.955779 PERU | 1996 | 470729954.729644 PERU | 1995 | 469087896.354111 PERU | 1994 | 463169210.140475 PERU | 1993 | 469839096.122557 PERU | 1992 | 464324368.217404 ROMANIA | 1998 | 277700842.038187 ROMANIA | 1997 | 471659992.136387 ROMANIA | 1996 | 482613828.858935 ROMANIA | 1995 | 476856622.581505 ROMANIA | 1994 | 465659343.296279 ROMANIA | 1993 | 474633226.067988 ROMANIA | 1992 | 470566945.521687 RUSSIA | 1998 | 279143540.27514 RUSSIA | 1997 | 479902256.596356 RUSSIA | 1996 | 475614189.343831 RUSSIA | 1995 | 475034394.320913 RUSSIA | 1994 | 471512636.548058 RUSSIA | 1993 | 476182966.902907 RUSSIA | 1992 | 479512814.636731 SAUDI ARABIA | 1998 | 273542607.833065 SAUDI ARABIA | 1997 | 467713436.424922 SAUDI ARABIA | 1996 | 474582084.557905 SAUDI ARABIA | 1995 | 476257067.319673 SAUDI ARABIA | 1994 | 476226575.690816 SAUDI ARABIA | 1993 | 480131505.387289 SAUDI ARABIA | 1992 | 476298035.085909 UNITED KINGDOM | 1998 | 271443781.434356 UNITED KINGDOM | 1997 | 466441214.24952 UNITED KINGDOM | 1996 | 463881773.885615 UNITED KINGDOM | 1995 | 462902148.585218 UNITED KINGDOM | 1994 | 460620841.169383 UNITED KINGDOM | 1993 | 464937749.881354 UNITED KINGDOM | 1992 | 455603880.013982 UNITED STATES | 1998 | 280366108.621254 UNITED STATES | 1997 | 476308305.380767 UNITED STATES | 1996 | 473539609.585672 UNITED STATES | 1995 | 474127299.969516 UNITED STATES | 1994 | 477049550.727849 UNITED STATES | 1993 | 471894002.736215 UNITED STATES | 1992 | 476371574.842483 VIETNAM | 1998 | 275716270.233414 VIETNAM | 1997 | 462855915.911846 VIETNAM | 1996 | 466999194.515383 VIETNAM | 1995 | 465144984.4908 VIETNAM | 1994 | 469222381.866012 VIETNAM | 1993 | 471580379.757594 VIETNAM | 1992 | 459997294.840782 (175 rows) COMMIT; COMMIT