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 | 263624198.385975 ALGERIA | 1997 | 452669186.841711 ALGERIA | 1996 | 453790274.630905 ALGERIA | 1995 | 454919572.039412 ALGERIA | 1994 | 461341389.83968 ALGERIA | 1993 | 454631831.78209 ALGERIA | 1992 | 457635871.059072 ARGENTINA | 1998 | 278305851.841318 ARGENTINA | 1997 | 468626654.410095 ARGENTINA | 1996 | 471645016.492169 ARGENTINA | 1995 | 465494338.50855 ARGENTINA | 1994 | 466236576.239886 ARGENTINA | 1993 | 466567129.494918 ARGENTINA | 1992 | 474454504.705952 BRAZIL | 1998 | 271579469.679116 BRAZIL | 1997 | 471289319.806958 BRAZIL | 1996 | 475762019.773418 BRAZIL | 1995 | 464801500.870453 BRAZIL | 1994 | 466262211.164566 BRAZIL | 1993 | 465192229.322734 BRAZIL | 1992 | 471504272.397393 CANADA | 1998 | 280700269.756268 CANADA | 1997 | 467874030.123192 CANADA | 1996 | 472951383.706566 CANADA | 1995 | 466031245.98186 CANADA | 1994 | 473596915.541533 CANADA | 1993 | 483151374.794312 CANADA | 1992 | 477552857.549205 CHINA | 1998 | 270172597.069062 CHINA | 1997 | 458764363.767512 CHINA | 1996 | 456892164.420235 CHINA | 1995 | 466820142.296924 CHINA | 1994 | 461363567.997946 CHINA | 1993 | 456609537.340611 CHINA | 1992 | 458226839.359176 EGYPT | 1998 | 266017040.968662 EGYPT | 1997 | 467287240.190842 EGYPT | 1996 | 463571159.888553 EGYPT | 1995 | 471189289.240563 EGYPT | 1994 | 465641230.017081 EGYPT | 1993 | 467956071.513148 EGYPT | 1992 | 468575199.567861 ETHIOPIA | 1998 | 271156542.516309 ETHIOPIA | 1997 | 449977910.219412 ETHIOPIA | 1996 | 452481322.229122 ETHIOPIA | 1995 | 450248792.57805 ETHIOPIA | 1994 | 461657461.859892 ETHIOPIA | 1993 | 456560903.52641 ETHIOPIA | 1992 | 461254183.827586 FRANCE | 1998 | 272668838.663666 FRANCE | 1997 | 468317366.624613 FRANCE | 1996 | 474512467.652139 FRANCE | 1995 | 459303886.389993 FRANCE | 1994 | 463383206.080118 FRANCE | 1993 | 463950449.623553 FRANCE | 1992 | 465563678.066055 GERMANY | 1998 | 283056693.68507 GERMANY | 1997 | 478354809.818459 GERMANY | 1996 | 483822684.346509 GERMANY | 1995 | 482733750.660954 GERMANY | 1994 | 478826081.546813 GERMANY | 1993 | 479092649.547738 GERMANY | 1992 | 476708542.308603 INDIA | 1998 | 276572813.961412 INDIA | 1997 | 470764723.267945 INDIA | 1996 | 474245078.389094 INDIA | 1995 | 473841451.614164 INDIA | 1994 | 474337504.970391 INDIA | 1993 | 469672788.542415 INDIA | 1992 | 484061904.081973 INDONESIA | 1998 | 275657520.290723 INDONESIA | 1997 | 461095907.404877 INDONESIA | 1996 | 465546563.682711 INDONESIA | 1995 | 468222385.494647 INDONESIA | 1994 | 464127750.060151 INDONESIA | 1993 | 458182767.20676 INDONESIA | 1992 | 457500532.006552 IRAN | 1998 | 268443912.545139 IRAN | 1997 | 458159420.724318 IRAN | 1996 | 460657444.504364 IRAN | 1995 | 463953492.145109 IRAN | 1994 | 458534751.654735 IRAN | 1993 | 457214437.556909 IRAN | 1992 | 462688001.80766 IRAQ | 1998 | 279123525.805886 IRAQ | 1997 | 466383418.309676 IRAQ | 1996 | 472668505.544199 IRAQ | 1995 | 469057555.645422 IRAQ | 1994 | 471029855.264306 IRAQ | 1993 | 464500438.80154 IRAQ | 1992 | 473261866.717983 JAPAN | 1998 | 275210818.228706 JAPAN | 1997 | 465335468.205388 JAPAN | 1996 | 466146010.018506 JAPAN | 1995 | 461506611.479854 JAPAN | 1994 | 458503381.269299 JAPAN | 1993 | 470012518.706859 JAPAN | 1992 | 457623430.470507 JORDAN | 1998 | 272511318.984007 JORDAN | 1997 | 464560406.956054 JORDAN | 1996 | 466854339.344966 JORDAN | 1995 | 462211021.265094 JORDAN | 1994 | 460754110.245904 JORDAN | 1993 | 458473707.29455 JORDAN | 1992 | 461755965.380685 KENYA | 1998 | 273924937.058375 KENYA | 1997 | 470047968.190722 KENYA | 1996 | 476683817.950189 KENYA | 1995 | 466505565.947143 KENYA | 1994 | 468956229.73782 KENYA | 1993 | 471458090.086954 KENYA | 1992 | 468834016.555075 MOROCCO | 1998 | 272577184.098212 MOROCCO | 1997 | 470894466.925849 MOROCCO | 1996 | 476946503.887448 MOROCCO | 1995 | 477164328.036228 MOROCCO | 1994 | 478649017.936387 MOROCCO | 1993 | 471101302.243043 MOROCCO | 1992 | 471351082.669449 MOZAMBIQUE | 1998 | 272947436.313219 MOZAMBIQUE | 1997 | 459182004.051069 MOZAMBIQUE | 1996 | 468471584.042991 MOZAMBIQUE | 1995 | 459156195.082414 MOZAMBIQUE | 1994 | 462367527.537312 MOZAMBIQUE | 1993 | 462029817.726207 MOZAMBIQUE | 1992 | 459209934.84581 PERU | 1998 | 268776963.225518 PERU | 1997 | 459405494.112719 PERU | 1996 | 462082237.453335 PERU | 1995 | 470822268.491 PERU | 1994 | 471116065.75061 PERU | 1993 | 467934745.927192 PERU | 1992 | 474347368.844594 ROMANIA | 1998 | 272389536.208282 ROMANIA | 1997 | 464363442.366163 ROMANIA | 1996 | 462787297.298256 ROMANIA | 1995 | 461085594.709798 ROMANIA | 1994 | 467979840.486893 ROMANIA | 1993 | 463389930.51786 ROMANIA | 1992 | 458926199.901014 RUSSIA | 1998 | 273306336.30825 RUSSIA | 1997 | 472670830.202855 RUSSIA | 1996 | 473160286.436242 RUSSIA | 1995 | 463877275.644247 RUSSIA | 1994 | 464961403.766557 RUSSIA | 1993 | 460726088.902689 RUSSIA | 1992 | 473398196.676258 SAUDI ARABIA | 1998 | 274669188.628531 SAUDI ARABIA | 1997 | 470103393.59642 SAUDI ARABIA | 1996 | 467088446.651852 SAUDI ARABIA | 1995 | 471481474.155965 SAUDI ARABIA | 1994 | 460473962.821887 SAUDI ARABIA | 1993 | 464036046.86624 SAUDI ARABIA | 1992 | 469056009.21606 UNITED KINGDOM | 1998 | 273204193.941372 UNITED KINGDOM | 1997 | 470833560.65549 UNITED KINGDOM | 1996 | 471744080.700624 UNITED KINGDOM | 1995 | 465238452.265836 UNITED KINGDOM | 1994 | 463608010.177821 UNITED KINGDOM | 1993 | 463276674.506432 UNITED KINGDOM | 1992 | 465123960.481834 UNITED STATES | 1998 | 269464167.838781 UNITED STATES | 1997 | 467201929.491778 UNITED STATES | 1996 | 469900027.739857 UNITED STATES | 1995 | 473960743.63137 UNITED STATES | 1994 | 468449420.293151 UNITED STATES | 1993 | 470388843.255813 UNITED STATES | 1992 | 463487325.383064 VIETNAM | 1998 | 263435997.477311 VIETNAM | 1997 | 461245843.490556 VIETNAM | 1996 | 476431708.141069 VIETNAM | 1995 | 461265973.589351 VIETNAM | 1994 | 464756563.853178 VIETNAM | 1993 | 466348607.333147 VIETNAM | 1992 | 460977552.453236 (175 rows) COMMIT; COMMIT