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 | 262242467.19019 ALGERIA | 1997 | 451356468.022274 ALGERIA | 1996 | 459768907.022178 ALGERIA | 1995 | 448554079.981508 ALGERIA | 1994 | 460574683.779114 ALGERIA | 1993 | 457431882.447111 ALGERIA | 1992 | 450568676.762301 ARGENTINA | 1998 | 275703462.187816 ARGENTINA | 1997 | 469434857.42272 ARGENTINA | 1996 | 470739975.283488 ARGENTINA | 1995 | 466226729.376125 ARGENTINA | 1994 | 468028871.214059 ARGENTINA | 1993 | 466620123.003412 ARGENTINA | 1992 | 471795657.125458 BRAZIL | 1998 | 276326440.010174 BRAZIL | 1997 | 462798373.943136 BRAZIL | 1996 | 462283188.151206 BRAZIL | 1995 | 458733268.60285 BRAZIL | 1994 | 460320683.428126 BRAZIL | 1993 | 458955757.298148 BRAZIL | 1992 | 461977630.386735 CANADA | 1998 | 281827872.496509 CANADA | 1997 | 473914429.676837 CANADA | 1996 | 482115121.897557 CANADA | 1995 | 478318656.596051 CANADA | 1994 | 484588708.595862 CANADA | 1993 | 476785359.922323 CANADA | 1992 | 483258630.25602 CHINA | 1998 | 272370392.498275 CHINA | 1997 | 463126733.901579 CHINA | 1996 | 464795902.902102 CHINA | 1995 | 465267136.917985 CHINA | 1994 | 467316805.08537 CHINA | 1993 | 468704796.651391 CHINA | 1992 | 471314559.347231 EGYPT | 1998 | 271863983.729998 EGYPT | 1997 | 472887759.88782 EGYPT | 1996 | 470597296.831498 EGYPT | 1995 | 470498342.607876 EGYPT | 1994 | 471423318.234771 EGYPT | 1993 | 467695042.925401 EGYPT | 1992 | 456852731.035746 ETHIOPIA | 1998 | 263147017.809064 ETHIOPIA | 1997 | 444056936.515639 ETHIOPIA | 1996 | 451853850.863645 ETHIOPIA | 1995 | 460050199.469348 ETHIOPIA | 1994 | 453521201.131133 ETHIOPIA | 1993 | 448881674.022503 ETHIOPIA | 1992 | 459662628.944057 FRANCE | 1998 | 272943583.55015 FRANCE | 1997 | 464611686.843564 FRANCE | 1996 | 476365443.466244 FRANCE | 1995 | 471640315.444745 FRANCE | 1994 | 473146788.164412 FRANCE | 1993 | 469355047.959777 FRANCE | 1992 | 471264976.859691 GERMANY | 1998 | 276955967.53446 GERMANY | 1997 | 467986388.41139 GERMANY | 1996 | 472081762.507436 GERMANY | 1995 | 471343671.444449 GERMANY | 1994 | 469701001.573712 GERMANY | 1993 | 470320866.844308 GERMANY | 1992 | 471179724.914069 INDIA | 1998 | 273241413.39081 INDIA | 1997 | 476388416.681702 INDIA | 1996 | 482227901.126789 INDIA | 1995 | 477393061.185667 INDIA | 1994 | 472687375.654965 INDIA | 1993 | 481951401.908288 INDIA | 1992 | 484533856.739984 INDONESIA | 1998 | 270800169.186986 INDONESIA | 1997 | 459436264.391298 INDONESIA | 1996 | 470432377.76204 INDONESIA | 1995 | 462775905.095435 INDONESIA | 1994 | 463580906.862534 INDONESIA | 1993 | 463752802.598631 INDONESIA | 1992 | 468507916.242626 IRAN | 1998 | 272333904.83829 IRAN | 1997 | 467667070.390692 IRAN | 1996 | 461653059.389188 IRAN | 1995 | 469090698.402676 IRAN | 1994 | 465684951.809309 IRAN | 1993 | 470425015.794747 IRAN | 1992 | 464101668.122302 IRAQ | 1998 | 279114175.009518 IRAQ | 1997 | 480046652.248923 IRAQ | 1996 | 478376660.643139 IRAQ | 1995 | 475342796.517445 IRAQ | 1994 | 472673755.219712 IRAQ | 1993 | 473283308.258827 IRAQ | 1992 | 469363860.727854 JAPAN | 1998 | 272274886.813258 JAPAN | 1997 | 469388449.028692 JAPAN | 1996 | 464944842.900733 JAPAN | 1995 | 463916868.633849 JAPAN | 1994 | 469205984.324375 JAPAN | 1993 | 471683354.922951 JAPAN | 1992 | 465747595.856948 JORDAN | 1998 | 275394488.057251 JORDAN | 1997 | 454545741.373719 JORDAN | 1996 | 465501102.745595 JORDAN | 1995 | 468411927.607928 JORDAN | 1994 | 469126586.864727 JORDAN | 1993 | 461717189.374083 JORDAN | 1992 | 472795077.057428 KENYA | 1998 | 276650901.19811 KENYA | 1997 | 476840248.286946 KENYA | 1996 | 477331372.015933 KENYA | 1995 | 468035288.571908 KENYA | 1994 | 479276259.733676 KENYA | 1993 | 480310012.426851 KENYA | 1992 | 466177777.57577 MOROCCO | 1998 | 276353362.703832 MOROCCO | 1997 | 477644447.28179 MOROCCO | 1996 | 484209283.741768 MOROCCO | 1995 | 476598801.907059 MOROCCO | 1994 | 482818373.901511 MOROCCO | 1993 | 477598927.670655 MOROCCO | 1992 | 483146137.590748 MOZAMBIQUE | 1998 | 276561889.031819 MOZAMBIQUE | 1997 | 461497941.563618 MOZAMBIQUE | 1996 | 460376648.777282 MOZAMBIQUE | 1995 | 459978890.061184 MOZAMBIQUE | 1994 | 457483744.038135 MOZAMBIQUE | 1993 | 461511402.797398 MOZAMBIQUE | 1992 | 458303626.086709 PERU | 1998 | 271564551.023345 PERU | 1997 | 469883318.805924 PERU | 1996 | 466593025.950436 PERU | 1995 | 463362782.444861 PERU | 1994 | 464184301.767958 PERU | 1993 | 465911764.217058 PERU | 1992 | 468489198.704792 ROMANIA | 1998 | 276977399.593645 ROMANIA | 1997 | 475498468.609516 ROMANIA | 1996 | 478565793.591605 ROMANIA | 1995 | 474485623.923903 ROMANIA | 1994 | 474065353.791177 ROMANIA | 1993 | 481606541.542556 ROMANIA | 1992 | 478375700.559075 RUSSIA | 1998 | 274673027.942151 RUSSIA | 1997 | 471345253.507043 RUSSIA | 1996 | 462680560.997959 RUSSIA | 1995 | 456485884.355901 RUSSIA | 1994 | 467145466.835919 RUSSIA | 1993 | 466302941.910666 RUSSIA | 1992 | 475741734.261522 SAUDI ARABIA | 1998 | 270131074.289018 SAUDI ARABIA | 1997 | 459550390.141962 SAUDI ARABIA | 1996 | 462252890.382753 SAUDI ARABIA | 1995 | 469972322.475351 SAUDI ARABIA | 1994 | 473684085.073947 SAUDI ARABIA | 1993 | 465492496.045506 SAUDI ARABIA | 1992 | 475429847.822955 UNITED KINGDOM | 1998 | 274802432.708035 UNITED KINGDOM | 1997 | 461012095.50522 UNITED KINGDOM | 1996 | 467316743.026889 UNITED KINGDOM | 1995 | 470916123.503318 UNITED KINGDOM | 1994 | 470664651.550936 UNITED KINGDOM | 1993 | 462447816.493128 UNITED KINGDOM | 1992 | 466439093.572802 UNITED STATES | 1998 | 277123610.757414 UNITED STATES | 1997 | 474976365.336213 UNITED STATES | 1996 | 476481497.861785 UNITED STATES | 1995 | 475910944.113807 UNITED STATES | 1994 | 476695992.100256 UNITED STATES | 1993 | 471582976.02898 UNITED STATES | 1992 | 476222311.755769 VIETNAM | 1998 | 277720751.18411 VIETNAM | 1997 | 465550108.240386 VIETNAM | 1996 | 480714258.964869 VIETNAM | 1995 | 474615707.165021 VIETNAM | 1994 | 469733053.756149 VIETNAM | 1993 | 468441868.615411 VIETNAM | 1992 | 469173972.371529 (175 rows) COMMIT; COMMIT