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 '%azure%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271917479.02023 ALGERIA | 1997 | 457289203.27489 ALGERIA | 1996 | 459071766.294295 ALGERIA | 1995 | 462634731.915217 ALGERIA | 1994 | 464526476.240356 ALGERIA | 1993 | 458849473.577487 ALGERIA | 1992 | 466415068.124724 ARGENTINA | 1998 | 283710535.631816 ARGENTINA | 1997 | 477768426.248402 ARGENTINA | 1996 | 481897706.25082 ARGENTINA | 1995 | 472141495.304326 ARGENTINA | 1994 | 473295750.648773 ARGENTINA | 1993 | 467099215.260783 ARGENTINA | 1992 | 477193528.583036 BRAZIL | 1998 | 272121809.862882 BRAZIL | 1997 | 474296937.440458 BRAZIL | 1996 | 476029791.767513 BRAZIL | 1995 | 465625030.009472 BRAZIL | 1994 | 465403001.945162 BRAZIL | 1993 | 474184510.922118 BRAZIL | 1992 | 473405820.221506 CANADA | 1998 | 280991661.036348 CANADA | 1997 | 474702465.125177 CANADA | 1996 | 476219356.860703 CANADA | 1995 | 480940219.024827 CANADA | 1994 | 484868253.89729 CANADA | 1993 | 475022171.020181 CANADA | 1992 | 481498674.05355 CHINA | 1998 | 276943985.069973 CHINA | 1997 | 468983265.895989 CHINA | 1996 | 473722386.728218 CHINA | 1995 | 469605084.136939 CHINA | 1994 | 460185229.315634 CHINA | 1993 | 471136703.260642 CHINA | 1992 | 474951842.193392 EGYPT | 1998 | 264288435.176573 EGYPT | 1997 | 460661431.689704 EGYPT | 1996 | 459899790.885107 EGYPT | 1995 | 458708308.911313 EGYPT | 1994 | 464892143.48256 EGYPT | 1993 | 460249679.419814 EGYPT | 1992 | 455083145.317434 ETHIOPIA | 1998 | 282061303.922758 ETHIOPIA | 1997 | 459640721.059703 ETHIOPIA | 1996 | 461774605.702442 ETHIOPIA | 1995 | 467929947.57534 ETHIOPIA | 1994 | 466483677.274392 ETHIOPIA | 1993 | 470053280.126491 ETHIOPIA | 1992 | 458629367.205255 FRANCE | 1998 | 271342850.159183 FRANCE | 1997 | 467309655.913327 FRANCE | 1996 | 466401190.78916 FRANCE | 1995 | 461075782.77504 FRANCE | 1994 | 462866974.799495 FRANCE | 1993 | 462259742.014072 FRANCE | 1992 | 465623771.198371 GERMANY | 1998 | 279221741.514377 GERMANY | 1997 | 477433757.1777 GERMANY | 1996 | 474471530.122168 GERMANY | 1995 | 481578883.367699 GERMANY | 1994 | 481688568.99132 GERMANY | 1993 | 479550052.593151 GERMANY | 1992 | 470403671.71508 INDIA | 1998 | 272836374.855801 INDIA | 1997 | 467107364.172652 INDIA | 1996 | 480040354.265709 INDIA | 1995 | 473103066.643408 INDIA | 1994 | 473488621.812796 INDIA | 1993 | 468554441.639954 INDIA | 1992 | 471871237.628955 INDONESIA | 1998 | 269466354.706309 INDONESIA | 1997 | 458440065.985 INDONESIA | 1996 | 470162689.085634 INDONESIA | 1995 | 458440887.635806 INDONESIA | 1994 | 450807187.473679 INDONESIA | 1993 | 463794666.507818 INDONESIA | 1992 | 457186327.012177 IRAN | 1998 | 273561269.318898 IRAN | 1997 | 462405857.369441 IRAN | 1996 | 463109915.838298 IRAN | 1995 | 472810615.675796 IRAN | 1994 | 466789147.621721 IRAN | 1993 | 462699720.156459 IRAN | 1992 | 457317639.33494 IRAQ | 1998 | 274231793.744518 IRAQ | 1997 | 458520235.47021 IRAQ | 1996 | 468304459.144245 IRAQ | 1995 | 464382059.804652 IRAQ | 1994 | 464925210.984786 IRAQ | 1993 | 466085702.740243 IRAQ | 1992 | 473739274.842162 JAPAN | 1998 | 274498756.557106 JAPAN | 1997 | 471514301.77002 JAPAN | 1996 | 468528173.189558 JAPAN | 1995 | 464582651.248554 JAPAN | 1994 | 471820799.895434 JAPAN | 1993 | 471715946.545624 JAPAN | 1992 | 461623481.945239 JORDAN | 1998 | 268766675.329921 JORDAN | 1997 | 465706836.188426 JORDAN | 1996 | 464743667.210625 JORDAN | 1995 | 461144973.10635 JORDAN | 1994 | 465858356.556524 JORDAN | 1993 | 461690871.078802 JORDAN | 1992 | 462024516.884078 KENYA | 1998 | 277281195.7142 KENYA | 1997 | 472300234.891813 KENYA | 1996 | 471681885.704518 KENYA | 1995 | 472260571.713498 KENYA | 1994 | 469950305.027909 KENYA | 1993 | 470745603.545031 KENYA | 1992 | 474197299.68889 MOROCCO | 1998 | 276094507.755295 MOROCCO | 1997 | 473707897.893924 MOROCCO | 1996 | 471851090.619744 MOROCCO | 1995 | 477737643.905727 MOROCCO | 1994 | 465546927.462592 MOROCCO | 1993 | 472119511.896323 MOROCCO | 1992 | 477108533.148727 MOZAMBIQUE | 1998 | 276381693.001059 MOZAMBIQUE | 1997 | 463655860.787922 MOZAMBIQUE | 1996 | 464255764.021002 MOZAMBIQUE | 1995 | 463474172.738085 MOZAMBIQUE | 1994 | 463196583.727637 MOZAMBIQUE | 1993 | 470712802.234005 MOZAMBIQUE | 1992 | 464409430.299826 PERU | 1998 | 274923798.912272 PERU | 1997 | 465887166.382567 PERU | 1996 | 469425388.38617 PERU | 1995 | 456296071.287699 PERU | 1994 | 466475474.47375 PERU | 1993 | 460271031.949869 PERU | 1992 | 462836121.89859 ROMANIA | 1998 | 272945754.94678 ROMANIA | 1997 | 467930906.609249 ROMANIA | 1996 | 470799906.030245 ROMANIA | 1995 | 468687714.58397 ROMANIA | 1994 | 455573413.198332 ROMANIA | 1993 | 457379152.412678 ROMANIA | 1992 | 473357649.233992 RUSSIA | 1998 | 272070813.628644 RUSSIA | 1997 | 468232174.395847 RUSSIA | 1996 | 459417368.963769 RUSSIA | 1995 | 467695538.517601 RUSSIA | 1994 | 468148690.564654 RUSSIA | 1993 | 466414607.156429 RUSSIA | 1992 | 468834569.521174 SAUDI ARABIA | 1998 | 275241390.322849 SAUDI ARABIA | 1997 | 475561496.736607 SAUDI ARABIA | 1996 | 471679768.944122 SAUDI ARABIA | 1995 | 468125555.633911 SAUDI ARABIA | 1994 | 466674997.118525 SAUDI ARABIA | 1993 | 476336820.311844 SAUDI ARABIA | 1992 | 477110187.968381 UNITED KINGDOM | 1998 | 274233037.417969 UNITED KINGDOM | 1997 | 465089006.371271 UNITED KINGDOM | 1996 | 460994945.77031 UNITED KINGDOM | 1995 | 461253312.245793 UNITED KINGDOM | 1994 | 460971843.615548 UNITED KINGDOM | 1993 | 466841012.179352 UNITED KINGDOM | 1992 | 461591659.221104 UNITED STATES | 1998 | 271155559.49889 UNITED STATES | 1997 | 475524611.582848 UNITED STATES | 1996 | 481184972.359291 UNITED STATES | 1995 | 478706415.518415 UNITED STATES | 1994 | 476269044.988255 UNITED STATES | 1993 | 467529804.362747 UNITED STATES | 1992 | 473688785.407651 VIETNAM | 1998 | 271848015.527156 VIETNAM | 1997 | 458458744.003832 VIETNAM | 1996 | 473494257.641742 VIETNAM | 1995 | 459287489.151834 VIETNAM | 1994 | 466954426.957393 VIETNAM | 1993 | 462712037.798306 VIETNAM | 1992 | 463454942.805595 (175 rows) COMMIT; COMMIT