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 '%navajo%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272037808.263474 ALGERIA | 1997 | 459281628.256922 ALGERIA | 1996 | 459963403.686848 ALGERIA | 1995 | 463442638.668351 ALGERIA | 1994 | 452323630.293636 ALGERIA | 1993 | 463993128.505307 ALGERIA | 1992 | 463249868.964995 ARGENTINA | 1998 | 281660468.005686 ARGENTINA | 1997 | 474249744.058602 ARGENTINA | 1996 | 474521488.369815 ARGENTINA | 1995 | 473582832.823913 ARGENTINA | 1994 | 476693341.491848 ARGENTINA | 1993 | 477226032.803096 ARGENTINA | 1992 | 474827929.015871 BRAZIL | 1998 | 265709228.549691 BRAZIL | 1997 | 461610914.735242 BRAZIL | 1996 | 465244490.762385 BRAZIL | 1995 | 467527123.377324 BRAZIL | 1994 | 469445306.446085 BRAZIL | 1993 | 470725512.036694 BRAZIL | 1992 | 468663674.296166 CANADA | 1998 | 284447271.439857 CANADA | 1997 | 489536258.318133 CANADA | 1996 | 482984629.626883 CANADA | 1995 | 490967606.351135 CANADA | 1994 | 487571615.207559 CANADA | 1993 | 485182981.395904 CANADA | 1992 | 492480708.168696 CHINA | 1998 | 282294233.885522 CHINA | 1997 | 470326902.089881 CHINA | 1996 | 467155541.33965 CHINA | 1995 | 470757951.385115 CHINA | 1994 | 474901444.468034 CHINA | 1993 | 469692857.159477 CHINA | 1992 | 476465914.12471 EGYPT | 1998 | 271227200.399919 EGYPT | 1997 | 461956186.607245 EGYPT | 1996 | 469286000.02734 EGYPT | 1995 | 461130691.936547 EGYPT | 1994 | 465977439.134165 EGYPT | 1993 | 465365521.484833 EGYPT | 1992 | 463573432.086041 ETHIOPIA | 1998 | 277691863.896451 ETHIOPIA | 1997 | 459232645.570865 ETHIOPIA | 1996 | 458739664.149226 ETHIOPIA | 1995 | 460101128.732508 ETHIOPIA | 1994 | 462286679.779356 ETHIOPIA | 1993 | 469918372.465005 ETHIOPIA | 1992 | 460627183.877571 FRANCE | 1998 | 267367610.495953 FRANCE | 1997 | 457276922.12588 FRANCE | 1996 | 460957548.44413 FRANCE | 1995 | 455589342.569711 FRANCE | 1994 | 461450974.626288 FRANCE | 1993 | 459541597.091175 FRANCE | 1992 | 449898920.098544 GERMANY | 1998 | 279619956.159645 GERMANY | 1997 | 469770172.36847 GERMANY | 1996 | 463391999.382498 GERMANY | 1995 | 468497114.253108 GERMANY | 1994 | 469559905.099056 GERMANY | 1993 | 467910699.258647 GERMANY | 1992 | 470720450.818607 INDIA | 1998 | 278327757.700503 INDIA | 1997 | 483501259.212738 INDIA | 1996 | 486244300.143857 INDIA | 1995 | 478152900.205822 INDIA | 1994 | 480020227.140986 INDIA | 1993 | 486867221.648096 INDIA | 1992 | 479847138.511789 INDONESIA | 1998 | 270313300.818928 INDONESIA | 1997 | 459870183.399444 INDONESIA | 1996 | 474201719.643966 INDONESIA | 1995 | 464901680.85229 INDONESIA | 1994 | 459359815.40218 INDONESIA | 1993 | 469952577.214093 INDONESIA | 1992 | 465428808.262479 IRAN | 1998 | 272837272.022982 IRAN | 1997 | 466327930.6726 IRAN | 1996 | 471401645.767025 IRAN | 1995 | 467585032.099977 IRAN | 1994 | 465263330.495591 IRAN | 1993 | 471381545.038287 IRAN | 1992 | 474632787.304166 IRAQ | 1998 | 280492085.626419 IRAQ | 1997 | 481263440.295561 IRAQ | 1996 | 478356926.920415 IRAQ | 1995 | 479562623.477904 IRAQ | 1994 | 482786617.966039 IRAQ | 1993 | 471690261.444766 IRAQ | 1992 | 480190672.669935 JAPAN | 1998 | 276195202.014618 JAPAN | 1997 | 477864955.777458 JAPAN | 1996 | 476008643.233495 JAPAN | 1995 | 471560963.013228 JAPAN | 1994 | 467839970.080193 JAPAN | 1993 | 469526889.491111 JAPAN | 1992 | 472535487.373805 JORDAN | 1998 | 272038792.235316 JORDAN | 1997 | 465461478.992405 JORDAN | 1996 | 465473114.79499 JORDAN | 1995 | 462302010.319 JORDAN | 1994 | 456874496.232456 JORDAN | 1993 | 464362858.99764 JORDAN | 1992 | 473593522.002635 KENYA | 1998 | 280572999.903009 KENYA | 1997 | 476291253.975656 KENYA | 1996 | 472260132.394994 KENYA | 1995 | 468744631.595708 KENYA | 1994 | 472333026.35554 KENYA | 1993 | 471850452.068384 KENYA | 1992 | 468341703.25473 MOROCCO | 1998 | 277150601.909664 MOROCCO | 1997 | 473661533.764611 MOROCCO | 1996 | 482209704.534283 MOROCCO | 1995 | 473184115.779519 MOROCCO | 1994 | 469235286.717843 MOROCCO | 1993 | 471816075.104905 MOROCCO | 1992 | 477124103.335256 MOZAMBIQUE | 1998 | 271178975.444099 MOZAMBIQUE | 1997 | 458922266.150709 MOZAMBIQUE | 1996 | 466472193.485412 MOZAMBIQUE | 1995 | 472019373.625167 MOZAMBIQUE | 1994 | 461258084.091853 MOZAMBIQUE | 1993 | 463143741.479841 MOZAMBIQUE | 1992 | 461289687.975275 PERU | 1998 | 281508540.139666 PERU | 1997 | 471878452.086364 PERU | 1996 | 472523909.313407 PERU | 1995 | 474662236.517974 PERU | 1994 | 471037411.200283 PERU | 1993 | 469003375.364843 PERU | 1992 | 472193074.087918 ROMANIA | 1998 | 283017030.770792 ROMANIA | 1997 | 472505843.805144 ROMANIA | 1996 | 478462491.620793 ROMANIA | 1995 | 472923541.339482 ROMANIA | 1994 | 476999630.271471 ROMANIA | 1993 | 476253788.33787 ROMANIA | 1992 | 474562254.168266 RUSSIA | 1998 | 279117472.364849 RUSSIA | 1997 | 477889377.153365 RUSSIA | 1996 | 482696701.474594 RUSSIA | 1995 | 486061492.468836 RUSSIA | 1994 | 482621108.925632 RUSSIA | 1993 | 479753576.952126 RUSSIA | 1992 | 482494294.077614 SAUDI ARABIA | 1998 | 281900323.574593 SAUDI ARABIA | 1997 | 465406135.973321 SAUDI ARABIA | 1996 | 474362262.869908 SAUDI ARABIA | 1995 | 472130411.478254 SAUDI ARABIA | 1994 | 471017509.374882 SAUDI ARABIA | 1993 | 465327543.150981 SAUDI ARABIA | 1992 | 461808383.998207 UNITED KINGDOM | 1998 | 264912695.995552 UNITED KINGDOM | 1997 | 466927322.278646 UNITED KINGDOM | 1996 | 456233243.883769 UNITED KINGDOM | 1995 | 460853051.576357 UNITED KINGDOM | 1994 | 456071684.103355 UNITED KINGDOM | 1993 | 451005426.74493 UNITED KINGDOM | 1992 | 454735499.328747 UNITED STATES | 1998 | 279920977.001494 UNITED STATES | 1997 | 473608760.408794 UNITED STATES | 1996 | 479491670.13091 UNITED STATES | 1995 | 473296314.950923 UNITED STATES | 1994 | 479323543.68156 UNITED STATES | 1993 | 475571744.669132 UNITED STATES | 1992 | 475634152.184237 VIETNAM | 1998 | 266879899.89552 VIETNAM | 1997 | 459151160.016312 VIETNAM | 1996 | 474410644.86676 VIETNAM | 1995 | 465030911.00862 VIETNAM | 1994 | 468697864.872398 VIETNAM | 1993 | 463952764.167665 VIETNAM | 1992 | 460389271.368602 (175 rows) COMMIT; COMMIT