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 '%mint%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264145431.681438 ALGERIA | 1997 | 456343624.928649 ALGERIA | 1996 | 461715863.063802 ALGERIA | 1995 | 465137149.188927 ALGERIA | 1994 | 453609926.354038 ALGERIA | 1993 | 460327489.461608 ALGERIA | 1992 | 457258641.140224 ARGENTINA | 1998 | 271525063.095477 ARGENTINA | 1997 | 466885414.254136 ARGENTINA | 1996 | 472247766.450138 ARGENTINA | 1995 | 470578604.473865 ARGENTINA | 1994 | 466143122.848663 ARGENTINA | 1993 | 462136442.521398 ARGENTINA | 1992 | 465346990.947896 BRAZIL | 1998 | 275517600.365901 BRAZIL | 1997 | 473648408.867135 BRAZIL | 1996 | 471232333.13756 BRAZIL | 1995 | 470779264.151196 BRAZIL | 1994 | 468545715.099432 BRAZIL | 1993 | 473263196.709885 BRAZIL | 1992 | 466234362.200212 CANADA | 1998 | 273248120.552481 CANADA | 1997 | 477271082.19373 CANADA | 1996 | 479520235.67327 CANADA | 1995 | 473359228.282986 CANADA | 1994 | 476420533.170854 CANADA | 1993 | 475365992.08071 CANADA | 1992 | 475512109.474101 CHINA | 1998 | 269762208.447908 CHINA | 1997 | 465895944.709877 CHINA | 1996 | 459451163.886381 CHINA | 1995 | 461322576.420726 CHINA | 1994 | 462196094.303828 CHINA | 1993 | 461328329.741127 CHINA | 1992 | 464753986.547875 EGYPT | 1998 | 271020002.460242 EGYPT | 1997 | 458834759.134713 EGYPT | 1996 | 461487953.875868 EGYPT | 1995 | 455245209.958944 EGYPT | 1994 | 455933421.060269 EGYPT | 1993 | 470260650.940215 EGYPT | 1992 | 459713028.37921 ETHIOPIA | 1998 | 268389151.192533 ETHIOPIA | 1997 | 444563059.440059 ETHIOPIA | 1996 | 457241045.735901 ETHIOPIA | 1995 | 462061962.435353 ETHIOPIA | 1994 | 463021967.010909 ETHIOPIA | 1993 | 449758403.5173 ETHIOPIA | 1992 | 458131076.513912 FRANCE | 1998 | 265443029.346386 FRANCE | 1997 | 462336909.333148 FRANCE | 1996 | 461894259.885803 FRANCE | 1995 | 460988746.861088 FRANCE | 1994 | 469114827.923426 FRANCE | 1993 | 461600740.935653 FRANCE | 1992 | 466134485.091664 GERMANY | 1998 | 277180788.339663 GERMANY | 1997 | 481570174.61623 GERMANY | 1996 | 481509624.31613 GERMANY | 1995 | 473347996.126234 GERMANY | 1994 | 471352217.814668 GERMANY | 1993 | 472534406.289739 GERMANY | 1992 | 475670208.841301 INDIA | 1998 | 279294540.412767 INDIA | 1997 | 472624514.845306 INDIA | 1996 | 475130348.768914 INDIA | 1995 | 474661005.341594 INDIA | 1994 | 474669041.872389 INDIA | 1993 | 471592392.655007 INDIA | 1992 | 483111886.368952 INDONESIA | 1998 | 270588290.093728 INDONESIA | 1997 | 458223562.190196 INDONESIA | 1996 | 468405940.900353 INDONESIA | 1995 | 452381779.129925 INDONESIA | 1994 | 458435558.275517 INDONESIA | 1993 | 457888775.688925 INDONESIA | 1992 | 458594312.165648 IRAN | 1998 | 266239875.160724 IRAN | 1997 | 456133108.183656 IRAN | 1996 | 461599323.714316 IRAN | 1995 | 452454660.846478 IRAN | 1994 | 456685131.021573 IRAN | 1993 | 459494235.779683 IRAN | 1992 | 460953792.275289 IRAQ | 1998 | 272177225.250218 IRAQ | 1997 | 464985287.665792 IRAQ | 1996 | 468989314.634572 IRAQ | 1995 | 464811103.044185 IRAQ | 1994 | 475052462.606557 IRAQ | 1993 | 465817344.012255 IRAQ | 1992 | 466387165.304377 JAPAN | 1998 | 270422434.943887 JAPAN | 1997 | 459769857.527244 JAPAN | 1996 | 459543823.286518 JAPAN | 1995 | 466736665.880252 JAPAN | 1994 | 459491003.698108 JAPAN | 1993 | 463131087.638697 JAPAN | 1992 | 464549071.612706 JORDAN | 1998 | 273449020.277485 JORDAN | 1997 | 455845741.97893 JORDAN | 1996 | 459831204.516997 JORDAN | 1995 | 459745001.088 JORDAN | 1994 | 459570712.259842 JORDAN | 1993 | 457623415.477734 JORDAN | 1992 | 450492682.188256 KENYA | 1998 | 279792234.901205 KENYA | 1997 | 473667579.91247 KENYA | 1996 | 481539252.384671 KENYA | 1995 | 468544487.903457 KENYA | 1994 | 484020248.463472 KENYA | 1993 | 469445346.718899 KENYA | 1992 | 472827108.550269 MOROCCO | 1998 | 270058714.922406 MOROCCO | 1997 | 446000028.953484 MOROCCO | 1996 | 456995966.337867 MOROCCO | 1995 | 468542290.430917 MOROCCO | 1994 | 458424208.580887 MOROCCO | 1993 | 455432213.50377 MOROCCO | 1992 | 462388805.815116 MOZAMBIQUE | 1998 | 274106836.695104 MOZAMBIQUE | 1997 | 458471539.775447 MOZAMBIQUE | 1996 | 467988422.760407 MOZAMBIQUE | 1995 | 463076138.542105 MOZAMBIQUE | 1994 | 465339062.867397 MOZAMBIQUE | 1993 | 470733187.286476 MOZAMBIQUE | 1992 | 462598882.520799 PERU | 1998 | 273058189.813159 PERU | 1997 | 473020661.152665 PERU | 1996 | 468903050.696213 PERU | 1995 | 466899562.741989 PERU | 1994 | 471090616.82973 PERU | 1993 | 463458541.016025 PERU | 1992 | 466214928.408234 ROMANIA | 1998 | 276673838.97355 ROMANIA | 1997 | 465134836.698471 ROMANIA | 1996 | 471327216.510797 ROMANIA | 1995 | 469204364.376683 ROMANIA | 1994 | 467869379.938866 ROMANIA | 1993 | 474755796.542754 ROMANIA | 1992 | 469686496.081452 RUSSIA | 1998 | 273363746.420009 RUSSIA | 1997 | 460515549.471475 RUSSIA | 1996 | 471160055.32865 RUSSIA | 1995 | 460260105.114931 RUSSIA | 1994 | 460768244.585873 RUSSIA | 1993 | 469056155.888827 RUSSIA | 1992 | 459739237.680261 SAUDI ARABIA | 1998 | 270031996.969547 SAUDI ARABIA | 1997 | 475156186.978958 SAUDI ARABIA | 1996 | 465399196.040832 SAUDI ARABIA | 1995 | 470103399.866137 SAUDI ARABIA | 1994 | 466538255.41024 SAUDI ARABIA | 1993 | 473389974.126463 SAUDI ARABIA | 1992 | 474031044.290832 UNITED KINGDOM | 1998 | 265574433.513364 UNITED KINGDOM | 1997 | 460233153.833147 UNITED KINGDOM | 1996 | 448621718.52824 UNITED KINGDOM | 1995 | 457457112.880151 UNITED KINGDOM | 1994 | 453233818.215852 UNITED KINGDOM | 1993 | 455148406.005638 UNITED KINGDOM | 1992 | 456399481.475679 UNITED STATES | 1998 | 283635174.208539 UNITED STATES | 1997 | 480882234.005067 UNITED STATES | 1996 | 474455226.51691 UNITED STATES | 1995 | 472353194.865835 UNITED STATES | 1994 | 482112393.900251 UNITED STATES | 1993 | 476224939.555106 UNITED STATES | 1992 | 471223288.221898 VIETNAM | 1998 | 278232231.523323 VIETNAM | 1997 | 463876029.043999 VIETNAM | 1996 | 465372946.443117 VIETNAM | 1995 | 466333911.841355 VIETNAM | 1994 | 469974311.414878 VIETNAM | 1993 | 466136499.575533 VIETNAM | 1992 | 468532385.093797 (175 rows) COMMIT; COMMIT