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 '%lace%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264179390.667605 ALGERIA | 1997 | 451645885.620152 ALGERIA | 1996 | 451520842.407517 ALGERIA | 1995 | 453706795.136312 ALGERIA | 1994 | 457990279.40251 ALGERIA | 1993 | 454456406.106569 ALGERIA | 1992 | 450375240.026061 ARGENTINA | 1998 | 268268389.728758 ARGENTINA | 1997 | 460366009.042137 ARGENTINA | 1996 | 461734163.138749 ARGENTINA | 1995 | 455546636.917693 ARGENTINA | 1994 | 459718673.483925 ARGENTINA | 1993 | 462025038.934382 ARGENTINA | 1992 | 460015386.720278 BRAZIL | 1998 | 276762626.419104 BRAZIL | 1997 | 472444429.395093 BRAZIL | 1996 | 476505530.234381 BRAZIL | 1995 | 481972462.579019 BRAZIL | 1994 | 475583299.111119 BRAZIL | 1993 | 473054062.476871 BRAZIL | 1992 | 471866957.318538 CANADA | 1998 | 280106977.237556 CANADA | 1997 | 475873996.069761 CANADA | 1996 | 481206514.70675 CANADA | 1995 | 470315278.557356 CANADA | 1994 | 471297951.942397 CANADA | 1993 | 476319956.381048 CANADA | 1992 | 476453838.945762 CHINA | 1998 | 272515646.859279 CHINA | 1997 | 463574333.86559 CHINA | 1996 | 462151891.42185 CHINA | 1995 | 458032089.705587 CHINA | 1994 | 464114454.477419 CHINA | 1993 | 463224189.379749 CHINA | 1992 | 459101697.094978 EGYPT | 1998 | 275422992.039695 EGYPT | 1997 | 463789507.235233 EGYPT | 1996 | 461783606.398384 EGYPT | 1995 | 462423138.847987 EGYPT | 1994 | 461897490.490467 EGYPT | 1993 | 468238425.639404 EGYPT | 1992 | 468805479.331194 ETHIOPIA | 1998 | 272210805.317656 ETHIOPIA | 1997 | 466606247.34879 ETHIOPIA | 1996 | 463139016.423788 ETHIOPIA | 1995 | 468179422.556921 ETHIOPIA | 1994 | 472236044.814532 ETHIOPIA | 1993 | 469591907.893459 ETHIOPIA | 1992 | 463439587.009977 FRANCE | 1998 | 262572562.249317 FRANCE | 1997 | 454009353.442515 FRANCE | 1996 | 454828225.134674 FRANCE | 1995 | 462428020.64245 FRANCE | 1994 | 458162721.487854 FRANCE | 1993 | 456226267.625119 FRANCE | 1992 | 453713942.569647 GERMANY | 1998 | 274946120.753576 GERMANY | 1997 | 465412173.238261 GERMANY | 1996 | 466799191.302 GERMANY | 1995 | 474253884.653621 GERMANY | 1994 | 471971865.564897 GERMANY | 1993 | 473603012.244732 GERMANY | 1992 | 467671568.947927 INDIA | 1998 | 284852038.250578 INDIA | 1997 | 475362254.077834 INDIA | 1996 | 482977387.450912 INDIA | 1995 | 479476748.87694 INDIA | 1994 | 474146465.547729 INDIA | 1993 | 481286428.707787 INDIA | 1992 | 478097496.647015 INDONESIA | 1998 | 270791391.754146 INDONESIA | 1997 | 472901132.69142 INDONESIA | 1996 | 467427888.784194 INDONESIA | 1995 | 464358085.834981 INDONESIA | 1994 | 473652870.252895 INDONESIA | 1993 | 467097547.782557 INDONESIA | 1992 | 467410839.052472 IRAN | 1998 | 269671879.572435 IRAN | 1997 | 467142459.829339 IRAN | 1996 | 459512372.587898 IRAN | 1995 | 465509601.851826 IRAN | 1994 | 457634574.735382 IRAN | 1993 | 462203653.024584 IRAN | 1992 | 467402482.181908 IRAQ | 1998 | 272444514.663715 IRAQ | 1997 | 470166132.386542 IRAQ | 1996 | 467726954.346624 IRAQ | 1995 | 468719371.436674 IRAQ | 1994 | 477465179.663495 IRAQ | 1993 | 462260953.204717 IRAQ | 1992 | 466259519.005895 JAPAN | 1998 | 275317840.083381 JAPAN | 1997 | 465969013.736504 JAPAN | 1996 | 467139880.37921 JAPAN | 1995 | 474056709.021047 JAPAN | 1994 | 458546631.838244 JAPAN | 1993 | 470009563.679743 JAPAN | 1992 | 473511995.494405 JORDAN | 1998 | 270075808.604139 JORDAN | 1997 | 460044044.494568 JORDAN | 1996 | 463011286.008831 JORDAN | 1995 | 462485847.766198 JORDAN | 1994 | 465576894.65198 JORDAN | 1993 | 466665391.302217 JORDAN | 1992 | 463124858.972595 KENYA | 1998 | 278577679.616571 KENYA | 1997 | 474394122.598144 KENYA | 1996 | 470121653.072526 KENYA | 1995 | 468645764.744362 KENYA | 1994 | 473515988.506105 KENYA | 1993 | 473771328.898499 KENYA | 1992 | 485579060.711918 MOROCCO | 1998 | 272661968.744924 MOROCCO | 1997 | 476717479.395091 MOROCCO | 1996 | 466899564.120782 MOROCCO | 1995 | 461650935.327241 MOROCCO | 1994 | 468103468.334873 MOROCCO | 1993 | 468183689.129111 MOROCCO | 1992 | 464831463.936913 MOZAMBIQUE | 1998 | 267689481.156115 MOZAMBIQUE | 1997 | 460721728.239774 MOZAMBIQUE | 1996 | 464870955.864318 MOZAMBIQUE | 1995 | 454043445.744808 MOZAMBIQUE | 1994 | 457409104.809468 MOZAMBIQUE | 1993 | 455858396.810717 MOZAMBIQUE | 1992 | 449733965.17472 PERU | 1998 | 270334645.61063 PERU | 1997 | 459326328.698578 PERU | 1996 | 469481626.628194 PERU | 1995 | 463489644.573679 PERU | 1994 | 459444632.348118 PERU | 1993 | 467913549.933415 PERU | 1992 | 465792973.028057 ROMANIA | 1998 | 271912946.606743 ROMANIA | 1997 | 463766903.560787 ROMANIA | 1996 | 473101704.724565 ROMANIA | 1995 | 467541163.105009 ROMANIA | 1994 | 473835140.152199 ROMANIA | 1993 | 474813668.554781 ROMANIA | 1992 | 469806931.425393 RUSSIA | 1998 | 275891884.205441 RUSSIA | 1997 | 471742107.893804 RUSSIA | 1996 | 473674719.644963 RUSSIA | 1995 | 462551485.430388 RUSSIA | 1994 | 470254919.468406 RUSSIA | 1993 | 463776381.516504 RUSSIA | 1992 | 474345276.05655 SAUDI ARABIA | 1998 | 272784041.882302 SAUDI ARABIA | 1997 | 468267372.76962 SAUDI ARABIA | 1996 | 466927176.924511 SAUDI ARABIA | 1995 | 468938031.199145 SAUDI ARABIA | 1994 | 466606052.803371 SAUDI ARABIA | 1993 | 468729874.302135 SAUDI ARABIA | 1992 | 469278158.020456 UNITED KINGDOM | 1998 | 278070843.680876 UNITED KINGDOM | 1997 | 464843836.458674 UNITED KINGDOM | 1996 | 465032958.071038 UNITED KINGDOM | 1995 | 465007145.181788 UNITED KINGDOM | 1994 | 472667561.092476 UNITED KINGDOM | 1993 | 461371857.870111 UNITED KINGDOM | 1992 | 454036332.299573 UNITED STATES | 1998 | 262968758.923809 UNITED STATES | 1997 | 460433153.188185 UNITED STATES | 1996 | 465279661.302925 UNITED STATES | 1995 | 458327640.774813 UNITED STATES | 1994 | 458523174.150484 UNITED STATES | 1993 | 463636439.634577 UNITED STATES | 1992 | 454420458.25325 VIETNAM | 1998 | 271445087.690683 VIETNAM | 1997 | 465027867.090409 VIETNAM | 1996 | 477948351.093888 VIETNAM | 1995 | 469877511.944911 VIETNAM | 1994 | 464254596.893966 VIETNAM | 1993 | 467966224.894944 VIETNAM | 1992 | 471287134.392418 (175 rows) COMMIT; COMMIT