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 | 267956756.273964 ALGERIA | 1997 | 454863610.058252 ALGERIA | 1996 | 464918627.523784 ALGERIA | 1995 | 463707849.81308 ALGERIA | 1994 | 464017823.077041 ALGERIA | 1993 | 466098185.581869 ALGERIA | 1992 | 460993836.054808 ARGENTINA | 1998 | 271546687.154838 ARGENTINA | 1997 | 474684869.475878 ARGENTINA | 1996 | 473160658.59056 ARGENTINA | 1995 | 473898024.63092 ARGENTINA | 1994 | 465561255.982644 ARGENTINA | 1993 | 467314979.572119 ARGENTINA | 1992 | 478416867.887575 BRAZIL | 1998 | 275402055.59639 BRAZIL | 1997 | 468630431.667448 BRAZIL | 1996 | 468735445.342445 BRAZIL | 1995 | 464690524.98092 BRAZIL | 1994 | 464289200.836693 BRAZIL | 1993 | 471188502.458633 BRAZIL | 1992 | 469259755.909924 CANADA | 1998 | 282708082.695808 CANADA | 1997 | 477398696.478624 CANADA | 1996 | 476159157.244798 CANADA | 1995 | 480156657.925823 CANADA | 1994 | 476519746.230928 CANADA | 1993 | 476458467.142737 CANADA | 1992 | 481586668.134473 CHINA | 1998 | 272061102.672474 CHINA | 1997 | 469398463.746364 CHINA | 1996 | 461117503.010008 CHINA | 1995 | 470706686.608555 CHINA | 1994 | 460516509.81179 CHINA | 1993 | 471820144.479038 CHINA | 1992 | 464025456.405888 EGYPT | 1998 | 275173072.769755 EGYPT | 1997 | 465141031.912053 EGYPT | 1996 | 470332605.203203 EGYPT | 1995 | 467589418.570992 EGYPT | 1994 | 468156021.694276 EGYPT | 1993 | 466393178.559443 EGYPT | 1992 | 460747138.149344 ETHIOPIA | 1998 | 268520652.711594 ETHIOPIA | 1997 | 454109686.246158 ETHIOPIA | 1996 | 460138441.025977 ETHIOPIA | 1995 | 467642278.909382 ETHIOPIA | 1994 | 463283080.015787 ETHIOPIA | 1993 | 455957274.221587 ETHIOPIA | 1992 | 459575814.215173 FRANCE | 1998 | 271627884.015134 FRANCE | 1997 | 450700979.42938 FRANCE | 1996 | 462253725.212017 FRANCE | 1995 | 464732862.888624 FRANCE | 1994 | 459196086.829506 FRANCE | 1993 | 458641137.466668 FRANCE | 1992 | 462065587.461286 GERMANY | 1998 | 277751789.16334 GERMANY | 1997 | 477974470.407485 GERMANY | 1996 | 478695401.073275 GERMANY | 1995 | 476723457.649936 GERMANY | 1994 | 483981100.244739 GERMANY | 1993 | 474525756.742069 GERMANY | 1992 | 482241837.026991 INDIA | 1998 | 274347548.578537 INDIA | 1997 | 467152346.772617 INDIA | 1996 | 479163134.240421 INDIA | 1995 | 464501717.170469 INDIA | 1994 | 467980398.042469 INDIA | 1993 | 474181702.908112 INDIA | 1992 | 472467868.605183 INDONESIA | 1998 | 279945807.168254 INDONESIA | 1997 | 475800256.392396 INDONESIA | 1996 | 470896727.741689 INDONESIA | 1995 | 473674782.35055 INDONESIA | 1994 | 476330933.501168 INDONESIA | 1993 | 471044911.272642 INDONESIA | 1992 | 478678956.003591 IRAN | 1998 | 264508149.244461 IRAN | 1997 | 463283204.869374 IRAN | 1996 | 462156289.521037 IRAN | 1995 | 466640588.354272 IRAN | 1994 | 462033626.38268 IRAN | 1993 | 457423570.522082 IRAN | 1992 | 462837647.636645 IRAQ | 1998 | 279799299.430624 IRAQ | 1997 | 473213887.736069 IRAQ | 1996 | 480204548.601514 IRAQ | 1995 | 473797258.462324 IRAQ | 1994 | 479226084.224354 IRAQ | 1993 | 474389271.906954 IRAQ | 1992 | 485588638.136723 JAPAN | 1998 | 273962913.849946 JAPAN | 1997 | 469924586.611542 JAPAN | 1996 | 465163921.937867 JAPAN | 1995 | 473718099.768755 JAPAN | 1994 | 462873493.977138 JAPAN | 1993 | 469328182.634351 JAPAN | 1992 | 469700891.410973 JORDAN | 1998 | 270572439.893134 JORDAN | 1997 | 463131800.649061 JORDAN | 1996 | 463443733.9415 JORDAN | 1995 | 465051375.877356 JORDAN | 1994 | 463259234.131416 JORDAN | 1993 | 469467397.796153 JORDAN | 1992 | 466852812.884308 KENYA | 1998 | 280835400.39608 KENYA | 1997 | 476081022.58625 KENYA | 1996 | 472497912.261368 KENYA | 1995 | 473131315.122248 KENYA | 1994 | 466234425.930313 KENYA | 1993 | 477803145.134668 KENYA | 1992 | 474846067.659466 MOROCCO | 1998 | 268637160.959771 MOROCCO | 1997 | 476111889.791353 MOROCCO | 1996 | 471089882.108723 MOROCCO | 1995 | 475280009.334681 MOROCCO | 1994 | 471451023.458626 MOROCCO | 1993 | 478384287.470559 MOROCCO | 1992 | 475590680.579462 MOZAMBIQUE | 1998 | 271986394.77772 MOZAMBIQUE | 1997 | 460416217.873663 MOZAMBIQUE | 1996 | 460796656.775949 MOZAMBIQUE | 1995 | 462172700.626277 MOZAMBIQUE | 1994 | 461850066.459892 MOZAMBIQUE | 1993 | 462664828.423686 MOZAMBIQUE | 1992 | 468375935.401665 PERU | 1998 | 272817777.103159 PERU | 1997 | 457929813.508975 PERU | 1996 | 473589124.462456 PERU | 1995 | 469679202.868462 PERU | 1994 | 467559097.053348 PERU | 1993 | 466905228.476809 PERU | 1992 | 463474579.524292 ROMANIA | 1998 | 279971774.230271 ROMANIA | 1997 | 474531745.092221 ROMANIA | 1996 | 482497673.046352 ROMANIA | 1995 | 482206626.703692 ROMANIA | 1994 | 478533293.905479 ROMANIA | 1993 | 479311403.531417 ROMANIA | 1992 | 481067825.275026 RUSSIA | 1998 | 278502978.218393 RUSSIA | 1997 | 479765706.745877 RUSSIA | 1996 | 477350537.838337 RUSSIA | 1995 | 468261942.674202 RUSSIA | 1994 | 481490032.144156 RUSSIA | 1993 | 474619399.008383 RUSSIA | 1992 | 473463953.040523 SAUDI ARABIA | 1998 | 281125994.283183 SAUDI ARABIA | 1997 | 482243330.433152 SAUDI ARABIA | 1996 | 476441843.707358 SAUDI ARABIA | 1995 | 476785303.179795 SAUDI ARABIA | 1994 | 476532898.303502 SAUDI ARABIA | 1993 | 472723167.118634 SAUDI ARABIA | 1992 | 475871484.091088 UNITED KINGDOM | 1998 | 276473474.09662 UNITED KINGDOM | 1997 | 468529086.026182 UNITED KINGDOM | 1996 | 469344382.853888 UNITED KINGDOM | 1995 | 468175491.539284 UNITED KINGDOM | 1994 | 470217828.629985 UNITED KINGDOM | 1993 | 462894417.271141 UNITED KINGDOM | 1992 | 465840857.219071 UNITED STATES | 1998 | 275155161.287774 UNITED STATES | 1997 | 468809560.149432 UNITED STATES | 1996 | 470984558.098427 UNITED STATES | 1995 | 471569416.229457 UNITED STATES | 1994 | 476412178.72726 UNITED STATES | 1993 | 471774926.801754 UNITED STATES | 1992 | 471534772.018958 VIETNAM | 1998 | 275153352.148143 VIETNAM | 1997 | 460577295.113619 VIETNAM | 1996 | 470707388.948595 VIETNAM | 1995 | 463346188.787533 VIETNAM | 1994 | 465033144.913196 VIETNAM | 1993 | 461756198.477164 VIETNAM | 1992 | 474300407.778617 (175 rows) COMMIT; COMMIT