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 '%sienna%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270580096.999179 ALGERIA | 1997 | 452350364.686154 ALGERIA | 1996 | 465226545.825154 ALGERIA | 1995 | 460008274.642018 ALGERIA | 1994 | 460075723.472737 ALGERIA | 1993 | 455964248.179704 ALGERIA | 1992 | 465338769.502439 ARGENTINA | 1998 | 279150446.115411 ARGENTINA | 1997 | 475171870.753288 ARGENTINA | 1996 | 470942213.281379 ARGENTINA | 1995 | 469328042.889403 ARGENTINA | 1994 | 470485570.212906 ARGENTINA | 1993 | 469453937.87641 ARGENTINA | 1992 | 478594534.952092 BRAZIL | 1998 | 279047185.581123 BRAZIL | 1997 | 473623354.057909 BRAZIL | 1996 | 474743734.893559 BRAZIL | 1995 | 467426028.651424 BRAZIL | 1994 | 467270229.593074 BRAZIL | 1993 | 468128420.204798 BRAZIL | 1992 | 484434676.606373 CANADA | 1998 | 278756085.345548 CANADA | 1997 | 476049726.612862 CANADA | 1996 | 479976688.200668 CANADA | 1995 | 473145131.631142 CANADA | 1994 | 481347458.710982 CANADA | 1993 | 473967050.618416 CANADA | 1992 | 479151095.452962 CHINA | 1998 | 275283780.687497 CHINA | 1997 | 469528930.669415 CHINA | 1996 | 467507148.237654 CHINA | 1995 | 475310215.27982 CHINA | 1994 | 470875744.715506 CHINA | 1993 | 471931544.027686 CHINA | 1992 | 468963670.781724 EGYPT | 1998 | 268908587.59032 EGYPT | 1997 | 465091533.368384 EGYPT | 1996 | 464655569.836357 EGYPT | 1995 | 459068470.005859 EGYPT | 1994 | 458893720.123347 EGYPT | 1993 | 463826219.060664 EGYPT | 1992 | 468515454.075435 ETHIOPIA | 1998 | 271901651.773179 ETHIOPIA | 1997 | 456808043.16647 ETHIOPIA | 1996 | 461756847.859448 ETHIOPIA | 1995 | 471379508.307406 ETHIOPIA | 1994 | 455135329.597501 ETHIOPIA | 1993 | 468287614.425374 ETHIOPIA | 1992 | 461886039.38726 FRANCE | 1998 | 273417529.326026 FRANCE | 1997 | 464060613.671307 FRANCE | 1996 | 472445428.010585 FRANCE | 1995 | 465980290.682451 FRANCE | 1994 | 462216168.376077 FRANCE | 1993 | 464480174.020725 FRANCE | 1992 | 471886460.706188 GERMANY | 1998 | 276268318.244271 GERMANY | 1997 | 473394165.497798 GERMANY | 1996 | 485160339.018506 GERMANY | 1995 | 477926365.785369 GERMANY | 1994 | 475857230.946428 GERMANY | 1993 | 473998254.939012 GERMANY | 1992 | 481192302.056567 INDIA | 1998 | 271575686.054709 INDIA | 1997 | 475765525.588973 INDIA | 1996 | 465826594.464197 INDIA | 1995 | 478641234.490416 INDIA | 1994 | 477274270.413287 INDIA | 1993 | 471347732.466626 INDIA | 1992 | 482705673.443816 INDONESIA | 1998 | 273182777.714923 INDONESIA | 1997 | 465397078.206525 INDONESIA | 1996 | 469538952.01257 INDONESIA | 1995 | 458763070.937408 INDONESIA | 1994 | 465355506.196689 INDONESIA | 1993 | 463932473.388615 INDONESIA | 1992 | 459283878.39966 IRAN | 1998 | 267769930.572591 IRAN | 1997 | 467238079.638012 IRAN | 1996 | 476616785.5011 IRAN | 1995 | 463768450.142524 IRAN | 1994 | 461306779.976163 IRAN | 1993 | 461573148.721443 IRAN | 1992 | 464159376.79037 IRAQ | 1998 | 275736723.937187 IRAQ | 1997 | 471446485.808054 IRAQ | 1996 | 472635678.006834 IRAQ | 1995 | 472162112.003533 IRAQ | 1994 | 473832891.161489 IRAQ | 1993 | 463156397.500423 IRAQ | 1992 | 471737614.673148 JAPAN | 1998 | 274305274.654376 JAPAN | 1997 | 473466404.795918 JAPAN | 1996 | 477022215.395096 JAPAN | 1995 | 462976031.780149 JAPAN | 1994 | 469349145.620178 JAPAN | 1993 | 465803716.76775 JAPAN | 1992 | 473556137.215312 JORDAN | 1998 | 270018303.31193 JORDAN | 1997 | 464398703.45897 JORDAN | 1996 | 462979190.785317 JORDAN | 1995 | 467291971.888998 JORDAN | 1994 | 464350512.47046 JORDAN | 1993 | 466570533.4214 JORDAN | 1992 | 461549591.309002 KENYA | 1998 | 275964433.269012 KENYA | 1997 | 466397080.422385 KENYA | 1996 | 476522238.666673 KENYA | 1995 | 467046127.44572 KENYA | 1994 | 472654552.081512 KENYA | 1993 | 476830449.97919 KENYA | 1992 | 471241074.917194 MOROCCO | 1998 | 274258723.777298 MOROCCO | 1997 | 475656074.150161 MOROCCO | 1996 | 471157015.079659 MOROCCO | 1995 | 467021176.870426 MOROCCO | 1994 | 470519205.368789 MOROCCO | 1993 | 473110246.073383 MOROCCO | 1992 | 466716458.981388 MOZAMBIQUE | 1998 | 270752891.993569 MOZAMBIQUE | 1997 | 461653208.942201 MOZAMBIQUE | 1996 | 460559071.630814 MOZAMBIQUE | 1995 | 460090396.137656 MOZAMBIQUE | 1994 | 461707547.68846 MOZAMBIQUE | 1993 | 452602127.54342 MOZAMBIQUE | 1992 | 455153088.47591 PERU | 1998 | 273689631.870178 PERU | 1997 | 467505803.659597 PERU | 1996 | 469850725.717254 PERU | 1995 | 469121554.798163 PERU | 1994 | 470188723.48326 PERU | 1993 | 466266780.945933 PERU | 1992 | 476676660.661046 ROMANIA | 1998 | 273873891.935654 ROMANIA | 1997 | 470617217.3528 ROMANIA | 1996 | 468747720.266917 ROMANIA | 1995 | 467458405.499572 ROMANIA | 1994 | 472363444.546228 ROMANIA | 1993 | 473185336.49843 ROMANIA | 1992 | 468922307.981111 RUSSIA | 1998 | 274084635.958812 RUSSIA | 1997 | 465077760.041357 RUSSIA | 1996 | 473346385.018314 RUSSIA | 1995 | 463755893.502212 RUSSIA | 1994 | 474191423.864383 RUSSIA | 1993 | 463043397.722309 RUSSIA | 1992 | 472651321.456251 SAUDI ARABIA | 1998 | 278887213.373619 SAUDI ARABIA | 1997 | 463406565.430733 SAUDI ARABIA | 1996 | 474346128.582426 SAUDI ARABIA | 1995 | 469915556.135917 SAUDI ARABIA | 1994 | 466160656.170712 SAUDI ARABIA | 1993 | 470195065.342822 SAUDI ARABIA | 1992 | 471618058.728837 UNITED KINGDOM | 1998 | 268596455.569973 UNITED KINGDOM | 1997 | 463642935.678264 UNITED KINGDOM | 1996 | 465131084.119014 UNITED KINGDOM | 1995 | 458693921.709323 UNITED KINGDOM | 1994 | 465063483.73471 UNITED KINGDOM | 1993 | 465100925.612717 UNITED KINGDOM | 1992 | 462306559.086444 UNITED STATES | 1998 | 276238611.610587 UNITED STATES | 1997 | 481093871.215988 UNITED STATES | 1996 | 489792786.52061 UNITED STATES | 1995 | 475484525.553763 UNITED STATES | 1994 | 475203065.770302 UNITED STATES | 1993 | 487283178.975449 UNITED STATES | 1992 | 478850873.812725 VIETNAM | 1998 | 282296019.625709 VIETNAM | 1997 | 461343429.207806 VIETNAM | 1996 | 475285895.660928 VIETNAM | 1995 | 466268197.529771 VIETNAM | 1994 | 464845176.826205 VIETNAM | 1993 | 466148025.465862 VIETNAM | 1992 | 470484150.050474 (175 rows) COMMIT; COMMIT