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 '%light%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267724641.108522 ALGERIA | 1997 | 441003443.720446 ALGERIA | 1996 | 451179537.502324 ALGERIA | 1995 | 456403130.606212 ALGERIA | 1994 | 454270406.91295 ALGERIA | 1993 | 452005319.087709 ALGERIA | 1992 | 449755901.998882 ARGENTINA | 1998 | 271954725.933075 ARGENTINA | 1997 | 459770964.73349 ARGENTINA | 1996 | 464541122.88388 ARGENTINA | 1995 | 467633666.841304 ARGENTINA | 1994 | 475409298.879384 ARGENTINA | 1993 | 464129279.40489 ARGENTINA | 1992 | 465377326.541361 BRAZIL | 1998 | 274841516.942212 BRAZIL | 1997 | 465725579.184153 BRAZIL | 1996 | 471057133.054494 BRAZIL | 1995 | 473114845.962629 BRAZIL | 1994 | 471040913.261196 BRAZIL | 1993 | 468086718.491491 BRAZIL | 1992 | 479203370.660573 CANADA | 1998 | 279033723.946325 CANADA | 1997 | 466531691.039002 CANADA | 1996 | 479408123.546394 CANADA | 1995 | 468092292.17004 CANADA | 1994 | 469870185.174827 CANADA | 1993 | 467350263.638692 CANADA | 1992 | 473074139.942543 CHINA | 1998 | 272916217.22379 CHINA | 1997 | 461967543.937884 CHINA | 1996 | 466595012.528608 CHINA | 1995 | 462350976.165134 CHINA | 1994 | 454480595.383206 CHINA | 1993 | 461498977.701057 CHINA | 1992 | 465886643.429757 EGYPT | 1998 | 273777246.546427 EGYPT | 1997 | 459234616.972714 EGYPT | 1996 | 460604058.515631 EGYPT | 1995 | 460708224.16296 EGYPT | 1994 | 470015534.913847 EGYPT | 1993 | 466608571.423215 EGYPT | 1992 | 463181852.30155 ETHIOPIA | 1998 | 271847420.284718 ETHIOPIA | 1997 | 456279151.883199 ETHIOPIA | 1996 | 461308859.918594 ETHIOPIA | 1995 | 463954473.499417 ETHIOPIA | 1994 | 456468073.370697 ETHIOPIA | 1993 | 462098736.154919 ETHIOPIA | 1992 | 455725583.753526 FRANCE | 1998 | 271839277.370771 FRANCE | 1997 | 466141526.657414 FRANCE | 1996 | 473148278.110608 FRANCE | 1995 | 458007395.546396 FRANCE | 1994 | 469278332.5944 FRANCE | 1993 | 464907617.78932 FRANCE | 1992 | 466217900.434218 GERMANY | 1998 | 275292107.173711 GERMANY | 1997 | 478347050.409898 GERMANY | 1996 | 482902846.319109 GERMANY | 1995 | 478316861.206427 GERMANY | 1994 | 472282312.784843 GERMANY | 1993 | 477083290.924433 GERMANY | 1992 | 480846558.310802 INDIA | 1998 | 276186021.869708 INDIA | 1997 | 474548264.654013 INDIA | 1996 | 470204023.44296 INDIA | 1995 | 462871246.175599 INDIA | 1994 | 472378067.102576 INDIA | 1993 | 472276402.593323 INDIA | 1992 | 468492696.634267 INDONESIA | 1998 | 269898087.560923 INDONESIA | 1997 | 461459318.156189 INDONESIA | 1996 | 461504552.587536 INDONESIA | 1995 | 460758380.68864 INDONESIA | 1994 | 467912444.680132 INDONESIA | 1993 | 464390145.530668 INDONESIA | 1992 | 465018212.555814 IRAN | 1998 | 274359293.111467 IRAN | 1997 | 468611950.538233 IRAN | 1996 | 466597092.669628 IRAN | 1995 | 467943539.940691 IRAN | 1994 | 470203318.840002 IRAN | 1993 | 463642835.371496 IRAN | 1992 | 475727672.305089 IRAQ | 1998 | 278166018.700986 IRAQ | 1997 | 470152098.823475 IRAQ | 1996 | 474375543.023161 IRAQ | 1995 | 464074189.90367 IRAQ | 1994 | 482158453.627123 IRAQ | 1993 | 475095078.943921 IRAQ | 1992 | 469934618.930683 JAPAN | 1998 | 264922245.658812 JAPAN | 1997 | 466724541.068879 JAPAN | 1996 | 459760300.126897 JAPAN | 1995 | 454647436.330482 JAPAN | 1994 | 459104725.408726 JAPAN | 1993 | 462772810.220065 JAPAN | 1992 | 464318207.387959 JORDAN | 1998 | 273083833.01099 JORDAN | 1997 | 460567291.218097 JORDAN | 1996 | 467137011.383687 JORDAN | 1995 | 458130203.52089 JORDAN | 1994 | 461078707.381388 JORDAN | 1993 | 461297917.694243 JORDAN | 1992 | 458075966.127371 KENYA | 1998 | 273770517.293672 KENYA | 1997 | 470119117.279485 KENYA | 1996 | 476146774.958186 KENYA | 1995 | 471990755.687446 KENYA | 1994 | 470098237.873592 KENYA | 1993 | 476376741.900133 KENYA | 1992 | 465068556.010327 MOROCCO | 1998 | 268108568.799476 MOROCCO | 1997 | 475911144.997261 MOROCCO | 1996 | 465645012.520006 MOROCCO | 1995 | 470908756.422729 MOROCCO | 1994 | 474499140.72094 MOROCCO | 1993 | 466614310.693484 MOROCCO | 1992 | 470128555.310531 MOZAMBIQUE | 1998 | 272523453.572551 MOZAMBIQUE | 1997 | 459908437.389833 MOZAMBIQUE | 1996 | 466165490.409633 MOZAMBIQUE | 1995 | 462338461.685805 MOZAMBIQUE | 1994 | 464518292.116313 MOZAMBIQUE | 1993 | 467157026.67704 MOZAMBIQUE | 1992 | 457277939.662275 PERU | 1998 | 274274174.540896 PERU | 1997 | 459281852.32089 PERU | 1996 | 464577991.997055 PERU | 1995 | 462410377.648372 PERU | 1994 | 464131188.035567 PERU | 1993 | 459124355.057679 PERU | 1992 | 460277063.542613 ROMANIA | 1998 | 265780996.662463 ROMANIA | 1997 | 471856920.679928 ROMANIA | 1996 | 458795468.731991 ROMANIA | 1995 | 463430472.660365 ROMANIA | 1994 | 463660110.414517 ROMANIA | 1993 | 467897454.41491 ROMANIA | 1992 | 463540208.275487 RUSSIA | 1998 | 277738334.518366 RUSSIA | 1997 | 474055244.550128 RUSSIA | 1996 | 481392973.443023 RUSSIA | 1995 | 470328809.406741 RUSSIA | 1994 | 478348979.939102 RUSSIA | 1993 | 473647686.336543 RUSSIA | 1992 | 470513525.442615 SAUDI ARABIA | 1998 | 278241857.554498 SAUDI ARABIA | 1997 | 465055314.442866 SAUDI ARABIA | 1996 | 471975491.714429 SAUDI ARABIA | 1995 | 474889815.544257 SAUDI ARABIA | 1994 | 468508485.023214 SAUDI ARABIA | 1993 | 468076383.617924 SAUDI ARABIA | 1992 | 462324977.789656 UNITED KINGDOM | 1998 | 275928117.142026 UNITED KINGDOM | 1997 | 464299193.697799 UNITED KINGDOM | 1996 | 460263615.030074 UNITED KINGDOM | 1995 | 466322081.849953 UNITED KINGDOM | 1994 | 462727555.636902 UNITED KINGDOM | 1993 | 468326383.169902 UNITED KINGDOM | 1992 | 458653810.136588 UNITED STATES | 1998 | 276427650.470343 UNITED STATES | 1997 | 472048985.297981 UNITED STATES | 1996 | 471669716.361431 UNITED STATES | 1995 | 462371050.034788 UNITED STATES | 1994 | 473321548.462685 UNITED STATES | 1993 | 467675881.311471 UNITED STATES | 1992 | 465459432.404079 VIETNAM | 1998 | 265574250.976208 VIETNAM | 1997 | 459883938.725495 VIETNAM | 1996 | 468278917.441298 VIETNAM | 1995 | 456908619.577101 VIETNAM | 1994 | 465382399.712109 VIETNAM | 1993 | 465083513.887734 VIETNAM | 1992 | 462805396.423392 (175 rows) COMMIT; COMMIT