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 | 267684641.392987 ALGERIA | 1997 | 454506614.731457 ALGERIA | 1996 | 464688854.831821 ALGERIA | 1995 | 463835416.459345 ALGERIA | 1994 | 464358080.589337 ALGERIA | 1993 | 466017695.811789 ALGERIA | 1992 | 461375369.204133 ARGENTINA | 1998 | 271520542.026798 ARGENTINA | 1997 | 474491993.820184 ARGENTINA | 1996 | 473847513.017053 ARGENTINA | 1995 | 473908547.113304 ARGENTINA | 1994 | 465788605.404616 ARGENTINA | 1993 | 467658486.557151 ARGENTINA | 1992 | 478022766.980251 BRAZIL | 1998 | 275066037.969261 BRAZIL | 1997 | 468893981.906949 BRAZIL | 1996 | 468664515.644353 BRAZIL | 1995 | 464423759.053144 BRAZIL | 1994 | 464219586.464315 BRAZIL | 1993 | 470885427.803531 BRAZIL | 1992 | 469510639.329106 CANADA | 1998 | 283033694.263886 CANADA | 1997 | 477791636.860861 CANADA | 1996 | 476341747.761846 CANADA | 1995 | 479402002.323978 CANADA | 1994 | 476107530.185615 CANADA | 1993 | 476142412.907482 CANADA | 1992 | 481726947.593697 CHINA | 1998 | 272133699.512031 CHINA | 1997 | 469645564.966185 CHINA | 1996 | 461066509.475341 CHINA | 1995 | 470515087.858752 CHINA | 1994 | 460293040.622698 CHINA | 1993 | 471536552.68734 CHINA | 1992 | 464083519.817962 EGYPT | 1998 | 275425341.940926 EGYPT | 1997 | 465111482.210982 EGYPT | 1996 | 470014793.236312 EGYPT | 1995 | 467450383.435452 EGYPT | 1994 | 468157830.242449 EGYPT | 1993 | 466036063.310152 EGYPT | 1992 | 461016268.613558 ETHIOPIA | 1998 | 268504126.368163 ETHIOPIA | 1997 | 453861329.804899 ETHIOPIA | 1996 | 459959928.783702 ETHIOPIA | 1995 | 468183855.986962 ETHIOPIA | 1994 | 463382620.466561 ETHIOPIA | 1993 | 455712267.005587 ETHIOPIA | 1992 | 459242532.705395 FRANCE | 1998 | 271815889.08778 FRANCE | 1997 | 450508255.506687 FRANCE | 1996 | 461744802.474793 FRANCE | 1995 | 464685363.80212 FRANCE | 1994 | 459084669.343248 FRANCE | 1993 | 458859291.118919 FRANCE | 1992 | 461990150.972806 GERMANY | 1998 | 277600503.945807 GERMANY | 1997 | 478500998.247412 GERMANY | 1996 | 479083793.207403 GERMANY | 1995 | 476943735.244653 GERMANY | 1994 | 483908200.561936 GERMANY | 1993 | 474406496.473974 GERMANY | 1992 | 481598981.21284 INDIA | 1998 | 274283602.081161 INDIA | 1997 | 467315282.067573 INDIA | 1996 | 478950571.05363 INDIA | 1995 | 464354635.42787 INDIA | 1994 | 467759228.903455 INDIA | 1993 | 473929632.036645 INDIA | 1992 | 472467720.525607 INDONESIA | 1998 | 280064218.354254 INDONESIA | 1997 | 475633016.660143 INDONESIA | 1996 | 470940520.661168 INDONESIA | 1995 | 473371200.128853 INDONESIA | 1994 | 476458491.544587 INDONESIA | 1993 | 470986903.005539 INDONESIA | 1992 | 478603920.827747 IRAN | 1998 | 264676136.065816 IRAN | 1997 | 463163397.718195 IRAN | 1996 | 461778754.75033 IRAN | 1995 | 466901396.641663 IRAN | 1994 | 462049244.408961 IRAN | 1993 | 457428349.448605 IRAN | 1992 | 462824489.160757 IRAQ | 1998 | 279865161.963662 IRAQ | 1997 | 473060778.835948 IRAQ | 1996 | 480370367.901972 IRAQ | 1995 | 474164842.410536 IRAQ | 1994 | 479474170.728744 IRAQ | 1993 | 473819331.474158 IRAQ | 1992 | 485239652.230962 JAPAN | 1998 | 274108099.717857 JAPAN | 1997 | 469881181.437584 JAPAN | 1996 | 465538987.594271 JAPAN | 1995 | 473791553.364177 JAPAN | 1994 | 462403028.553703 JAPAN | 1993 | 469370477.772824 JAPAN | 1992 | 469559983.911485 JORDAN | 1998 | 270655920.801961 JORDAN | 1997 | 462802499.467314 JORDAN | 1996 | 463613856.731748 JORDAN | 1995 | 464458400.9486 JORDAN | 1994 | 463581489.583607 JORDAN | 1993 | 469557868.923549 JORDAN | 1992 | 467087408.862335 KENYA | 1998 | 280991069.068056 KENYA | 1997 | 475948534.671233 KENYA | 1996 | 472803881.913744 KENYA | 1995 | 473286810.816674 KENYA | 1994 | 466120289.08547 KENYA | 1993 | 477567259.689372 KENYA | 1992 | 474889087.388955 MOROCCO | 1998 | 268866479.834374 MOROCCO | 1997 | 476648862.071978 MOROCCO | 1996 | 470757668.224875 MOROCCO | 1995 | 475447047.479735 MOROCCO | 1994 | 471389107.427501 MOROCCO | 1993 | 478765200.993189 MOROCCO | 1992 | 475581058.527477 MOZAMBIQUE | 1998 | 271688091.950203 MOZAMBIQUE | 1997 | 459875003.174465 MOZAMBIQUE | 1996 | 461030424.812349 MOZAMBIQUE | 1995 | 461933177.863665 MOZAMBIQUE | 1994 | 461404337.609055 MOZAMBIQUE | 1993 | 462781550.523586 MOZAMBIQUE | 1992 | 468608399.456188 PERU | 1998 | 272798801.627602 PERU | 1997 | 458738522.085172 PERU | 1996 | 473718281.167189 PERU | 1995 | 470063890.077871 PERU | 1994 | 467860760.87591 PERU | 1993 | 467092982.082008 PERU | 1992 | 463692015.781039 ROMANIA | 1998 | 280068169.264392 ROMANIA | 1997 | 474412685.298472 ROMANIA | 1996 | 482689698.51808 ROMANIA | 1995 | 481884495.848314 ROMANIA | 1994 | 478574000.040787 ROMANIA | 1993 | 479300203.019089 ROMANIA | 1992 | 480504060.320913 RUSSIA | 1998 | 278278164.126632 RUSSIA | 1997 | 479710982.921681 RUSSIA | 1996 | 477181065.948781 RUSSIA | 1995 | 468305465.267083 RUSSIA | 1994 | 481430342.603882 RUSSIA | 1993 | 474614218.993616 RUSSIA | 1992 | 473235319.630699 SAUDI ARABIA | 1998 | 281216672.08873 SAUDI ARABIA | 1997 | 482475917.585201 SAUDI ARABIA | 1996 | 476754080.024628 SAUDI ARABIA | 1995 | 476779208.083246 SAUDI ARABIA | 1994 | 476624541.643062 SAUDI ARABIA | 1993 | 472839795.653494 SAUDI ARABIA | 1992 | 475912808.411437 UNITED KINGDOM | 1998 | 276047362.951899 UNITED KINGDOM | 1997 | 468322243.483573 UNITED KINGDOM | 1996 | 469594395.841016 UNITED KINGDOM | 1995 | 468508307.845057 UNITED KINGDOM | 1994 | 469543439.888609 UNITED KINGDOM | 1993 | 462630122.565444 UNITED KINGDOM | 1992 | 465888176.822241 UNITED STATES | 1998 | 275059585.32905 UNITED STATES | 1997 | 468853108.42366 UNITED STATES | 1996 | 471337721.393436 UNITED STATES | 1995 | 472159322.728301 UNITED STATES | 1994 | 476994031.450021 UNITED STATES | 1993 | 471834247.888555 UNITED STATES | 1992 | 471284145.057576 VIETNAM | 1998 | 275155112.003049 VIETNAM | 1997 | 460977747.067057 VIETNAM | 1996 | 470967270.247276 VIETNAM | 1995 | 462961750.415642 VIETNAM | 1994 | 464863326.586552 VIETNAM | 1993 | 461677174.553763 VIETNAM | 1992 | 474237774.761369 (175 rows) COMMIT; COMMIT