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 '%snow%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267771362.44071 ALGERIA | 1997 | 446554961.433444 ALGERIA | 1996 | 468910180.551883 ALGERIA | 1995 | 463989903.733737 ALGERIA | 1994 | 461527790.588458 ALGERIA | 1993 | 462686508.890602 ALGERIA | 1992 | 462395160.998878 ARGENTINA | 1998 | 271437395.218746 ARGENTINA | 1997 | 467116469.254178 ARGENTINA | 1996 | 463816280.335066 ARGENTINA | 1995 | 458726121.662829 ARGENTINA | 1994 | 475837224.031104 ARGENTINA | 1993 | 458420666.128639 ARGENTINA | 1992 | 465089655.893116 BRAZIL | 1998 | 272360310.954221 BRAZIL | 1997 | 461154608.029841 BRAZIL | 1996 | 463470271.481989 BRAZIL | 1995 | 461989644.504081 BRAZIL | 1994 | 468899414.507346 BRAZIL | 1993 | 462601656.850892 BRAZIL | 1992 | 462604918.359028 CANADA | 1998 | 282259098.738523 CANADA | 1997 | 484586728.480454 CANADA | 1996 | 489701607.867761 CANADA | 1995 | 486507124.520839 CANADA | 1994 | 485131088.463346 CANADA | 1993 | 480880493.475556 CANADA | 1992 | 485865498.500671 CHINA | 1998 | 274576294.516329 CHINA | 1997 | 468889873.866333 CHINA | 1996 | 462681758.697464 CHINA | 1995 | 460778982.646393 CHINA | 1994 | 466312444.22385 CHINA | 1993 | 463108988.325883 CHINA | 1992 | 465679737.106853 EGYPT | 1998 | 271548700.505827 EGYPT | 1997 | 461249568.296187 EGYPT | 1996 | 460793854.035703 EGYPT | 1995 | 466494465.005041 EGYPT | 1994 | 467887420.783707 EGYPT | 1993 | 466172347.665894 EGYPT | 1992 | 460745362.475433 ETHIOPIA | 1998 | 271041589.853512 ETHIOPIA | 1997 | 474211125.676099 ETHIOPIA | 1996 | 455878121.172154 ETHIOPIA | 1995 | 467109853.712667 ETHIOPIA | 1994 | 472265350.346061 ETHIOPIA | 1993 | 457596805.060795 ETHIOPIA | 1992 | 457391543.856113 FRANCE | 1998 | 270876076.221534 FRANCE | 1997 | 461264408.492598 FRANCE | 1996 | 460245158.202693 FRANCE | 1995 | 463347544.441017 FRANCE | 1994 | 463355924.04682 FRANCE | 1993 | 465063562.295286 FRANCE | 1992 | 461853653.075486 GERMANY | 1998 | 278983414.698512 GERMANY | 1997 | 478859303.664152 GERMANY | 1996 | 464504080.695523 GERMANY | 1995 | 477186883.139262 GERMANY | 1994 | 473586985.798017 GERMANY | 1993 | 475672389.105256 GERMANY | 1992 | 475807772.647939 INDIA | 1998 | 271697149.323851 INDIA | 1997 | 475070021.448712 INDIA | 1996 | 476516193.041813 INDIA | 1995 | 462278886.619185 INDIA | 1994 | 474414581.653718 INDIA | 1993 | 469807615.185652 INDIA | 1992 | 469762011.340183 INDONESIA | 1998 | 267778348.907968 INDONESIA | 1997 | 458163619.250684 INDONESIA | 1996 | 467121498.201812 INDONESIA | 1995 | 452337505.140524 INDONESIA | 1994 | 457317657.25061 INDONESIA | 1993 | 465675374.024853 INDONESIA | 1992 | 458083201.815209 IRAN | 1998 | 275941967.42452 IRAN | 1997 | 463525288.183054 IRAN | 1996 | 475205290.381617 IRAN | 1995 | 455571702.728528 IRAN | 1994 | 463332237.039269 IRAN | 1993 | 464182193.232638 IRAN | 1992 | 467878047.088514 IRAQ | 1998 | 288608321.341979 IRAQ | 1997 | 473600689.524561 IRAQ | 1996 | 485654883.369482 IRAQ | 1995 | 476501999.601151 IRAQ | 1994 | 482035072.676213 IRAQ | 1993 | 472254517.614015 IRAQ | 1992 | 480533355.809107 JAPAN | 1998 | 272085331.612832 JAPAN | 1997 | 466502083.638638 JAPAN | 1996 | 465846780.196279 JAPAN | 1995 | 470927651.703406 JAPAN | 1994 | 464281717.343969 JAPAN | 1993 | 459187612.520711 JAPAN | 1992 | 467141407.445283 JORDAN | 1998 | 268706692.038691 JORDAN | 1997 | 457810501.625415 JORDAN | 1996 | 461015534.51206 JORDAN | 1995 | 452298882.73204 JORDAN | 1994 | 459319380.148579 JORDAN | 1993 | 454399469.762607 JORDAN | 1992 | 460453409.835267 KENYA | 1998 | 280547587.271427 KENYA | 1997 | 468657038.564191 KENYA | 1996 | 479875386.567728 KENYA | 1995 | 471961126.8651 KENYA | 1994 | 477696115.341206 KENYA | 1993 | 468983057.493103 KENYA | 1992 | 474494283.997292 MOROCCO | 1998 | 269030078.921422 MOROCCO | 1997 | 466646938.140423 MOROCCO | 1996 | 464795050.641154 MOROCCO | 1995 | 462481772.713784 MOROCCO | 1994 | 470820103.401993 MOROCCO | 1993 | 464566588.121008 MOROCCO | 1992 | 459588773.582288 MOZAMBIQUE | 1998 | 266820534.222454 MOZAMBIQUE | 1997 | 458549076.453811 MOZAMBIQUE | 1996 | 458024116.181034 MOZAMBIQUE | 1995 | 456785754.607088 MOZAMBIQUE | 1994 | 460282142.842373 MOZAMBIQUE | 1993 | 464465773.078059 MOZAMBIQUE | 1992 | 455051259.53503 PERU | 1998 | 274083012.794311 PERU | 1997 | 467430078.278026 PERU | 1996 | 467184402.533682 PERU | 1995 | 479828719.443445 PERU | 1994 | 469757681.996171 PERU | 1993 | 465597770.462491 PERU | 1992 | 469865624.744911 ROMANIA | 1998 | 271479702.826398 ROMANIA | 1997 | 465359675.28992 ROMANIA | 1996 | 475063102.388816 ROMANIA | 1995 | 474008663.077096 ROMANIA | 1994 | 466768404.844942 ROMANIA | 1993 | 469700233.943633 ROMANIA | 1992 | 464442417.274174 RUSSIA | 1998 | 283652019.440992 RUSSIA | 1997 | 478701502.719479 RUSSIA | 1996 | 479200585.211098 RUSSIA | 1995 | 464938738.346705 RUSSIA | 1994 | 476618518.7423 RUSSIA | 1993 | 469987876.437443 RUSSIA | 1992 | 470806500.588637 SAUDI ARABIA | 1998 | 274884655.648263 SAUDI ARABIA | 1997 | 465731419.331579 SAUDI ARABIA | 1996 | 468494942.935594 SAUDI ARABIA | 1995 | 467071779.900504 SAUDI ARABIA | 1994 | 467827599.412569 SAUDI ARABIA | 1993 | 459872885.717804 SAUDI ARABIA | 1992 | 470114379.308182 UNITED KINGDOM | 1998 | 267323532.311609 UNITED KINGDOM | 1997 | 463156229.743872 UNITED KINGDOM | 1996 | 458188144.93766 UNITED KINGDOM | 1995 | 456366652.239385 UNITED KINGDOM | 1994 | 457488356.750531 UNITED KINGDOM | 1993 | 465349217.865852 UNITED KINGDOM | 1992 | 453114228.233496 UNITED STATES | 1998 | 271514159.620195 UNITED STATES | 1997 | 471285766.393991 UNITED STATES | 1996 | 461945545.17175 UNITED STATES | 1995 | 465833556.455532 UNITED STATES | 1994 | 461580917.378391 UNITED STATES | 1993 | 471243449.373076 UNITED STATES | 1992 | 468635838.701917 VIETNAM | 1998 | 275104168.416501 VIETNAM | 1997 | 464166630.875046 VIETNAM | 1996 | 471654903.140381 VIETNAM | 1995 | 466030891.362491 VIETNAM | 1994 | 469145450.713981 VIETNAM | 1993 | 458195694.353131 VIETNAM | 1992 | 460698166.991219 (175 rows) COMMIT; COMMIT