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 '%frosted%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267855854.716755 ALGERIA | 1997 | 453784595.627777 ALGERIA | 1996 | 448508479.951776 ALGERIA | 1995 | 450552164.297601 ALGERIA | 1994 | 454047089.291558 ALGERIA | 1993 | 446223090.544051 ALGERIA | 1992 | 450196758.161524 ARGENTINA | 1998 | 280015324.012869 ARGENTINA | 1997 | 470373574.746652 ARGENTINA | 1996 | 473060978.746355 ARGENTINA | 1995 | 472561989.017787 ARGENTINA | 1994 | 473048187.447463 ARGENTINA | 1993 | 466434431.926942 ARGENTINA | 1992 | 464827432.516137 BRAZIL | 1998 | 271269907.47679 BRAZIL | 1997 | 461925078.32586 BRAZIL | 1996 | 465152326.540177 BRAZIL | 1995 | 458689351.571668 BRAZIL | 1994 | 460144940.332271 BRAZIL | 1993 | 461611050.938989 BRAZIL | 1992 | 467180516.508852 CANADA | 1998 | 285798881.111938 CANADA | 1997 | 485253755.023573 CANADA | 1996 | 486219639.367194 CANADA | 1995 | 490333646.962669 CANADA | 1994 | 493290491.04397 CANADA | 1993 | 485200935.608608 CANADA | 1992 | 489250630.56847 CHINA | 1998 | 266466300.677593 CHINA | 1997 | 465669731.152252 CHINA | 1996 | 472626713.156464 CHINA | 1995 | 464763121.196074 CHINA | 1994 | 464881191.903268 CHINA | 1993 | 466231654.014 CHINA | 1992 | 466678733.564428 EGYPT | 1998 | 278471068.741592 EGYPT | 1997 | 473849908.435354 EGYPT | 1996 | 489484662.598051 EGYPT | 1995 | 483811493.672715 EGYPT | 1994 | 481299650.065415 EGYPT | 1993 | 480963707.671661 EGYPT | 1992 | 474105592.601824 ETHIOPIA | 1998 | 266057406.061761 ETHIOPIA | 1997 | 456552297.789864 ETHIOPIA | 1996 | 461792117.215433 ETHIOPIA | 1995 | 467704507.619608 ETHIOPIA | 1994 | 457788032.222231 ETHIOPIA | 1993 | 467127199.932693 ETHIOPIA | 1992 | 468260375.667255 FRANCE | 1998 | 269230973.428065 FRANCE | 1997 | 448138166.409218 FRANCE | 1996 | 458744404.55867 FRANCE | 1995 | 456985810.869552 FRANCE | 1994 | 455414300.285186 FRANCE | 1993 | 460837909.595927 FRANCE | 1992 | 458725857.138184 GERMANY | 1998 | 278396909.531236 GERMANY | 1997 | 478016768.47089 GERMANY | 1996 | 474117508.886308 GERMANY | 1995 | 476648942.579709 GERMANY | 1994 | 472329769.263627 GERMANY | 1993 | 469148535.715922 GERMANY | 1992 | 471864636.315618 INDIA | 1998 | 280302609.526034 INDIA | 1997 | 476229736.99167 INDIA | 1996 | 484361764.125588 INDIA | 1995 | 470838544.118057 INDIA | 1994 | 472493114.233006 INDIA | 1993 | 476291960.052293 INDIA | 1992 | 478330423.952529 INDONESIA | 1998 | 268446433.832083 INDONESIA | 1997 | 461404564.855136 INDONESIA | 1996 | 469591906.12336 INDONESIA | 1995 | 460384398.346072 INDONESIA | 1994 | 465786909.473011 INDONESIA | 1993 | 467299054.092503 INDONESIA | 1992 | 458512240.299059 IRAN | 1998 | 271371106.072369 IRAN | 1997 | 461771114.103465 IRAN | 1996 | 469776859.482126 IRAN | 1995 | 471398139.697453 IRAN | 1994 | 462105737.482486 IRAN | 1993 | 456496670.595612 IRAN | 1992 | 464560827.457705 IRAQ | 1998 | 280783163.418753 IRAQ | 1997 | 475012542.187108 IRAQ | 1996 | 477354260.337607 IRAQ | 1995 | 483820968.225836 IRAQ | 1994 | 480247476.042699 IRAQ | 1993 | 478588532.488111 IRAQ | 1992 | 482471352.09825 JAPAN | 1998 | 277930183.550173 JAPAN | 1997 | 468476282.334512 JAPAN | 1996 | 465790396.074381 JAPAN | 1995 | 467256218.843881 JAPAN | 1994 | 465269818.984386 JAPAN | 1993 | 462508829.416835 JAPAN | 1992 | 468651698.55748 JORDAN | 1998 | 265816813.041319 JORDAN | 1997 | 459579153.914993 JORDAN | 1996 | 457640912.54851 JORDAN | 1995 | 458431161.156043 JORDAN | 1994 | 458757084.96519 JORDAN | 1993 | 456557285.600855 JORDAN | 1992 | 454401864.986286 KENYA | 1998 | 277086713.939819 KENYA | 1997 | 465315234.107687 KENYA | 1996 | 467245692.944955 KENYA | 1995 | 463786593.734793 KENYA | 1994 | 462916855.317797 KENYA | 1993 | 464398564.985731 KENYA | 1992 | 465634984.595058 MOROCCO | 1998 | 274949418.026324 MOROCCO | 1997 | 464391352.673381 MOROCCO | 1996 | 463265110.280841 MOROCCO | 1995 | 463963137.706249 MOROCCO | 1994 | 463287153.580832 MOROCCO | 1993 | 468218584.732162 MOROCCO | 1992 | 467954821.29751 MOZAMBIQUE | 1998 | 274307023.701531 MOZAMBIQUE | 1997 | 455056679.876899 MOZAMBIQUE | 1996 | 460077437.010512 MOZAMBIQUE | 1995 | 459651811.817398 MOZAMBIQUE | 1994 | 454216761.700067 MOZAMBIQUE | 1993 | 450732376.06602 MOZAMBIQUE | 1992 | 458293391.618064 PERU | 1998 | 273706337.916945 PERU | 1997 | 457068256.705511 PERU | 1996 | 463431475.34091 PERU | 1995 | 462071741.416628 PERU | 1994 | 457063442.734194 PERU | 1993 | 459495659.713804 PERU | 1992 | 462497839.161788 ROMANIA | 1998 | 280376933.072423 ROMANIA | 1997 | 469772183.263462 ROMANIA | 1996 | 475927344.509943 ROMANIA | 1995 | 472691628.774273 ROMANIA | 1994 | 474275427.297576 ROMANIA | 1993 | 477145381.878032 ROMANIA | 1992 | 473064109.559768 RUSSIA | 1998 | 275948744.602087 RUSSIA | 1997 | 474879389.432569 RUSSIA | 1996 | 475845451.905794 RUSSIA | 1995 | 473582116.389401 RUSSIA | 1994 | 469296987.6012 RUSSIA | 1993 | 470213598.796302 RUSSIA | 1992 | 478264399.746965 SAUDI ARABIA | 1998 | 279540511.767177 SAUDI ARABIA | 1997 | 469473727.483036 SAUDI ARABIA | 1996 | 471949970.72048 SAUDI ARABIA | 1995 | 476045782.902643 SAUDI ARABIA | 1994 | 474028512.588464 SAUDI ARABIA | 1993 | 475257692.879439 SAUDI ARABIA | 1992 | 472493804.169662 UNITED KINGDOM | 1998 | 271905389.946483 UNITED KINGDOM | 1997 | 457152398.606138 UNITED KINGDOM | 1996 | 463884218.971883 UNITED KINGDOM | 1995 | 461471763.700912 UNITED KINGDOM | 1994 | 464820117.772584 UNITED KINGDOM | 1993 | 459543935.652483 UNITED KINGDOM | 1992 | 463576152.435988 UNITED STATES | 1998 | 274856469.306999 UNITED STATES | 1997 | 468663134.475509 UNITED STATES | 1996 | 473338774.903949 UNITED STATES | 1995 | 472316836.362243 UNITED STATES | 1994 | 474533037.311363 UNITED STATES | 1993 | 466772833.966572 UNITED STATES | 1992 | 476476688.817438 VIETNAM | 1998 | 272831715.994224 VIETNAM | 1997 | 462209790.47395 VIETNAM | 1996 | 469224410.619826 VIETNAM | 1995 | 464559181.730923 VIETNAM | 1994 | 465329715.279068 VIETNAM | 1993 | 472737159.020086 VIETNAM | 1992 | 465963922.048308 (175 rows) COMMIT; COMMIT