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 '%lemon%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265376180.319919 ALGERIA | 1997 | 447559685.275533 ALGERIA | 1996 | 455919506.894067 ALGERIA | 1995 | 451721169.219859 ALGERIA | 1994 | 458537441.858803 ALGERIA | 1993 | 453686814.751747 ALGERIA | 1992 | 445876551.522398 ARGENTINA | 1998 | 276184328.079293 ARGENTINA | 1997 | 464860035.504369 ARGENTINA | 1996 | 463701304.120113 ARGENTINA | 1995 | 466936534.895169 ARGENTINA | 1994 | 470983700.619932 ARGENTINA | 1993 | 465024761.189511 ARGENTINA | 1992 | 458669315.336456 BRAZIL | 1998 | 280489394.844882 BRAZIL | 1997 | 479673111.881388 BRAZIL | 1996 | 481232513.169672 BRAZIL | 1995 | 465867939.684031 BRAZIL | 1994 | 465390907.168007 BRAZIL | 1993 | 473510752.074267 BRAZIL | 1992 | 474836424.282446 CANADA | 1998 | 276867751.977376 CANADA | 1997 | 475693889.094221 CANADA | 1996 | 480483956.206522 CANADA | 1995 | 476337905.931196 CANADA | 1994 | 479675443.997716 CANADA | 1993 | 474498558.894653 CANADA | 1992 | 476425294.720527 CHINA | 1998 | 266364579.969705 CHINA | 1997 | 466598805.697707 CHINA | 1996 | 468311076.157401 CHINA | 1995 | 462596867.81519 CHINA | 1994 | 458735356.75961 CHINA | 1993 | 464426497.603756 CHINA | 1992 | 463026473.376986 EGYPT | 1998 | 268273523.053128 EGYPT | 1997 | 459091063.686868 EGYPT | 1996 | 463261918.391009 EGYPT | 1995 | 454301124.925845 EGYPT | 1994 | 466169366.338028 EGYPT | 1993 | 472876526.201094 EGYPT | 1992 | 465036589.525472 ETHIOPIA | 1998 | 275272931.371242 ETHIOPIA | 1997 | 460783598.908942 ETHIOPIA | 1996 | 470362179.588952 ETHIOPIA | 1995 | 469085005.99171 ETHIOPIA | 1994 | 468257650.987127 ETHIOPIA | 1993 | 467438610.851679 ETHIOPIA | 1992 | 463807502.790106 FRANCE | 1998 | 268336743.077418 FRANCE | 1997 | 453483885.621073 FRANCE | 1996 | 466792199.687766 FRANCE | 1995 | 455723081.485147 FRANCE | 1994 | 460262443.561567 FRANCE | 1993 | 458705119.394835 FRANCE | 1992 | 450721667.379456 GERMANY | 1998 | 278960059.302925 GERMANY | 1997 | 479474944.086361 GERMANY | 1996 | 479918488.440601 GERMANY | 1995 | 474873961.383254 GERMANY | 1994 | 472724707.48264 GERMANY | 1993 | 466431239.297422 GERMANY | 1992 | 482904607.062978 INDIA | 1998 | 279176847.246 INDIA | 1997 | 477880352.933262 INDIA | 1996 | 478511123.800405 INDIA | 1995 | 473088595.020235 INDIA | 1994 | 476356782.355818 INDIA | 1993 | 472648319.680176 INDIA | 1992 | 482922069.807945 INDONESIA | 1998 | 269505779.762927 INDONESIA | 1997 | 453870895.870241 INDONESIA | 1996 | 452222276.08786 INDONESIA | 1995 | 452122005.621363 INDONESIA | 1994 | 453661667.514761 INDONESIA | 1993 | 460262940.917592 INDONESIA | 1992 | 460095456.206257 IRAN | 1998 | 269415551.658702 IRAN | 1997 | 463492816.602075 IRAN | 1996 | 472028926.190017 IRAN | 1995 | 464227610.726597 IRAN | 1994 | 464614997.513376 IRAN | 1993 | 463676818.914229 IRAN | 1992 | 471116566.482657 IRAQ | 1998 | 279912962.024082 IRAQ | 1997 | 476424663.439001 IRAQ | 1996 | 473282457.666887 IRAQ | 1995 | 477623639.758277 IRAQ | 1994 | 477322627.581073 IRAQ | 1993 | 478464038.509348 IRAQ | 1992 | 474105860.698615 JAPAN | 1998 | 266430571.70393 JAPAN | 1997 | 464471311.333863 JAPAN | 1996 | 466091775.897431 JAPAN | 1995 | 466304257.410647 JAPAN | 1994 | 467712773.586341 JAPAN | 1993 | 466902959.380014 JAPAN | 1992 | 468075238.414198 JORDAN | 1998 | 273731590.349216 JORDAN | 1997 | 464135056.362446 JORDAN | 1996 | 470588569.812551 JORDAN | 1995 | 473547541.314059 JORDAN | 1994 | 465182446.93587 JORDAN | 1993 | 463642175.717671 JORDAN | 1992 | 456547210.328822 KENYA | 1998 | 274429369.663265 KENYA | 1997 | 466314987.998514 KENYA | 1996 | 468197009.097805 KENYA | 1995 | 466786662.789063 KENYA | 1994 | 465832035.702669 KENYA | 1993 | 470189594.552878 KENYA | 1992 | 471788560.199456 MOROCCO | 1998 | 271350614.76325 MOROCCO | 1997 | 465062982.691425 MOROCCO | 1996 | 465849275.870582 MOROCCO | 1995 | 463731057.333255 MOROCCO | 1994 | 472727791.436699 MOROCCO | 1993 | 467600871.455951 MOROCCO | 1992 | 466767106.696567 MOZAMBIQUE | 1998 | 268060616.903853 MOZAMBIQUE | 1997 | 464500826.023829 MOZAMBIQUE | 1996 | 467054395.729982 MOZAMBIQUE | 1995 | 464589185.983196 MOZAMBIQUE | 1994 | 457359249.024846 MOZAMBIQUE | 1993 | 455308089.055797 MOZAMBIQUE | 1992 | 460601278.651245 PERU | 1998 | 273769241.039638 PERU | 1997 | 471324659.057445 PERU | 1996 | 472212791.375346 PERU | 1995 | 473739217.506826 PERU | 1994 | 465280438.500754 PERU | 1993 | 470371643.442027 PERU | 1992 | 475814978.952792 ROMANIA | 1998 | 270133541.956929 ROMANIA | 1997 | 472903864.913732 ROMANIA | 1996 | 470651832.533996 ROMANIA | 1995 | 465394991.383734 ROMANIA | 1994 | 462609608.976715 ROMANIA | 1993 | 473942815.83028 ROMANIA | 1992 | 473345581.023055 RUSSIA | 1998 | 279178249.94986 RUSSIA | 1997 | 465813264.410472 RUSSIA | 1996 | 475205527.853411 RUSSIA | 1995 | 469565068.079118 RUSSIA | 1994 | 468071715.878546 RUSSIA | 1993 | 473709737.682098 RUSSIA | 1992 | 466141431.81254 SAUDI ARABIA | 1998 | 274351944.774385 SAUDI ARABIA | 1997 | 467489092.000917 SAUDI ARABIA | 1996 | 467351212.479371 SAUDI ARABIA | 1995 | 473736403.88495 SAUDI ARABIA | 1994 | 463410226.95529 SAUDI ARABIA | 1993 | 469936508.380674 SAUDI ARABIA | 1992 | 475395943.566909 UNITED KINGDOM | 1998 | 270391662.789959 UNITED KINGDOM | 1997 | 458150951.583375 UNITED KINGDOM | 1996 | 462515824.070765 UNITED KINGDOM | 1995 | 462787268.396531 UNITED KINGDOM | 1994 | 458890621.036888 UNITED KINGDOM | 1993 | 461820609.167884 UNITED KINGDOM | 1992 | 460622214.283974 UNITED STATES | 1998 | 273925489.330817 UNITED STATES | 1997 | 471739159.827569 UNITED STATES | 1996 | 481459409.116865 UNITED STATES | 1995 | 471450481.628893 UNITED STATES | 1994 | 474189968.277618 UNITED STATES | 1993 | 487412971.660299 UNITED STATES | 1992 | 477165846.504488 VIETNAM | 1998 | 269949259.053585 VIETNAM | 1997 | 463273460.808967 VIETNAM | 1996 | 472649824.523339 VIETNAM | 1995 | 467083530.32122 VIETNAM | 1994 | 472007647.222776 VIETNAM | 1993 | 470808432.265678 VIETNAM | 1992 | 465793630.345545 (175 rows) COMMIT; COMMIT