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 '%blue%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 262358980.754373 ALGERIA | 1997 | 451278152.639322 ALGERIA | 1996 | 459812769.422614 ALGERIA | 1995 | 447924981.902669 ALGERIA | 1994 | 459999692.374342 ALGERIA | 1993 | 456459948.075286 ALGERIA | 1992 | 450337994.774137 ARGENTINA | 1998 | 275519852.383299 ARGENTINA | 1997 | 468643787.743085 ARGENTINA | 1996 | 470127939.268363 ARGENTINA | 1995 | 465995931.385883 ARGENTINA | 1994 | 467419058.945557 ARGENTINA | 1993 | 466210090.863526 ARGENTINA | 1992 | 471671006.095696 BRAZIL | 1998 | 275903102.345741 BRAZIL | 1997 | 461291937.135746 BRAZIL | 1996 | 461346337.109315 BRAZIL | 1995 | 458087116.570003 BRAZIL | 1994 | 459200202.129972 BRAZIL | 1993 | 458632980.891484 BRAZIL | 1992 | 460526973.90027 CANADA | 1998 | 281576993.272772 CANADA | 1997 | 473668758.799096 CANADA | 1996 | 481381048.035447 CANADA | 1995 | 477581889.921517 CANADA | 1994 | 483948968.655469 CANADA | 1993 | 476522530.315035 CANADA | 1992 | 482601282.543631 CHINA | 1998 | 271928429.176041 CHINA | 1997 | 463380545.671485 CHINA | 1996 | 464258251.655667 CHINA | 1995 | 464263025.930825 CHINA | 1994 | 467355806.041603 CHINA | 1993 | 468152290.957966 CHINA | 1992 | 470362063.505575 EGYPT | 1998 | 271477458.70117 EGYPT | 1997 | 472794541.16249 EGYPT | 1996 | 469764826.758968 EGYPT | 1995 | 470567027.385915 EGYPT | 1994 | 471488170.01721 EGYPT | 1993 | 466568341.980567 EGYPT | 1992 | 455961246.63179 ETHIOPIA | 1998 | 262880307.908846 ETHIOPIA | 1997 | 442950006.212292 ETHIOPIA | 1996 | 451385833.33762 ETHIOPIA | 1995 | 459002014.812534 ETHIOPIA | 1994 | 452645796.202657 ETHIOPIA | 1993 | 447998143.988787 ETHIOPIA | 1992 | 459379803.142275 FRANCE | 1998 | 272788509.837768 FRANCE | 1997 | 464624375.221417 FRANCE | 1996 | 475492320.377424 FRANCE | 1995 | 471687195.49646 FRANCE | 1994 | 472601849.588458 FRANCE | 1993 | 468399091.006564 FRANCE | 1992 | 471223024.961011 GERMANY | 1998 | 277467774.235823 GERMANY | 1997 | 467918780.558374 GERMANY | 1996 | 471633421.506503 GERMANY | 1995 | 470898193.649302 GERMANY | 1994 | 469197931.24308 GERMANY | 1993 | 469511879.00317 GERMANY | 1992 | 471023148.685743 INDIA | 1998 | 273463984.442117 INDIA | 1997 | 476091880.539612 INDIA | 1996 | 482292515.278566 INDIA | 1995 | 477252961.793355 INDIA | 1994 | 471949410.011879 INDIA | 1993 | 481750595.394977 INDIA | 1992 | 483977013.412292 INDONESIA | 1998 | 270833630.617774 INDONESIA | 1997 | 459856960.485784 INDONESIA | 1996 | 469425607.156569 INDONESIA | 1995 | 461706849.709879 INDONESIA | 1994 | 463050111.887954 INDONESIA | 1993 | 462744410.877133 INDONESIA | 1992 | 468544920.385875 IRAN | 1998 | 272161950.675989 IRAN | 1997 | 466803836.33522 IRAN | 1996 | 461234313.696706 IRAN | 1995 | 469217190.943273 IRAN | 1994 | 465187377.013178 IRAN | 1993 | 469550714.152459 IRAN | 1992 | 464127792.179984 IRAQ | 1998 | 279361814.918724 IRAQ | 1997 | 479048786.133583 IRAQ | 1996 | 478203734.647379 IRAQ | 1995 | 475367136.819599 IRAQ | 1994 | 472426225.403507 IRAQ | 1993 | 473600164.171002 IRAQ | 1992 | 468692057.31788 JAPAN | 1998 | 272589473.774292 JAPAN | 1997 | 468778436.842026 JAPAN | 1996 | 464809792.089402 JAPAN | 1995 | 463914762.200071 JAPAN | 1994 | 469770521.639497 JAPAN | 1993 | 470467181.186572 JAPAN | 1992 | 465721425.974729 JORDAN | 1998 | 275114620.313375 JORDAN | 1997 | 453550592.137163 JORDAN | 1996 | 465798527.895955 JORDAN | 1995 | 467804888.287363 JORDAN | 1994 | 468925472.38301 JORDAN | 1993 | 461012007.011249 JORDAN | 1992 | 472158402.071215 KENYA | 1998 | 275813891.687923 KENYA | 1997 | 476603362.304168 KENYA | 1996 | 477520221.781932 KENYA | 1995 | 467721774.775918 KENYA | 1994 | 478427615.869798 KENYA | 1993 | 478789858.265863 KENYA | 1992 | 466383126.486627 MOROCCO | 1998 | 276250317.982991 MOROCCO | 1997 | 476915257.932152 MOROCCO | 1996 | 484221794.186175 MOROCCO | 1995 | 476483002.782671 MOROCCO | 1994 | 481618832.313728 MOROCCO | 1993 | 475833569.704369 MOROCCO | 1992 | 482723142.39508 MOZAMBIQUE | 1998 | 276534343.142365 MOZAMBIQUE | 1997 | 460505437.299947 MOZAMBIQUE | 1996 | 459464947.902676 MOZAMBIQUE | 1995 | 459498261.546868 MOZAMBIQUE | 1994 | 456730802.723229 MOZAMBIQUE | 1993 | 460837303.907604 MOZAMBIQUE | 1992 | 457754301.52841 PERU | 1998 | 271456254.416588 PERU | 1997 | 469836122.47856 PERU | 1996 | 465777596.024727 PERU | 1995 | 463092524.847597 PERU | 1994 | 464122606.548082 PERU | 1993 | 466347211.079994 PERU | 1992 | 468654648.453974 ROMANIA | 1998 | 276671450.188283 ROMANIA | 1997 | 475131180.964685 ROMANIA | 1996 | 478127163.690881 ROMANIA | 1995 | 473576008.644452 ROMANIA | 1994 | 473671836.965743 ROMANIA | 1993 | 480061835.033126 ROMANIA | 1992 | 477784527.726159 RUSSIA | 1998 | 274943317.737699 RUSSIA | 1997 | 471399007.050767 RUSSIA | 1996 | 462840345.178077 RUSSIA | 1995 | 455238168.529858 RUSSIA | 1994 | 466323993.046833 RUSSIA | 1993 | 466388686.006946 RUSSIA | 1992 | 476084825.188581 SAUDI ARABIA | 1998 | 269546737.787268 SAUDI ARABIA | 1997 | 459282845.994271 SAUDI ARABIA | 1996 | 462567048.959866 SAUDI ARABIA | 1995 | 469343411.0201 SAUDI ARABIA | 1994 | 472804782.295305 SAUDI ARABIA | 1993 | 464503161.812099 SAUDI ARABIA | 1992 | 476084393.053551 UNITED KINGDOM | 1998 | 274480935.650466 UNITED KINGDOM | 1997 | 460728072.053359 UNITED KINGDOM | 1996 | 467255269.765425 UNITED KINGDOM | 1995 | 470345677.861215 UNITED KINGDOM | 1994 | 470401027.54092 UNITED KINGDOM | 1993 | 461729510.307917 UNITED KINGDOM | 1992 | 466363209.814736 UNITED STATES | 1998 | 276791592.140624 UNITED STATES | 1997 | 475429053.781808 UNITED STATES | 1996 | 476222232.508054 UNITED STATES | 1995 | 475043411.036207 UNITED STATES | 1994 | 475897300.04117 UNITED STATES | 1993 | 470704988.518878 UNITED STATES | 1992 | 476653255.078169 VIETNAM | 1998 | 277869818.20706 VIETNAM | 1997 | 465175854.375282 VIETNAM | 1996 | 479778822.262605 VIETNAM | 1995 | 474330436.299459 VIETNAM | 1994 | 469152799.612805 VIETNAM | 1993 | 467920977.795694 VIETNAM | 1992 | 468048377.035774 (175 rows) COMMIT; COMMIT