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 | 273177011.763032 ALGERIA | 1997 | 471719552.629597 ALGERIA | 1996 | 468611899.82486 ALGERIA | 1995 | 456318698.621342 ALGERIA | 1994 | 462245764.310642 ALGERIA | 1993 | 458352078.363536 ALGERIA | 1992 | 459619361.800441 ARGENTINA | 1998 | 274538353.944047 ARGENTINA | 1997 | 470174909.182673 ARGENTINA | 1996 | 472379574.789502 ARGENTINA | 1995 | 473832540.084064 ARGENTINA | 1994 | 473284205.268338 ARGENTINA | 1993 | 472379230.794718 ARGENTINA | 1992 | 469974144.319682 BRAZIL | 1998 | 279760905.366635 BRAZIL | 1997 | 474187170.956646 BRAZIL | 1996 | 469013261.384612 BRAZIL | 1995 | 477578126.64438 BRAZIL | 1994 | 477145813.142107 BRAZIL | 1993 | 469526510.106243 BRAZIL | 1992 | 467241500.68179 CANADA | 1998 | 273671583.578092 CANADA | 1997 | 468344659.868335 CANADA | 1996 | 472867126.645751 CANADA | 1995 | 473502035.532805 CANADA | 1994 | 478968971.898491 CANADA | 1993 | 475966556.78023 CANADA | 1992 | 479932786.746989 CHINA | 1998 | 277009299.109923 CHINA | 1997 | 467661787.707591 CHINA | 1996 | 474338092.089911 CHINA | 1995 | 475330302.579917 CHINA | 1994 | 469597555.881597 CHINA | 1993 | 468476611.300682 CHINA | 1992 | 477873420.08276 EGYPT | 1998 | 274268654.594962 EGYPT | 1997 | 463768137.256093 EGYPT | 1996 | 457031322.316993 EGYPT | 1995 | 466695046.591728 EGYPT | 1994 | 467157121.706282 EGYPT | 1993 | 467623616.4285 EGYPT | 1992 | 465493870.264513 ETHIOPIA | 1998 | 272823028.178064 ETHIOPIA | 1997 | 455026413.099385 ETHIOPIA | 1996 | 459456087.500805 ETHIOPIA | 1995 | 464118390.563872 ETHIOPIA | 1994 | 466209401.754253 ETHIOPIA | 1993 | 462069972.867546 ETHIOPIA | 1992 | 462234137.747632 FRANCE | 1998 | 269689477.430462 FRANCE | 1997 | 458745547.710832 FRANCE | 1996 | 460273599.953952 FRANCE | 1995 | 455362618.980842 FRANCE | 1994 | 460309292.618495 FRANCE | 1993 | 458034825.035346 FRANCE | 1992 | 459318570.685826 GERMANY | 1998 | 281138615.266273 GERMANY | 1997 | 479367153.958944 GERMANY | 1996 | 473433076.89326 GERMANY | 1995 | 489331638.080416 GERMANY | 1994 | 482603075.94751 GERMANY | 1993 | 478553642.488832 GERMANY | 1992 | 483066510.381449 INDIA | 1998 | 277352976.186082 INDIA | 1997 | 475294948.969539 INDIA | 1996 | 479120579.411606 INDIA | 1995 | 479875895.242789 INDIA | 1994 | 475080201.576919 INDIA | 1993 | 479508696.757182 INDIA | 1992 | 483507543.335033 INDONESIA | 1998 | 273933258.238846 INDONESIA | 1997 | 457429604.279426 INDONESIA | 1996 | 468264300.793972 INDONESIA | 1995 | 469294416.615871 INDONESIA | 1994 | 460587695.702077 INDONESIA | 1993 | 474373502.824405 INDONESIA | 1992 | 472748281.551467 IRAN | 1998 | 275763131.609292 IRAN | 1997 | 471994858.677304 IRAN | 1996 | 466963278.702709 IRAN | 1995 | 465281428.100735 IRAN | 1994 | 470459091.492037 IRAN | 1993 | 469872254.263556 IRAN | 1992 | 469123996.000078 IRAQ | 1998 | 275785111.242507 IRAQ | 1997 | 484688352.955582 IRAQ | 1996 | 477375410.78493 IRAQ | 1995 | 473465666.298233 IRAQ | 1994 | 473457695.581858 IRAQ | 1993 | 478054515.539782 IRAQ | 1992 | 473916978.190515 JAPAN | 1998 | 270945237.51316 JAPAN | 1997 | 472170504.385228 JAPAN | 1996 | 470719444.388418 JAPAN | 1995 | 464153124.98473 JAPAN | 1994 | 459019411.529249 JAPAN | 1993 | 469485257.376538 JAPAN | 1992 | 463225781.46004 JORDAN | 1998 | 274136736.588205 JORDAN | 1997 | 466417177.605431 JORDAN | 1996 | 458853319.544436 JORDAN | 1995 | 463247187.627295 JORDAN | 1994 | 467936517.164377 JORDAN | 1993 | 473351299.037092 JORDAN | 1992 | 461666353.910553 KENYA | 1998 | 278405358.989421 KENYA | 1997 | 475116249.73019 KENYA | 1996 | 478213489.840867 KENYA | 1995 | 479786637.387087 KENYA | 1994 | 468657685.463608 KENYA | 1993 | 466766391.186376 KENYA | 1992 | 478673277.784793 MOROCCO | 1998 | 282380612.595875 MOROCCO | 1997 | 468706071.119189 MOROCCO | 1996 | 469606715.878892 MOROCCO | 1995 | 475797707.62089 MOROCCO | 1994 | 475425144.667769 MOROCCO | 1993 | 467077140.513544 MOROCCO | 1992 | 467408523.068861 MOZAMBIQUE | 1998 | 271649643.64674 MOZAMBIQUE | 1997 | 465668078.974903 MOZAMBIQUE | 1996 | 462936983.790231 MOZAMBIQUE | 1995 | 459302176.026976 MOZAMBIQUE | 1994 | 465098239.45745 MOZAMBIQUE | 1993 | 463377072.017995 MOZAMBIQUE | 1992 | 460757091.786428 PERU | 1998 | 273685410.755176 PERU | 1997 | 467248885.449165 PERU | 1996 | 468416387.020285 PERU | 1995 | 466751460.41474 PERU | 1994 | 469363334.017057 PERU | 1993 | 470601558.213887 PERU | 1992 | 470613408.840407 ROMANIA | 1998 | 276726784.691277 ROMANIA | 1997 | 477009137.896654 ROMANIA | 1996 | 482643094.045174 ROMANIA | 1995 | 466194476.986104 ROMANIA | 1994 | 464989374.65292 ROMANIA | 1993 | 467235189.473741 ROMANIA | 1992 | 470885295.253223 RUSSIA | 1998 | 280363997.489777 RUSSIA | 1997 | 475084175.996049 RUSSIA | 1996 | 473488189.394864 RUSSIA | 1995 | 478752387.144507 RUSSIA | 1994 | 470368190.268455 RUSSIA | 1993 | 480273609.911526 RUSSIA | 1992 | 474066565.942086 SAUDI ARABIA | 1998 | 279651622.944699 SAUDI ARABIA | 1997 | 471798482.144899 SAUDI ARABIA | 1996 | 472703625.107342 SAUDI ARABIA | 1995 | 468172580.419059 SAUDI ARABIA | 1994 | 467961529.49155 SAUDI ARABIA | 1993 | 469432336.832888 SAUDI ARABIA | 1992 | 468556957.93156 UNITED KINGDOM | 1998 | 264871425.24716 UNITED KINGDOM | 1997 | 457418991.874843 UNITED KINGDOM | 1996 | 466345895.617416 UNITED KINGDOM | 1995 | 459631191.921806 UNITED KINGDOM | 1994 | 462513384.520041 UNITED KINGDOM | 1993 | 459542980.09926 UNITED KINGDOM | 1992 | 460081460.305379 UNITED STATES | 1998 | 285059147.146089 UNITED STATES | 1997 | 470732767.23133 UNITED STATES | 1996 | 477424105.623508 UNITED STATES | 1995 | 474977739.119012 UNITED STATES | 1994 | 480003492.77185 UNITED STATES | 1993 | 473175715.883885 UNITED STATES | 1992 | 476890755.784244 VIETNAM | 1998 | 271524131.43439 VIETNAM | 1997 | 466020623.907494 VIETNAM | 1996 | 479165324.262039 VIETNAM | 1995 | 464425279.689303 VIETNAM | 1994 | 470774416.346368 VIETNAM | 1993 | 475092842.700024 VIETNAM | 1992 | 469564457.737752 (175 rows) COMMIT; COMMIT