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 '%chocolate%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 273324029.8977 ALGERIA | 1997 | 471756756.442641 ALGERIA | 1996 | 468839386.961918 ALGERIA | 1995 | 456348481.362888 ALGERIA | 1994 | 462506944.219621 ALGERIA | 1993 | 458660101.419898 ALGERIA | 1992 | 459892183.07557 ARGENTINA | 1998 | 274457116.080048 ARGENTINA | 1997 | 470244061.734866 ARGENTINA | 1996 | 472544456.681967 ARGENTINA | 1995 | 474321003.456776 ARGENTINA | 1994 | 473734604.045296 ARGENTINA | 1993 | 472288999.885671 ARGENTINA | 1992 | 470064667.643128 BRAZIL | 1998 | 279896688.843598 BRAZIL | 1997 | 474063969.89463 BRAZIL | 1996 | 469095377.179089 BRAZIL | 1995 | 477402766.847341 BRAZIL | 1994 | 476821564.078127 BRAZIL | 1993 | 469653582.083297 BRAZIL | 1992 | 467267788.743079 CANADA | 1998 | 273787581.169701 CANADA | 1997 | 468151253.577247 CANADA | 1996 | 473176447.429699 CANADA | 1995 | 473417259.714496 CANADA | 1994 | 478974011.975523 CANADA | 1993 | 475950757.580148 CANADA | 1992 | 479707853.907819 CHINA | 1998 | 277068424.208918 CHINA | 1997 | 467921223.394497 CHINA | 1996 | 474799385.405917 CHINA | 1995 | 475234543.044265 CHINA | 1994 | 469591582.956996 CHINA | 1993 | 468211892.056685 CHINA | 1992 | 477932495.338604 EGYPT | 1998 | 274523350.455549 EGYPT | 1997 | 463503901.018624 EGYPT | 1996 | 456765344.123587 EGYPT | 1995 | 467100999.039694 EGYPT | 1994 | 467049970.0611 EGYPT | 1993 | 467178457.147662 EGYPT | 1992 | 465691967.767368 ETHIOPIA | 1998 | 272847969.379005 ETHIOPIA | 1997 | 455138593.159253 ETHIOPIA | 1996 | 459720544.862048 ETHIOPIA | 1995 | 464132564.731247 ETHIOPIA | 1994 | 466235394.390922 ETHIOPIA | 1993 | 461724937.051524 ETHIOPIA | 1992 | 462309401.471131 FRANCE | 1998 | 269617477.026315 FRANCE | 1997 | 458546904.545777 FRANCE | 1996 | 460672845.865228 FRANCE | 1995 | 455155144.963607 FRANCE | 1994 | 460397832.143782 FRANCE | 1993 | 458267805.427833 FRANCE | 1992 | 458998808.447272 GERMANY | 1998 | 281151970.53829 GERMANY | 1997 | 479663648.933102 GERMANY | 1996 | 473602667.939997 GERMANY | 1995 | 489481848.10071 GERMANY | 1994 | 482522106.279535 GERMANY | 1993 | 478923910.290647 GERMANY | 1992 | 483139421.722583 INDIA | 1998 | 277185881.60175 INDIA | 1997 | 475180182.482075 INDIA | 1996 | 478996241.855362 INDIA | 1995 | 479658673.861057 INDIA | 1994 | 475325740.645151 INDIA | 1993 | 479528587.520635 INDIA | 1992 | 482927632.808812 INDONESIA | 1998 | 273906907.455214 INDONESIA | 1997 | 457162805.363419 INDONESIA | 1996 | 467900160.891656 INDONESIA | 1995 | 469241816.428909 INDONESIA | 1994 | 460472191.218009 INDONESIA | 1993 | 474231391.686159 INDONESIA | 1992 | 473094815.131448 IRAN | 1998 | 275769554.146421 IRAN | 1997 | 472530509.06416 IRAN | 1996 | 467211675.013882 IRAN | 1995 | 464929530.230261 IRAN | 1994 | 470547182.848927 IRAN | 1993 | 469658033.630654 IRAN | 1992 | 469042679.230883 IRAQ | 1998 | 276009123.842146 IRAQ | 1997 | 484693790.745622 IRAQ | 1996 | 477528355.200882 IRAQ | 1995 | 473738686.054875 IRAQ | 1994 | 473827656.133285 IRAQ | 1993 | 478163190.002239 IRAQ | 1992 | 474253462.288149 JAPAN | 1998 | 271130170.296832 JAPAN | 1997 | 471799006.453081 JAPAN | 1996 | 470612664.489761 JAPAN | 1995 | 464755967.147962 JAPAN | 1994 | 459169307.443396 JAPAN | 1993 | 469348075.761898 JAPAN | 1992 | 463332673.556161 JORDAN | 1998 | 274090804.570388 JORDAN | 1997 | 466333760.000991 JORDAN | 1996 | 458537270.00766 JORDAN | 1995 | 463120326.184198 JORDAN | 1994 | 468141297.682149 JORDAN | 1993 | 473158137.010414 JORDAN | 1992 | 462091586.11986 KENYA | 1998 | 278593415.597231 KENYA | 1997 | 475163717.659463 KENYA | 1996 | 477963073.99392 KENYA | 1995 | 479445128.844261 KENYA | 1994 | 468262868.064886 KENYA | 1993 | 466452660.258724 KENYA | 1992 | 478852597.338188 MOROCCO | 1998 | 282541030.015009 MOROCCO | 1997 | 469188430.038279 MOROCCO | 1996 | 469405537.780238 MOROCCO | 1995 | 475505733.73321 MOROCCO | 1994 | 475433920.687919 MOROCCO | 1993 | 467089181.774205 MOROCCO | 1992 | 467751418.880429 MOZAMBIQUE | 1998 | 271570859.657254 MOZAMBIQUE | 1997 | 465855649.185186 MOZAMBIQUE | 1996 | 463011080.319075 MOZAMBIQUE | 1995 | 460155333.198842 MOZAMBIQUE | 1994 | 465135750.111089 MOZAMBIQUE | 1993 | 462586011.603172 MOZAMBIQUE | 1992 | 460768177.168937 PERU | 1998 | 273468742.218 PERU | 1997 | 466993550.144431 PERU | 1996 | 468664875.442926 PERU | 1995 | 466700859.240691 PERU | 1994 | 469188983.451445 PERU | 1993 | 470713616.170003 PERU | 1992 | 470827272.384507 ROMANIA | 1998 | 276954118.257828 ROMANIA | 1997 | 476882289.48311 ROMANIA | 1996 | 482899313.817134 ROMANIA | 1995 | 466264157.606992 ROMANIA | 1994 | 465255250.906587 ROMANIA | 1993 | 467066473.216148 ROMANIA | 1992 | 470847777.346432 RUSSIA | 1998 | 280543322.23698 RUSSIA | 1997 | 474709952.07253 RUSSIA | 1996 | 473563247.651472 RUSSIA | 1995 | 478659054.034838 RUSSIA | 1994 | 470491947.301714 RUSSIA | 1993 | 480196093.063761 RUSSIA | 1992 | 473702598.689017 SAUDI ARABIA | 1998 | 279463468.775347 SAUDI ARABIA | 1997 | 471794497.578567 SAUDI ARABIA | 1996 | 472452813.435472 SAUDI ARABIA | 1995 | 468416025.899973 SAUDI ARABIA | 1994 | 468030498.538071 SAUDI ARABIA | 1993 | 469554261.20258 SAUDI ARABIA | 1992 | 468379719.087049 UNITED KINGDOM | 1998 | 264579633.166211 UNITED KINGDOM | 1997 | 457282537.810205 UNITED KINGDOM | 1996 | 466556399.064891 UNITED KINGDOM | 1995 | 460056886.952734 UNITED KINGDOM | 1994 | 462453159.93115 UNITED KINGDOM | 1993 | 459626531.604774 UNITED KINGDOM | 1992 | 459725874.259029 UNITED STATES | 1998 | 285279767.976038 UNITED STATES | 1997 | 470335703.221979 UNITED STATES | 1996 | 477342902.704603 UNITED STATES | 1995 | 474798167.352609 UNITED STATES | 1994 | 480118676.615487 UNITED STATES | 1993 | 473062391.841797 UNITED STATES | 1992 | 477065097.939966 VIETNAM | 1998 | 271667734.578538 VIETNAM | 1997 | 465896754.783953 VIETNAM | 1996 | 479062529.836209 VIETNAM | 1995 | 464491171.191747 VIETNAM | 1994 | 470690780.054685 VIETNAM | 1993 | 475151704.431896 VIETNAM | 1992 | 469626513.451718 (175 rows) COMMIT; COMMIT