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 '%rose%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267803114.183115 ALGERIA | 1997 | 448419160.438514 ALGERIA | 1996 | 463573907.112901 ALGERIA | 1995 | 450763234.46386 ALGERIA | 1994 | 457004417.209269 ALGERIA | 1993 | 460449590.73968 ALGERIA | 1992 | 466699250.977321 ARGENTINA | 1998 | 272949788.31748 ARGENTINA | 1997 | 463766142.473772 ARGENTINA | 1996 | 460641965.105882 ARGENTINA | 1995 | 460910717.773584 ARGENTINA | 1994 | 469506190.017543 ARGENTINA | 1993 | 464559048.594472 ARGENTINA | 1992 | 456763222.667431 BRAZIL | 1998 | 272869568.376527 BRAZIL | 1997 | 457716164.702921 BRAZIL | 1996 | 463642977.023744 BRAZIL | 1995 | 455727071.226245 BRAZIL | 1994 | 462374146.005341 BRAZIL | 1993 | 464208806.910867 BRAZIL | 1992 | 461320018.187722 CANADA | 1998 | 275047272.128054 CANADA | 1997 | 470299823.733286 CANADA | 1996 | 471662695.448543 CANADA | 1995 | 474699857.103793 CANADA | 1994 | 471935926.092928 CANADA | 1993 | 474874557.139917 CANADA | 1992 | 478146179.150599 CHINA | 1998 | 271944478.047907 CHINA | 1997 | 461855014.43867 CHINA | 1996 | 455878638.591158 CHINA | 1995 | 467311425.907081 CHINA | 1994 | 464810105.785374 CHINA | 1993 | 459758215.514533 CHINA | 1992 | 468023312.526573 EGYPT | 1998 | 272281282.84177 EGYPT | 1997 | 461455628.383155 EGYPT | 1996 | 470273090.973004 EGYPT | 1995 | 457824463.640634 EGYPT | 1994 | 464340563.049598 EGYPT | 1993 | 465205874.772992 EGYPT | 1992 | 458129767.350432 ETHIOPIA | 1998 | 269533178.904091 ETHIOPIA | 1997 | 456563993.780461 ETHIOPIA | 1996 | 451981442.943077 ETHIOPIA | 1995 | 450652757.081879 ETHIOPIA | 1994 | 464143331.013687 ETHIOPIA | 1993 | 454390227.497982 ETHIOPIA | 1992 | 458755204.477582 FRANCE | 1998 | 267824584.999098 FRANCE | 1997 | 456896027.363616 FRANCE | 1996 | 460854839.432962 FRANCE | 1995 | 452935845.252711 FRANCE | 1994 | 463627311.142511 FRANCE | 1993 | 464901501.877727 FRANCE | 1992 | 457873184.262749 GERMANY | 1998 | 281062179.607009 GERMANY | 1997 | 475864033.751679 GERMANY | 1996 | 483903189.730114 GERMANY | 1995 | 476466777.80692 GERMANY | 1994 | 475937045.254378 GERMANY | 1993 | 482363820.497159 GERMANY | 1992 | 477283404.230373 INDIA | 1998 | 278715665.889457 INDIA | 1997 | 476829156.757113 INDIA | 1996 | 476841121.09121 INDIA | 1995 | 479246262.367005 INDIA | 1994 | 477819530.361773 INDIA | 1993 | 480296553.029385 INDIA | 1992 | 478164166.93643 INDONESIA | 1998 | 272188926.692364 INDONESIA | 1997 | 457234224.481227 INDONESIA | 1996 | 455394958.545575 INDONESIA | 1995 | 452989629.863875 INDONESIA | 1994 | 460292302.585776 INDONESIA | 1993 | 456922397.842239 INDONESIA | 1992 | 460101000.545793 IRAN | 1998 | 269718674.208396 IRAN | 1997 | 467927440.74095 IRAN | 1996 | 461461609.802876 IRAN | 1995 | 465865509.920818 IRAN | 1994 | 464762490.809435 IRAN | 1993 | 458660190.739562 IRAN | 1992 | 468456786.516719 IRAQ | 1998 | 281973579.54234 IRAQ | 1997 | 468593448.654562 IRAQ | 1996 | 476358159.087055 IRAQ | 1995 | 477983024.023158 IRAQ | 1994 | 473454313.251226 IRAQ | 1993 | 470271032.556681 IRAQ | 1992 | 473307326.511595 JAPAN | 1998 | 265340872.416134 JAPAN | 1997 | 468247649.896012 JAPAN | 1996 | 464364059.911481 JAPAN | 1995 | 466855304.792365 JAPAN | 1994 | 466286774.787248 JAPAN | 1993 | 463969690.597779 JAPAN | 1992 | 468790559.133891 JORDAN | 1998 | 275318657.797194 JORDAN | 1997 | 463004917.277087 JORDAN | 1996 | 463906177.514371 JORDAN | 1995 | 464934637.983076 JORDAN | 1994 | 469565636.863587 JORDAN | 1993 | 457975072.328628 JORDAN | 1992 | 459046110.048553 KENYA | 1998 | 274115946.719082 KENYA | 1997 | 463182666.715253 KENYA | 1996 | 468255731.987771 KENYA | 1995 | 462895635.48399 KENYA | 1994 | 462831700.531516 KENYA | 1993 | 466493978.213526 KENYA | 1992 | 462065992.61237 MOROCCO | 1998 | 268816571.161479 MOROCCO | 1997 | 465052712.173267 MOROCCO | 1996 | 468571187.529433 MOROCCO | 1995 | 468876755.018412 MOROCCO | 1994 | 472504331.417296 MOROCCO | 1993 | 475712055.862924 MOROCCO | 1992 | 468005323.290422 MOZAMBIQUE | 1998 | 271566037.964607 MOZAMBIQUE | 1997 | 461058526.548936 MOZAMBIQUE | 1996 | 457976763.81482 MOZAMBIQUE | 1995 | 459069862.222814 MOZAMBIQUE | 1994 | 466653502.07774 MOZAMBIQUE | 1993 | 459059671.299847 MOZAMBIQUE | 1992 | 460868927.493008 PERU | 1998 | 275764352.945523 PERU | 1997 | 458345904.041117 PERU | 1996 | 462291142.598607 PERU | 1995 | 460187876.602174 PERU | 1994 | 460869979.558642 PERU | 1993 | 460352570.900379 PERU | 1992 | 466941617.101548 ROMANIA | 1998 | 274942272.648073 ROMANIA | 1997 | 463153874.108201 ROMANIA | 1996 | 473143164.606726 ROMANIA | 1995 | 469385865.93396 ROMANIA | 1994 | 466392362.153045 ROMANIA | 1993 | 470725299.994323 ROMANIA | 1992 | 472907750.999315 RUSSIA | 1998 | 274595852.425171 RUSSIA | 1997 | 469923064.897732 RUSSIA | 1996 | 474198345.866508 RUSSIA | 1995 | 465690942.756608 RUSSIA | 1994 | 461918399.28626 RUSSIA | 1993 | 457332425.968902 RUSSIA | 1992 | 469786270.191951 SAUDI ARABIA | 1998 | 284468545.910078 SAUDI ARABIA | 1997 | 475637424.263028 SAUDI ARABIA | 1996 | 469792088.557649 SAUDI ARABIA | 1995 | 482883418.303802 SAUDI ARABIA | 1994 | 475705276.539652 SAUDI ARABIA | 1993 | 474154029.321402 SAUDI ARABIA | 1992 | 474350278.413658 UNITED KINGDOM | 1998 | 269120223.779374 UNITED KINGDOM | 1997 | 452232547.704262 UNITED KINGDOM | 1996 | 449877258.018813 UNITED KINGDOM | 1995 | 449907297.327361 UNITED KINGDOM | 1994 | 453979552.887067 UNITED KINGDOM | 1993 | 447863617.430337 UNITED KINGDOM | 1992 | 450462972.241106 UNITED STATES | 1998 | 270083387.245282 UNITED STATES | 1997 | 463186141.076979 UNITED STATES | 1996 | 462182642.872752 UNITED STATES | 1995 | 457893310.481523 UNITED STATES | 1994 | 463345027.087164 UNITED STATES | 1993 | 468991528.809428 UNITED STATES | 1992 | 467898982.401617 VIETNAM | 1998 | 275730925.164887 VIETNAM | 1997 | 463056671.304388 VIETNAM | 1996 | 464561879.442497 VIETNAM | 1995 | 462289684.506717 VIETNAM | 1994 | 463466413.945056 VIETNAM | 1993 | 464214649.770044 VIETNAM | 1992 | 463379177.00247 (175 rows) COMMIT; COMMIT