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 | 261676711.140813 ALGERIA | 1997 | 453292506.59749 ALGERIA | 1996 | 456465987.288438 ALGERIA | 1995 | 460923611.803604 ALGERIA | 1994 | 453171542.587906 ALGERIA | 1993 | 457267927.3434 ALGERIA | 1992 | 464381253.191939 ARGENTINA | 1998 | 282415472.717735 ARGENTINA | 1997 | 469569110.991255 ARGENTINA | 1996 | 468799133.955919 ARGENTINA | 1995 | 464735336.115929 ARGENTINA | 1994 | 471239674.4482 ARGENTINA | 1993 | 465535055.031357 ARGENTINA | 1992 | 471832199.729544 BRAZIL | 1998 | 282931154.993268 BRAZIL | 1997 | 474451220.542142 BRAZIL | 1996 | 469249170.33407 BRAZIL | 1995 | 475816464.506938 BRAZIL | 1994 | 468649244.364486 BRAZIL | 1993 | 477653420.655794 BRAZIL | 1992 | 472363379.021312 CANADA | 1998 | 277521558.128649 CANADA | 1997 | 476548713.435378 CANADA | 1996 | 485612421.283511 CANADA | 1995 | 480103673.699861 CANADA | 1994 | 483200096.119843 CANADA | 1993 | 484289989.537441 CANADA | 1992 | 480862589.713961 CHINA | 1998 | 277209677.392876 CHINA | 1997 | 475450936.998226 CHINA | 1996 | 472564258.808979 CHINA | 1995 | 478167900.305302 CHINA | 1994 | 469040480.775924 CHINA | 1993 | 479076848.983331 CHINA | 1992 | 485296049.888179 EGYPT | 1998 | 274119704.895725 EGYPT | 1997 | 465612322.674542 EGYPT | 1996 | 476349978.048418 EGYPT | 1995 | 461138834.860933 EGYPT | 1994 | 461941611.12859 EGYPT | 1993 | 463516853.930779 EGYPT | 1992 | 461224686.645226 ETHIOPIA | 1998 | 275177861.376915 ETHIOPIA | 1997 | 462516636.304536 ETHIOPIA | 1996 | 464496400.565842 ETHIOPIA | 1995 | 457607824.427456 ETHIOPIA | 1994 | 472975792.522071 ETHIOPIA | 1993 | 472696217.582152 ETHIOPIA | 1992 | 465532888.465918 FRANCE | 1998 | 266957048.946498 FRANCE | 1997 | 457081024.771137 FRANCE | 1996 | 461346625.765401 FRANCE | 1995 | 464888887.421252 FRANCE | 1994 | 471778536.954289 FRANCE | 1993 | 453153211.732063 FRANCE | 1992 | 462489891.477635 GERMANY | 1998 | 273234556.412003 GERMANY | 1997 | 478167989.123541 GERMANY | 1996 | 468925845.466423 GERMANY | 1995 | 466601120.817144 GERMANY | 1994 | 470354814.790878 GERMANY | 1993 | 464391036.460288 GERMANY | 1992 | 470124555.55953 INDIA | 1998 | 281613692.497738 INDIA | 1997 | 481610396.71556 INDIA | 1996 | 484863918.802409 INDIA | 1995 | 490063042.56299 INDIA | 1994 | 492692565.314 INDIA | 1993 | 489596993.701165 INDIA | 1992 | 479381573.359553 INDONESIA | 1998 | 269585811.153401 INDONESIA | 1997 | 473846067.640424 INDONESIA | 1996 | 472202636.342586 INDONESIA | 1995 | 468066126.059502 INDONESIA | 1994 | 475927791.835528 INDONESIA | 1993 | 463850176.564184 INDONESIA | 1992 | 459208199.716741 IRAN | 1998 | 273756530.218296 IRAN | 1997 | 471855445.395026 IRAN | 1996 | 469163164.822798 IRAN | 1995 | 471614288.463359 IRAN | 1994 | 469031006.006067 IRAN | 1993 | 466794884.417436 IRAN | 1992 | 469410657.819741 IRAQ | 1998 | 277553369.402705 IRAQ | 1997 | 471337162.579645 IRAQ | 1996 | 472702549.333977 IRAQ | 1995 | 469650305.634565 IRAQ | 1994 | 471439234.831943 IRAQ | 1993 | 479015591.85402 IRAQ | 1992 | 475512241.44633 JAPAN | 1998 | 265120733.867651 JAPAN | 1997 | 466516775.843377 JAPAN | 1996 | 475118013.920182 JAPAN | 1995 | 465353816.91113 JAPAN | 1994 | 475113825.226312 JAPAN | 1993 | 467069235.260946 JAPAN | 1992 | 467124870.805454 JORDAN | 1998 | 269752299.617004 JORDAN | 1997 | 465836446.589093 JORDAN | 1996 | 465273446.202074 JORDAN | 1995 | 462192080.228033 JORDAN | 1994 | 467498591.792198 JORDAN | 1993 | 468601193.210477 JORDAN | 1992 | 465451930.665403 KENYA | 1998 | 280924335.549567 KENYA | 1997 | 479750664.624793 KENYA | 1996 | 472541544.958766 KENYA | 1995 | 476747959.807144 KENYA | 1994 | 485541457.386016 KENYA | 1993 | 473893059.105378 KENYA | 1992 | 472614730.349286 MOROCCO | 1998 | 273238055.351664 MOROCCO | 1997 | 476428327.504589 MOROCCO | 1996 | 472010411.326829 MOROCCO | 1995 | 472931480.064028 MOROCCO | 1994 | 480449860.728166 MOROCCO | 1993 | 463105179.026826 MOROCCO | 1992 | 478719001.697734 MOZAMBIQUE | 1998 | 267223265.594289 MOZAMBIQUE | 1997 | 452933778.563721 MOZAMBIQUE | 1996 | 459188433.471749 MOZAMBIQUE | 1995 | 457717872.333534 MOZAMBIQUE | 1994 | 457270270.799672 MOZAMBIQUE | 1993 | 459576073.20286 MOZAMBIQUE | 1992 | 458971187.00862 PERU | 1998 | 266014451.067395 PERU | 1997 | 469972134.544101 PERU | 1996 | 470889275.802826 PERU | 1995 | 469457848.151809 PERU | 1994 | 462963803.280476 PERU | 1993 | 469482856.448218 PERU | 1992 | 464075459.655817 ROMANIA | 1998 | 277718326.859655 ROMANIA | 1997 | 471460502.000616 ROMANIA | 1996 | 482969656.758058 ROMANIA | 1995 | 476315310.758428 ROMANIA | 1994 | 465427339.668361 ROMANIA | 1993 | 474962656.991233 ROMANIA | 1992 | 470415135.042682 RUSSIA | 1998 | 279289924.06283 RUSSIA | 1997 | 480081531.644264 RUSSIA | 1996 | 475717929.549518 RUSSIA | 1995 | 474846321.27874 RUSSIA | 1994 | 471441282.677599 RUSSIA | 1993 | 476110172.553014 RUSSIA | 1992 | 479094574.526923 SAUDI ARABIA | 1998 | 273585159.459616 SAUDI ARABIA | 1997 | 467711652.15047 SAUDI ARABIA | 1996 | 474504399.030713 SAUDI ARABIA | 1995 | 475963899.463605 SAUDI ARABIA | 1994 | 475935688.241185 SAUDI ARABIA | 1993 | 480481076.159108 SAUDI ARABIA | 1992 | 476089639.700154 UNITED KINGDOM | 1998 | 271458513.723978 UNITED KINGDOM | 1997 | 466454861.69095 UNITED KINGDOM | 1996 | 463438686.526847 UNITED KINGDOM | 1995 | 462489895.790358 UNITED KINGDOM | 1994 | 460620604.16801 UNITED KINGDOM | 1993 | 465305270.695412 UNITED KINGDOM | 1992 | 455778901.761663 UNITED STATES | 1998 | 280269713.030759 UNITED STATES | 1997 | 476753162.333851 UNITED STATES | 1996 | 473495518.256946 UNITED STATES | 1995 | 474500064.195258 UNITED STATES | 1994 | 476925177.34126 UNITED STATES | 1993 | 471863276.871206 UNITED STATES | 1992 | 476167388.645841 VIETNAM | 1998 | 275478521.299146 VIETNAM | 1997 | 462906454.643048 VIETNAM | 1996 | 467295854.87918 VIETNAM | 1995 | 465181887.468579 VIETNAM | 1994 | 469127701.026424 VIETNAM | 1993 | 471316528.430659 VIETNAM | 1992 | 459696999.885028 (175 rows) COMMIT; COMMIT