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 | 261481624.686069 ALGERIA | 1997 | 453591414.935346 ALGERIA | 1996 | 456962826.099178 ALGERIA | 1995 | 460881214.056893 ALGERIA | 1994 | 452903559.436385 ALGERIA | 1993 | 457562245.897649 ALGERIA | 1992 | 464407861.763577 ARGENTINA | 1998 | 281516535.387243 ARGENTINA | 1997 | 469784364.34661 ARGENTINA | 1996 | 468357951.041521 ARGENTINA | 1995 | 464752687.19781 ARGENTINA | 1994 | 472190295.278407 ARGENTINA | 1993 | 465907084.667027 ARGENTINA | 1992 | 471907531.145707 BRAZIL | 1998 | 283096163.977252 BRAZIL | 1997 | 473942978.073122 BRAZIL | 1996 | 469633359.519947 BRAZIL | 1995 | 475693193.251016 BRAZIL | 1994 | 467808467.283428 BRAZIL | 1993 | 476681409.512987 BRAZIL | 1992 | 472383314.669209 CANADA | 1998 | 277200536.665358 CANADA | 1997 | 476019809.403537 CANADA | 1996 | 485649354.156245 CANADA | 1995 | 479385085.103657 CANADA | 1994 | 483545687.157666 CANADA | 1993 | 483757068.273218 CANADA | 1992 | 480687047.382264 CHINA | 1998 | 277093040.037282 CHINA | 1997 | 475114425.654571 CHINA | 1996 | 472267645.456909 CHINA | 1995 | 477534151.806746 CHINA | 1994 | 469225584.161059 CHINA | 1993 | 478808997.889719 CHINA | 1992 | 485335432.953589 EGYPT | 1998 | 274060718.272034 EGYPT | 1997 | 465400267.156567 EGYPT | 1996 | 476199180.436628 EGYPT | 1995 | 460377678.005892 EGYPT | 1994 | 461852361.393448 EGYPT | 1993 | 462720084.425249 EGYPT | 1992 | 461280114.947383 ETHIOPIA | 1998 | 275106870.269984 ETHIOPIA | 1997 | 461845757.761229 ETHIOPIA | 1996 | 464363295.603083 ETHIOPIA | 1995 | 457397634.382204 ETHIOPIA | 1994 | 472817760.439709 ETHIOPIA | 1993 | 472489808.9882 ETHIOPIA | 1992 | 466015466.294713 FRANCE | 1998 | 266844922.268883 FRANCE | 1997 | 457225303.506266 FRANCE | 1996 | 461288234.354991 FRANCE | 1995 | 463374997.425588 FRANCE | 1994 | 472387112.59703 FRANCE | 1993 | 453544819.051102 FRANCE | 1992 | 462332174.732758 GERMANY | 1998 | 273082398.517885 GERMANY | 1997 | 477549991.827606 GERMANY | 1996 | 469064370.720159 GERMANY | 1995 | 466704517.669715 GERMANY | 1994 | 470619559.702036 GERMANY | 1993 | 463801883.769427 GERMANY | 1992 | 471175151.740173 INDIA | 1998 | 281433711.750085 INDIA | 1997 | 481646914.345535 INDIA | 1996 | 484839711.415795 INDIA | 1995 | 489240718.925586 INDIA | 1994 | 491840638.586327 INDIA | 1993 | 489041225.74769 INDIA | 1992 | 479228651.595794 INDONESIA | 1998 | 268761996.696965 INDONESIA | 1997 | 474335359.873376 INDONESIA | 1996 | 472664513.419273 INDONESIA | 1995 | 468571364.633298 INDONESIA | 1994 | 475531704.817137 INDONESIA | 1993 | 463757074.779199 INDONESIA | 1992 | 459502698.204484 IRAN | 1998 | 274261060.825951 IRAN | 1997 | 472461386.256645 IRAN | 1996 | 468633025.31603 IRAN | 1995 | 471996325.276378 IRAN | 1994 | 468820128.973375 IRAN | 1993 | 466355916.310278 IRAN | 1992 | 470698590.952092 IRAQ | 1998 | 277735701.633009 IRAQ | 1997 | 471530435.814819 IRAQ | 1996 | 473246803.336621 IRAQ | 1995 | 469573400.234956 IRAQ | 1994 | 471299404.520711 IRAQ | 1993 | 479152249.313418 IRAQ | 1992 | 474808642.242521 JAPAN | 1998 | 265033297.813226 JAPAN | 1997 | 466587664.394577 JAPAN | 1996 | 475233494.818412 JAPAN | 1995 | 465085134.627559 JAPAN | 1994 | 475960273.994947 JAPAN | 1993 | 466453350.103469 JAPAN | 1992 | 468130863.262263 JORDAN | 1998 | 270053607.207963 JORDAN | 1997 | 465962447.914652 JORDAN | 1996 | 465311071.914152 JORDAN | 1995 | 462122747.194152 JORDAN | 1994 | 468197862.416185 JORDAN | 1993 | 468156385.529298 JORDAN | 1992 | 464442382.704666 KENYA | 1998 | 280588446.468248 KENYA | 1997 | 480120934.568906 KENYA | 1996 | 472378433.921157 KENYA | 1995 | 476957149.621416 KENYA | 1994 | 484714005.723503 KENYA | 1993 | 473749055.797362 KENYA | 1992 | 472813599.171583 MOROCCO | 1998 | 273187202.578963 MOROCCO | 1997 | 476680581.510567 MOROCCO | 1996 | 471834330.743854 MOROCCO | 1995 | 472461738.833237 MOROCCO | 1994 | 481243371.29136 MOROCCO | 1993 | 463322069.306794 MOROCCO | 1992 | 478935808.354978 MOZAMBIQUE | 1998 | 267591595.198902 MOZAMBIQUE | 1997 | 453421958.066714 MOZAMBIQUE | 1996 | 459084396.156092 MOZAMBIQUE | 1995 | 457847365.018334 MOZAMBIQUE | 1994 | 457858715.146533 MOZAMBIQUE | 1993 | 459351451.754119 MOZAMBIQUE | 1992 | 458586173.554605 PERU | 1998 | 266175962.830861 PERU | 1997 | 469519828.239038 PERU | 1996 | 470762637.142772 PERU | 1995 | 469176707.893284 PERU | 1994 | 463909316.645128 PERU | 1993 | 469860353.030638 PERU | 1992 | 464500082.942063 ROMANIA | 1998 | 277907511.588842 ROMANIA | 1997 | 471879575.692118 ROMANIA | 1996 | 481412470.26264 ROMANIA | 1995 | 476872672.53461 ROMANIA | 1994 | 465069580.436017 ROMANIA | 1993 | 474619992.220388 ROMANIA | 1992 | 469868473.697039 RUSSIA | 1998 | 279061952.740773 RUSSIA | 1997 | 480158247.152453 RUSSIA | 1996 | 475267653.066928 RUSSIA | 1995 | 475149880.039405 RUSSIA | 1994 | 471595852.125462 RUSSIA | 1993 | 476633109.692237 RUSSIA | 1992 | 479830549.793828 SAUDI ARABIA | 1998 | 273359255.136781 SAUDI ARABIA | 1997 | 468251713.654645 SAUDI ARABIA | 1996 | 474878389.787248 SAUDI ARABIA | 1995 | 476900997.464322 SAUDI ARABIA | 1994 | 476503520.224916 SAUDI ARABIA | 1993 | 480227553.543725 SAUDI ARABIA | 1992 | 476881965.534257 UNITED KINGDOM | 1998 | 271443208.570167 UNITED KINGDOM | 1997 | 466637518.088222 UNITED KINGDOM | 1996 | 463515652.766545 UNITED KINGDOM | 1995 | 462944243.965648 UNITED KINGDOM | 1994 | 460801835.969879 UNITED KINGDOM | 1993 | 465428005.842753 UNITED KINGDOM | 1992 | 455943215.646152 UNITED STATES | 1998 | 279957746.369297 UNITED STATES | 1997 | 475780581.215611 UNITED STATES | 1996 | 473462887.910437 UNITED STATES | 1995 | 473935031.432824 UNITED STATES | 1994 | 477064388.653722 UNITED STATES | 1993 | 472707504.11099 UNITED STATES | 1992 | 476727832.069615 VIETNAM | 1998 | 275509608.768616 VIETNAM | 1997 | 463530644.394202 VIETNAM | 1996 | 467006011.994355 VIETNAM | 1995 | 464837189.122792 VIETNAM | 1994 | 469879733.85183 VIETNAM | 1993 | 471699775.636831 VIETNAM | 1992 | 459750702.785396 (175 rows) COMMIT; COMMIT