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 '%magenta%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 263668443.518403 ALGERIA | 1997 | 459605909.267567 ALGERIA | 1996 | 467335653.017505 ALGERIA | 1995 | 463963949.276279 ALGERIA | 1994 | 460527047.92579 ALGERIA | 1993 | 460499555.764876 ALGERIA | 1992 | 452754382.376476 ARGENTINA | 1998 | 276050407.860374 ARGENTINA | 1997 | 467073369.145035 ARGENTINA | 1996 | 478374548.39977 ARGENTINA | 1995 | 472192081.937908 ARGENTINA | 1994 | 469335057.728652 ARGENTINA | 1993 | 464803041.278803 ARGENTINA | 1992 | 470217687.920886 BRAZIL | 1998 | 271746001.009375 BRAZIL | 1997 | 467597035.452017 BRAZIL | 1996 | 462305196.16685 BRAZIL | 1995 | 464635597.501544 BRAZIL | 1994 | 464396417.545537 BRAZIL | 1993 | 467008374.213548 BRAZIL | 1992 | 465363899.438673 CANADA | 1998 | 275144222.641291 CANADA | 1997 | 475575276.665132 CANADA | 1996 | 475610865.916518 CANADA | 1995 | 480502233.639934 CANADA | 1994 | 479241409.393636 CANADA | 1993 | 472076212.459753 CANADA | 1992 | 473359708.06223 CHINA | 1998 | 274448578.951482 CHINA | 1997 | 462256819.091618 CHINA | 1996 | 466365108.701962 CHINA | 1995 | 454803125.876565 CHINA | 1994 | 457730354.709245 CHINA | 1993 | 463370455.185238 CHINA | 1992 | 467761804.136509 EGYPT | 1998 | 272865657.076823 EGYPT | 1997 | 476795867.016163 EGYPT | 1996 | 471545088.838647 EGYPT | 1995 | 461338482.069848 EGYPT | 1994 | 469208646.94177 EGYPT | 1993 | 461497261.013378 EGYPT | 1992 | 466547048.585214 ETHIOPIA | 1998 | 269597759.061062 ETHIOPIA | 1997 | 453269450.5254 ETHIOPIA | 1996 | 453699507.564663 ETHIOPIA | 1995 | 460314657.772107 ETHIOPIA | 1994 | 454082113.802616 ETHIOPIA | 1993 | 458797930.478931 ETHIOPIA | 1992 | 452096724.536945 FRANCE | 1998 | 271794455.093476 FRANCE | 1997 | 458864529.02636 FRANCE | 1996 | 463069547.460943 FRANCE | 1995 | 466866517.610812 FRANCE | 1994 | 462796757.597699 FRANCE | 1993 | 462426207.569944 FRANCE | 1992 | 452970714.045943 GERMANY | 1998 | 279685117.984525 GERMANY | 1997 | 481773865.402953 GERMANY | 1996 | 478337569.567796 GERMANY | 1995 | 474510443.680389 GERMANY | 1994 | 472474746.684677 GERMANY | 1993 | 480871811.245792 GERMANY | 1992 | 478906313.966932 INDIA | 1998 | 276484942.478583 INDIA | 1997 | 474254787.05572 INDIA | 1996 | 474381966.419831 INDIA | 1995 | 475119147.632251 INDIA | 1994 | 473892474.927232 INDIA | 1993 | 476074525.710693 INDIA | 1992 | 474200472.845305 INDONESIA | 1998 | 267895399.901944 INDONESIA | 1997 | 456208455.035952 INDONESIA | 1996 | 464941886.789324 INDONESIA | 1995 | 459760050.413906 INDONESIA | 1994 | 449972587.752767 INDONESIA | 1993 | 459756770.582206 INDONESIA | 1992 | 459433213.355202 IRAN | 1998 | 267066712.94831 IRAN | 1997 | 463394678.23519 IRAN | 1996 | 464823938.09256 IRAN | 1995 | 458166790.460605 IRAN | 1994 | 467859932.797832 IRAN | 1993 | 468754527.04963 IRAN | 1992 | 456078887.302722 IRAQ | 1998 | 271297361.266195 IRAQ | 1997 | 463425314.906282 IRAQ | 1996 | 460154719.033826 IRAQ | 1995 | 468333734.810381 IRAQ | 1994 | 467311869.944285 IRAQ | 1993 | 465953975.630864 IRAQ | 1992 | 477858113.303387 JAPAN | 1998 | 274036629.550241 JAPAN | 1997 | 469012777.879403 JAPAN | 1996 | 464181371.570694 JAPAN | 1995 | 466423587.169977 JAPAN | 1994 | 473919664.243184 JAPAN | 1993 | 463785613.82787 JAPAN | 1992 | 465303279.56559 JORDAN | 1998 | 265554792.039081 JORDAN | 1997 | 451535810.869453 JORDAN | 1996 | 449698986.172798 JORDAN | 1995 | 454801739.897557 JORDAN | 1994 | 440844997.550453 JORDAN | 1993 | 458572081.877845 JORDAN | 1992 | 446652270.786449 KENYA | 1998 | 273358951.690256 KENYA | 1997 | 462834489.804462 KENYA | 1996 | 466107730.26647 KENYA | 1995 | 463306212.696914 KENYA | 1994 | 464734580.636109 KENYA | 1993 | 465549234.884261 KENYA | 1992 | 466983398.352349 MOROCCO | 1998 | 269301706.330243 MOROCCO | 1997 | 465202053.117157 MOROCCO | 1996 | 473223041.245042 MOROCCO | 1995 | 465980738.056579 MOROCCO | 1994 | 471484301.293758 MOROCCO | 1993 | 465181624.993509 MOROCCO | 1992 | 475487917.273299 MOZAMBIQUE | 1998 | 266393257.279769 MOZAMBIQUE | 1997 | 465157032.620849 MOZAMBIQUE | 1996 | 456258436.273916 MOZAMBIQUE | 1995 | 456381734.466503 MOZAMBIQUE | 1994 | 459280609.772551 MOZAMBIQUE | 1993 | 458942743.712092 MOZAMBIQUE | 1992 | 460075973.279975 PERU | 1998 | 271135846.15856 PERU | 1997 | 466151717.512116 PERU | 1996 | 456029010.705748 PERU | 1995 | 460360514.946275 PERU | 1994 | 460365835.547971 PERU | 1993 | 467230624.117948 PERU | 1992 | 466306984.491658 ROMANIA | 1998 | 269897683.508475 ROMANIA | 1997 | 467486838.369064 ROMANIA | 1996 | 475866694.730563 ROMANIA | 1995 | 463811720.896497 ROMANIA | 1994 | 470429411.239367 ROMANIA | 1993 | 465950802.034457 ROMANIA | 1992 | 479560634.263328 RUSSIA | 1998 | 279027085.482541 RUSSIA | 1997 | 469639287.080992 RUSSIA | 1996 | 471759745.160858 RUSSIA | 1995 | 460746051.133034 RUSSIA | 1994 | 470709638.932799 RUSSIA | 1993 | 467490803.786402 RUSSIA | 1992 | 474587695.667805 SAUDI ARABIA | 1998 | 272074648.417069 SAUDI ARABIA | 1997 | 470162735.267156 SAUDI ARABIA | 1996 | 471179104.215799 SAUDI ARABIA | 1995 | 475565789.093015 SAUDI ARABIA | 1994 | 463250802.547637 SAUDI ARABIA | 1993 | 471374227.641917 SAUDI ARABIA | 1992 | 473123887.925905 UNITED KINGDOM | 1998 | 264607432.733926 UNITED KINGDOM | 1997 | 464220348.892433 UNITED KINGDOM | 1996 | 455440519.708292 UNITED KINGDOM | 1995 | 453869727.405524 UNITED KINGDOM | 1994 | 454440052.334348 UNITED KINGDOM | 1993 | 464722023.447548 UNITED KINGDOM | 1992 | 462330503.317367 UNITED STATES | 1998 | 276440781.146712 UNITED STATES | 1997 | 473432402.248819 UNITED STATES | 1996 | 472474397.888726 UNITED STATES | 1995 | 471365212.713526 UNITED STATES | 1994 | 469238620.089557 UNITED STATES | 1993 | 471632313.418155 UNITED STATES | 1992 | 473797211.243513 VIETNAM | 1998 | 274801586.250732 VIETNAM | 1997 | 463474954.314965 VIETNAM | 1996 | 466358320.841685 VIETNAM | 1995 | 467895486.527651 VIETNAM | 1994 | 465905841.137828 VIETNAM | 1993 | 469361474.952394 VIETNAM | 1992 | 469277257.583954 (175 rows) COMMIT; COMMIT