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 '%lavender%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267594966.230745 ALGERIA | 1997 | 454470095.080246 ALGERIA | 1996 | 464794166.898008 ALGERIA | 1995 | 463927322.670261 ALGERIA | 1994 | 464808997.348657 ALGERIA | 1993 | 465591090.551393 ALGERIA | 1992 | 461565604.795174 ARGENTINA | 1998 | 271332102.893399 ARGENTINA | 1997 | 474240257.434548 ARGENTINA | 1996 | 473527523.035709 ARGENTINA | 1995 | 473954364.935993 ARGENTINA | 1994 | 465616699.591608 ARGENTINA | 1993 | 467729289.093978 ARGENTINA | 1992 | 478081975.277742 BRAZIL | 1998 | 274940735.992173 BRAZIL | 1997 | 469166261.732281 BRAZIL | 1996 | 468337810.966522 BRAZIL | 1995 | 464427502.55029 BRAZIL | 1994 | 464015730.468214 BRAZIL | 1993 | 470795828.336986 BRAZIL | 1992 | 469519873.633752 CANADA | 1998 | 283179326.924181 CANADA | 1997 | 477826334.316928 CANADA | 1996 | 475873109.453055 CANADA | 1995 | 479023450.871019 CANADA | 1994 | 476131180.742344 CANADA | 1993 | 476137797.366418 CANADA | 1992 | 482108731.297946 CHINA | 1998 | 272083001.098856 CHINA | 1997 | 469607875.007573 CHINA | 1996 | 461193344.413387 CHINA | 1995 | 470468588.047012 CHINA | 1994 | 460237686.659337 CHINA | 1993 | 471609485.019845 CHINA | 1992 | 463960885.348712 EGYPT | 1998 | 275294146.966554 EGYPT | 1997 | 465324148.222273 EGYPT | 1996 | 470004383.332147 EGYPT | 1995 | 467453919.932031 EGYPT | 1994 | 468257278.190548 EGYPT | 1993 | 466224060.598684 EGYPT | 1992 | 461039011.359182 ETHIOPIA | 1998 | 268587490.17861 ETHIOPIA | 1997 | 453824402.450643 ETHIOPIA | 1996 | 459996397.849885 ETHIOPIA | 1995 | 467987601.098169 ETHIOPIA | 1994 | 463427413.991859 ETHIOPIA | 1993 | 455751012.318423 ETHIOPIA | 1992 | 459511090.122956 FRANCE | 1998 | 271702467.19756 FRANCE | 1997 | 450543178.114056 FRANCE | 1996 | 461626732.952216 FRANCE | 1995 | 465041833.544058 FRANCE | 1994 | 459234869.38942 FRANCE | 1993 | 458920825.815901 FRANCE | 1992 | 461792353.341931 GERMANY | 1998 | 277826232.96524 GERMANY | 1997 | 478509104.655098 GERMANY | 1996 | 478969929.02284 GERMANY | 1995 | 477071763.780207 GERMANY | 1994 | 483795609.934862 GERMANY | 1993 | 474222949.551652 GERMANY | 1992 | 481774381.311924 INDIA | 1998 | 274065848.461736 INDIA | 1997 | 467233442.452137 INDIA | 1996 | 479022298.326156 INDIA | 1995 | 464306635.430965 INDIA | 1994 | 467808968.652734 INDIA | 1993 | 473992019.529742 INDIA | 1992 | 472264202.971124 INDONESIA | 1998 | 280127582.837126 INDONESIA | 1997 | 475661210.40604 INDONESIA | 1996 | 470790860.626321 INDONESIA | 1995 | 473570159.914933 INDONESIA | 1994 | 476208039.036311 INDONESIA | 1993 | 471277646.199669 INDONESIA | 1992 | 478448007.499572 IRAN | 1998 | 264561339.962149 IRAN | 1997 | 463235099.444242 IRAN | 1996 | 462184250.290584 IRAN | 1995 | 466596868.861293 IRAN | 1994 | 461938500.165069 IRAN | 1993 | 457329628.548327 IRAN | 1992 | 463103978.152766 IRAQ | 1998 | 279991169.2469 IRAQ | 1997 | 472887940.901363 IRAQ | 1996 | 480538959.757628 IRAQ | 1995 | 474217953.260147 IRAQ | 1994 | 479681636.632828 IRAQ | 1993 | 473723084.262714 IRAQ | 1992 | 485319453.426393 JAPAN | 1998 | 274044008.869517 JAPAN | 1997 | 469616943.858516 JAPAN | 1996 | 465560590.532543 JAPAN | 1995 | 474114519.493342 JAPAN | 1994 | 462202557.494323 JAPAN | 1993 | 469525855.20586 JAPAN | 1992 | 469320064.3838 JORDAN | 1998 | 270588463.816371 JORDAN | 1997 | 462903772.923245 JORDAN | 1996 | 463309423.39146 JORDAN | 1995 | 464834160.800865 JORDAN | 1994 | 463545773.784378 JORDAN | 1993 | 469606554.980755 JORDAN | 1992 | 467055746.621909 KENYA | 1998 | 280941588.882434 KENYA | 1997 | 476004253.976211 KENYA | 1996 | 472811720.08745 KENYA | 1995 | 472978795.93382 KENYA | 1994 | 466049375.179543 KENYA | 1993 | 477285072.766312 KENYA | 1992 | 474817695.127031 MOROCCO | 1998 | 268659484.383934 MOROCCO | 1997 | 476465999.35111 MOROCCO | 1996 | 470497605.74642 MOROCCO | 1995 | 475740026.395206 MOROCCO | 1994 | 471472394.710252 MOROCCO | 1993 | 478455628.559811 MOROCCO | 1992 | 475692581.279203 MOZAMBIQUE | 1998 | 271907814.21033 MOZAMBIQUE | 1997 | 459787351.179758 MOZAMBIQUE | 1996 | 460831711.365349 MOZAMBIQUE | 1995 | 462448177.873731 MOZAMBIQUE | 1994 | 461614401.258427 MOZAMBIQUE | 1993 | 462944540.721293 MOZAMBIQUE | 1992 | 468718360.669839 PERU | 1998 | 272958075.25354 PERU | 1997 | 458908044.506622 PERU | 1996 | 473572183.433226 PERU | 1995 | 470203600.86497 PERU | 1994 | 468030602.586064 PERU | 1993 | 466611115.037756 PERU | 1992 | 463612537.395796 ROMANIA | 1998 | 279911101.82941 ROMANIA | 1997 | 474419529.043126 ROMANIA | 1996 | 482529427.316612 ROMANIA | 1995 | 481856207.377614 ROMANIA | 1994 | 478341542.519165 ROMANIA | 1993 | 479420810.890255 ROMANIA | 1992 | 480929099.031569 RUSSIA | 1998 | 278228851.978486 RUSSIA | 1997 | 479953352.807641 RUSSIA | 1996 | 477245572.887639 RUSSIA | 1995 | 468317171.58394 RUSSIA | 1994 | 481469302.605987 RUSSIA | 1993 | 474648227.542384 RUSSIA | 1992 | 473561889.375652 SAUDI ARABIA | 1998 | 281362226.825159 SAUDI ARABIA | 1997 | 482823176.882829 SAUDI ARABIA | 1996 | 476702042.521522 SAUDI ARABIA | 1995 | 476688497.151946 SAUDI ARABIA | 1994 | 476701860.994179 SAUDI ARABIA | 1993 | 472965983.754852 SAUDI ARABIA | 1992 | 476279695.951043 UNITED KINGDOM | 1998 | 276122705.029559 UNITED KINGDOM | 1997 | 468387014.034864 UNITED KINGDOM | 1996 | 469617976.228932 UNITED KINGDOM | 1995 | 468689827.029129 UNITED KINGDOM | 1994 | 469366643.245596 UNITED KINGDOM | 1993 | 462790996.347969 UNITED KINGDOM | 1992 | 465871858.366546 UNITED STATES | 1998 | 275025757.581071 UNITED STATES | 1997 | 468857188.903904 UNITED STATES | 1996 | 471426881.051816 UNITED STATES | 1995 | 472137782.85103 UNITED STATES | 1994 | 477105718.58017 UNITED STATES | 1993 | 472133383.990483 UNITED STATES | 1992 | 471207785.502318 VIETNAM | 1998 | 275185902.685367 VIETNAM | 1997 | 461250609.615483 VIETNAM | 1996 | 470873117.939684 VIETNAM | 1995 | 462665726.079371 VIETNAM | 1994 | 464768086.416517 VIETNAM | 1993 | 461582041.296064 VIETNAM | 1992 | 474340266.646734 (175 rows) COMMIT; COMMIT