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 '%blanched%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264146147.724008 ALGERIA | 1997 | 452499892.45671 ALGERIA | 1996 | 453903006.844733 ALGERIA | 1995 | 454711134.26357 ALGERIA | 1994 | 461033027.22637 ALGERIA | 1993 | 454920925.981022 ALGERIA | 1992 | 457565611.226603 ARGENTINA | 1998 | 278386475.151407 ARGENTINA | 1997 | 468806180.297383 ARGENTINA | 1996 | 471815227.47264 ARGENTINA | 1995 | 465635967.721624 ARGENTINA | 1994 | 466369784.621985 ARGENTINA | 1993 | 466683985.310607 ARGENTINA | 1992 | 474286974.859426 BRAZIL | 1998 | 271252026.064291 BRAZIL | 1997 | 471152773.068315 BRAZIL | 1996 | 476067002.395047 BRAZIL | 1995 | 464973559.642974 BRAZIL | 1994 | 466225883.093987 BRAZIL | 1993 | 464994559.129092 BRAZIL | 1992 | 471866084.458672 CANADA | 1998 | 280368956.744555 CANADA | 1997 | 467816447.694955 CANADA | 1996 | 473094557.851985 CANADA | 1995 | 466121173.193901 CANADA | 1994 | 473635247.543471 CANADA | 1993 | 483046029.905316 CANADA | 1992 | 476924044.994367 CHINA | 1998 | 270250471.461277 CHINA | 1997 | 458843271.606792 CHINA | 1996 | 456913548.361363 CHINA | 1995 | 467015177.74805 CHINA | 1994 | 461058506.151248 CHINA | 1993 | 456383303.47794 CHINA | 1992 | 458503889.968411 EGYPT | 1998 | 266011478.802132 EGYPT | 1997 | 467614357.054849 EGYPT | 1996 | 464061066.595649 EGYPT | 1995 | 471281981.803434 EGYPT | 1994 | 465324961.051071 EGYPT | 1993 | 467888378.515957 EGYPT | 1992 | 468664830.4819 ETHIOPIA | 1998 | 271127719.902911 ETHIOPIA | 1997 | 449877005.139379 ETHIOPIA | 1996 | 452226500.886629 ETHIOPIA | 1995 | 450321571.298868 ETHIOPIA | 1994 | 461805748.952045 ETHIOPIA | 1993 | 456182830.828365 ETHIOPIA | 1992 | 461325166.56273 FRANCE | 1998 | 272615499.603919 FRANCE | 1997 | 468285876.035201 FRANCE | 1996 | 474368815.563371 FRANCE | 1995 | 460002733.661986 FRANCE | 1994 | 463343949.303868 FRANCE | 1993 | 463878789.851476 FRANCE | 1992 | 465174121.386428 GERMANY | 1998 | 282889758.271199 GERMANY | 1997 | 478039648.501128 GERMANY | 1996 | 483492620.337169 GERMANY | 1995 | 482350842.471482 GERMANY | 1994 | 478805027.085294 GERMANY | 1993 | 478576934.628335 GERMANY | 1992 | 476847111.860937 INDIA | 1998 | 276686885.784015 INDIA | 1997 | 470688229.463605 INDIA | 1996 | 474610957.759971 INDIA | 1995 | 473969158.917264 INDIA | 1994 | 474538282.568718 INDIA | 1993 | 469613400.346616 INDIA | 1992 | 483768617.866603 INDONESIA | 1998 | 275868877.631507 INDONESIA | 1997 | 461173354.873825 INDONESIA | 1996 | 465968205.188546 INDONESIA | 1995 | 468423044.985204 INDONESIA | 1994 | 464094295.972988 INDONESIA | 1993 | 458322049.474051 INDONESIA | 1992 | 457372579.049548 IRAN | 1998 | 268224436.90547 IRAN | 1997 | 458524800.156011 IRAN | 1996 | 460347080.362455 IRAN | 1995 | 464339747.813267 IRAN | 1994 | 458714084.247509 IRAN | 1993 | 456957026.404368 IRAN | 1992 | 462626572.161235 IRAQ | 1998 | 279064026.106981 IRAQ | 1997 | 466678637.299656 IRAQ | 1996 | 472600027.810736 IRAQ | 1995 | 469140838.588981 IRAQ | 1994 | 470883719.055172 IRAQ | 1993 | 464645135.140733 IRAQ | 1992 | 473182127.337034 JAPAN | 1998 | 275062306.873809 JAPAN | 1997 | 465057038.461201 JAPAN | 1996 | 466551309.135354 JAPAN | 1995 | 462383483.867952 JAPAN | 1994 | 458776915.784144 JAPAN | 1993 | 469959522.857158 JAPAN | 1992 | 457825048.270421 JORDAN | 1998 | 272537918.479227 JORDAN | 1997 | 464630017.295781 JORDAN | 1996 | 467020548.790742 JORDAN | 1995 | 462294471.144388 JORDAN | 1994 | 460804762.173429 JORDAN | 1993 | 458333151.433892 JORDAN | 1992 | 462278540.769324 KENYA | 1998 | 273628457.257522 KENYA | 1997 | 469966588.67281 KENYA | 1996 | 476375043.157347 KENYA | 1995 | 466705677.044219 KENYA | 1994 | 469494672.494434 KENYA | 1993 | 471887689.496827 KENYA | 1992 | 469127711.86382 MOROCCO | 1998 | 272802682.131359 MOROCCO | 1997 | 470748841.56492 MOROCCO | 1996 | 477079301.473763 MOROCCO | 1995 | 477499570.363994 MOROCCO | 1994 | 478388405.143196 MOROCCO | 1993 | 470991111.238855 MOROCCO | 1992 | 471597661.323656 MOZAMBIQUE | 1998 | 272992549.214721 MOZAMBIQUE | 1997 | 459397226.102759 MOZAMBIQUE | 1996 | 468226642.667837 MOZAMBIQUE | 1995 | 459971336.492393 MOZAMBIQUE | 1994 | 462363889.977949 MOZAMBIQUE | 1993 | 462338488.599991 MOZAMBIQUE | 1992 | 459591088.599108 PERU | 1998 | 268703925.095614 PERU | 1997 | 459459196.213878 PERU | 1996 | 461780769.553986 PERU | 1995 | 470641466.021814 PERU | 1994 | 470995640.641206 PERU | 1993 | 467797576.146739 PERU | 1992 | 474209291.187681 ROMANIA | 1998 | 272266594.401111 ROMANIA | 1997 | 464303916.603187 ROMANIA | 1996 | 462224063.080104 ROMANIA | 1995 | 461437759.582847 ROMANIA | 1994 | 468287258.916286 ROMANIA | 1993 | 463651559.53992 ROMANIA | 1992 | 459258975.977466 RUSSIA | 1998 | 273253751.532491 RUSSIA | 1997 | 472426466.689681 RUSSIA | 1996 | 473320377.190752 RUSSIA | 1995 | 463993889.474195 RUSSIA | 1994 | 465390316.019427 RUSSIA | 1993 | 460425439.464778 RUSSIA | 1992 | 473634755.4526 SAUDI ARABIA | 1998 | 274746085.908596 SAUDI ARABIA | 1997 | 469684896.380642 SAUDI ARABIA | 1996 | 466624460.44481 SAUDI ARABIA | 1995 | 471252727.446511 SAUDI ARABIA | 1994 | 460673963.105771 SAUDI ARABIA | 1993 | 464619076.439549 SAUDI ARABIA | 1992 | 469437472.279144 UNITED KINGDOM | 1998 | 272705616.267686 UNITED KINGDOM | 1997 | 470476491.774104 UNITED KINGDOM | 1996 | 471955037.343759 UNITED KINGDOM | 1995 | 465306964.600289 UNITED KINGDOM | 1994 | 463007153.249897 UNITED KINGDOM | 1993 | 463457968.689997 UNITED KINGDOM | 1992 | 464661747.077707 UNITED STATES | 1998 | 269510687.208424 UNITED STATES | 1997 | 467123514.325447 UNITED STATES | 1996 | 469812247.62608 UNITED STATES | 1995 | 474510846.73713 UNITED STATES | 1994 | 468560046.827551 UNITED STATES | 1993 | 470452989.104724 UNITED STATES | 1992 | 463203703.778342 VIETNAM | 1998 | 263409409.383112 VIETNAM | 1997 | 461023762.032638 VIETNAM | 1996 | 476312723.126191 VIETNAM | 1995 | 461088994.103969 VIETNAM | 1994 | 464851535.652119 VIETNAM | 1993 | 466072953.269467 VIETNAM | 1992 | 460894545.159261 (175 rows) COMMIT; COMMIT