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 '%aquamarine%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 262379551.189139 ALGERIA | 1997 | 470158092.856659 ALGERIA | 1996 | 467539699.002368 ALGERIA | 1995 | 463495822.981836 ALGERIA | 1994 | 454679468.60446 ALGERIA | 1993 | 454585426.194879 ALGERIA | 1992 | 462611803.306834 ARGENTINA | 1998 | 272739337.215612 ARGENTINA | 1997 | 468375699.765695 ARGENTINA | 1996 | 460468791.956839 ARGENTINA | 1995 | 466601546.033444 ARGENTINA | 1994 | 466173976.208396 ARGENTINA | 1993 | 462426152.046485 ARGENTINA | 1992 | 465686856.310122 BRAZIL | 1998 | 273224509.353958 BRAZIL | 1997 | 469510621.150144 BRAZIL | 1996 | 465441366.704845 BRAZIL | 1995 | 468780976.982311 BRAZIL | 1994 | 464314749.77622 BRAZIL | 1993 | 470442437.298718 BRAZIL | 1992 | 465677291.377768 CANADA | 1998 | 277336897.953263 CANADA | 1997 | 467503352.982324 CANADA | 1996 | 472259795.289122 CANADA | 1995 | 473154055.523154 CANADA | 1994 | 475138214.754345 CANADA | 1993 | 469642541.031521 CANADA | 1992 | 478583334.289901 CHINA | 1998 | 266754466.553585 CHINA | 1997 | 465528944.91533 CHINA | 1996 | 459153995.727012 CHINA | 1995 | 457354850.47805 CHINA | 1994 | 466566180.405721 CHINA | 1993 | 461992922.521864 CHINA | 1992 | 463131641.148315 EGYPT | 1998 | 270572936.248019 EGYPT | 1997 | 468620515.99275 EGYPT | 1996 | 468152068.437624 EGYPT | 1995 | 466521149.017542 EGYPT | 1994 | 461965947.972859 EGYPT | 1993 | 467162621.967941 EGYPT | 1992 | 469109789.351045 ETHIOPIA | 1998 | 268612336.422834 ETHIOPIA | 1997 | 456866718.202353 ETHIOPIA | 1996 | 452549503.012479 ETHIOPIA | 1995 | 456163122.527496 ETHIOPIA | 1994 | 458637936.556378 ETHIOPIA | 1993 | 459896784.373047 ETHIOPIA | 1992 | 451659524.427305 FRANCE | 1998 | 260299039.296715 FRANCE | 1997 | 448861719.714607 FRANCE | 1996 | 451258695.24941 FRANCE | 1995 | 446927637.803258 FRANCE | 1994 | 453105575.668393 FRANCE | 1993 | 449361417.783321 FRANCE | 1992 | 455258439.277636 GERMANY | 1998 | 269841863.735601 GERMANY | 1997 | 473335283.259951 GERMANY | 1996 | 471775072.849407 GERMANY | 1995 | 475216682.59614 GERMANY | 1994 | 470245871.188315 GERMANY | 1993 | 469501224.463927 GERMANY | 1992 | 467882614.89919 INDIA | 1998 | 279216968.88786 INDIA | 1997 | 472290233.708354 INDIA | 1996 | 472481207.644992 INDIA | 1995 | 472034893.311513 INDIA | 1994 | 474766754.912587 INDIA | 1993 | 478997719.09331 INDIA | 1992 | 479426884.783264 INDONESIA | 1998 | 264634049.634077 INDONESIA | 1997 | 458191773.172823 INDONESIA | 1996 | 459928896.351857 INDONESIA | 1995 | 457315645.786734 INDONESIA | 1994 | 461763295.321508 INDONESIA | 1993 | 458063352.586 INDONESIA | 1992 | 460621362.124146 IRAN | 1998 | 269315176.166302 IRAN | 1997 | 459681256.986021 IRAN | 1996 | 461761069.038778 IRAN | 1995 | 462455033.663964 IRAN | 1994 | 466041367.956248 IRAN | 1993 | 466374688.389074 IRAN | 1992 | 467908623.023613 IRAQ | 1998 | 278247010.750868 IRAQ | 1997 | 476146427.552712 IRAQ | 1996 | 474678534.588384 IRAQ | 1995 | 472463535.260294 IRAQ | 1994 | 472099208.404263 IRAQ | 1993 | 467840983.939852 IRAQ | 1992 | 473452172.534767 JAPAN | 1998 | 266583485.324606 JAPAN | 1997 | 460474127.839878 JAPAN | 1996 | 463737342.510789 JAPAN | 1995 | 460638719.596903 JAPAN | 1994 | 458520982.084087 JAPAN | 1993 | 461834983.34636 JAPAN | 1992 | 461704993.249538 JORDAN | 1998 | 271214995.311757 JORDAN | 1997 | 465185871.741203 JORDAN | 1996 | 456044885.592065 JORDAN | 1995 | 463350291.305069 JORDAN | 1994 | 462706272.113684 JORDAN | 1993 | 466750932.298343 JORDAN | 1992 | 466994138.773632 KENYA | 1998 | 270229950.125443 KENYA | 1997 | 464288037.691719 KENYA | 1996 | 468474161.834303 KENYA | 1995 | 466656970.795564 KENYA | 1994 | 473183279.725386 KENYA | 1993 | 464287621.040394 KENYA | 1992 | 465615228.329875 MOROCCO | 1998 | 272033081.397027 MOROCCO | 1997 | 466906880.565636 MOROCCO | 1996 | 465644575.495214 MOROCCO | 1995 | 467232255.696058 MOROCCO | 1994 | 461215722.488739 MOROCCO | 1993 | 465964657.539066 MOROCCO | 1992 | 471483487.915457 MOZAMBIQUE | 1998 | 265241229.191146 MOZAMBIQUE | 1997 | 460803311.303904 MOZAMBIQUE | 1996 | 457853587.748332 MOZAMBIQUE | 1995 | 455301854.569186 MOZAMBIQUE | 1994 | 457559393.797752 MOZAMBIQUE | 1993 | 461255572.582019 MOZAMBIQUE | 1992 | 455750294.359399 PERU | 1998 | 271631988.359461 PERU | 1997 | 461976915.624104 PERU | 1996 | 470166392.602072 PERU | 1995 | 460449355.821132 PERU | 1994 | 464216047.078449 PERU | 1993 | 461064130.946531 PERU | 1992 | 455600222.040408 ROMANIA | 1998 | 280158155.290615 ROMANIA | 1997 | 482138998.091477 ROMANIA | 1996 | 479991866.698287 ROMANIA | 1995 | 480894598.881881 ROMANIA | 1994 | 474225420.539701 ROMANIA | 1993 | 476185211.10263 ROMANIA | 1992 | 484914689.393184 RUSSIA | 1998 | 275301989.430907 RUSSIA | 1997 | 469351844.331698 RUSSIA | 1996 | 475872290.191494 RUSSIA | 1995 | 464972861.610167 RUSSIA | 1994 | 466598924.660584 RUSSIA | 1993 | 468512619.996471 RUSSIA | 1992 | 462253307.003252 SAUDI ARABIA | 1998 | 277705052.204846 SAUDI ARABIA | 1997 | 460906705.458494 SAUDI ARABIA | 1996 | 475906175.147797 SAUDI ARABIA | 1995 | 456444279.821478 SAUDI ARABIA | 1994 | 465825493.254131 SAUDI ARABIA | 1993 | 464064942.936166 SAUDI ARABIA | 1992 | 466518954.281941 UNITED KINGDOM | 1998 | 272883020.000318 UNITED KINGDOM | 1997 | 473352100.793716 UNITED KINGDOM | 1996 | 465941159.613343 UNITED KINGDOM | 1995 | 467014146.999897 UNITED KINGDOM | 1994 | 462754262.381122 UNITED KINGDOM | 1993 | 461985199.256677 UNITED KINGDOM | 1992 | 462162426.828131 UNITED STATES | 1998 | 270583871.394363 UNITED STATES | 1997 | 465130999.377024 UNITED STATES | 1996 | 455899977.412213 UNITED STATES | 1995 | 458983667.48964 UNITED STATES | 1994 | 464107087.821096 UNITED STATES | 1993 | 466583452.14717 UNITED STATES | 1992 | 464810400.390775 VIETNAM | 1998 | 270434540.382261 VIETNAM | 1997 | 473590875.319099 VIETNAM | 1996 | 479420978.406684 VIETNAM | 1995 | 467044886.690114 VIETNAM | 1994 | 471914871.392958 VIETNAM | 1993 | 465518159.078447 VIETNAM | 1992 | 472419588.231106 (175 rows) COMMIT; COMMIT