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 | 262289617.371764 ALGERIA | 1997 | 451368621.494795 ALGERIA | 1996 | 459592866.097541 ALGERIA | 1995 | 448851683.973794 ALGERIA | 1994 | 460636950.973103 ALGERIA | 1993 | 457626674.045594 ALGERIA | 1992 | 450548211.308325 ARGENTINA | 1998 | 275712369.868804 ARGENTINA | 1997 | 469255167.278549 ARGENTINA | 1996 | 470713917.703969 ARGENTINA | 1995 | 466515973.284838 ARGENTINA | 1994 | 467929843.582358 ARGENTINA | 1993 | 466546470.672569 ARGENTINA | 1992 | 471940553.972238 BRAZIL | 1998 | 276470701.359107 BRAZIL | 1997 | 462666297.492217 BRAZIL | 1996 | 462524038.107837 BRAZIL | 1995 | 458503955.758033 BRAZIL | 1994 | 460134249.924127 BRAZIL | 1993 | 459137310.723793 BRAZIL | 1992 | 461860255.612345 CANADA | 1998 | 281547947.721356 CANADA | 1997 | 473832093.29832 CANADA | 1996 | 481968525.74667 CANADA | 1995 | 477648000.822823 CANADA | 1994 | 484960497.477522 CANADA | 1993 | 476780235.078364 CANADA | 1992 | 483449856.299622 CHINA | 1998 | 272399183.136477 CHINA | 1997 | 463384525.852035 CHINA | 1996 | 464831370.71567 CHINA | 1995 | 464879483.122076 CHINA | 1994 | 466965543.044251 CHINA | 1993 | 468724893.162769 CHINA | 1992 | 470894105.156256 EGYPT | 1998 | 272072493.31297 EGYPT | 1997 | 473236682.970624 EGYPT | 1996 | 470517737.842713 EGYPT | 1995 | 470671258.872122 EGYPT | 1994 | 471467921.104235 EGYPT | 1993 | 467297345.772735 EGYPT | 1992 | 457300468.658631 ETHIOPIA | 1998 | 262814646.691084 ETHIOPIA | 1997 | 443709213.569923 ETHIOPIA | 1996 | 451574250.34964 ETHIOPIA | 1995 | 459980571.814495 ETHIOPIA | 1994 | 453217282.632405 ETHIOPIA | 1993 | 448440483.514291 ETHIOPIA | 1992 | 459952165.197511 FRANCE | 1998 | 272771265.220671 FRANCE | 1997 | 464811098.763768 FRANCE | 1996 | 476354907.885909 FRANCE | 1995 | 471936305.825864 FRANCE | 1994 | 473450443.613541 FRANCE | 1993 | 469311302.333911 FRANCE | 1992 | 471364229.256158 GERMANY | 1998 | 276641585.401021 GERMANY | 1997 | 468531637.309053 GERMANY | 1996 | 472060904.880043 GERMANY | 1995 | 471538245.418972 GERMANY | 1994 | 469867677.492744 GERMANY | 1993 | 470426438.771247 GERMANY | 1992 | 471091738.561435 INDIA | 1998 | 273373525.494083 INDIA | 1997 | 476575580.042505 INDIA | 1996 | 482475375.815135 INDIA | 1995 | 477726373.361709 INDIA | 1994 | 472827688.561695 INDIA | 1993 | 482057171.937566 INDIA | 1992 | 484679868.507281 INDONESIA | 1998 | 270945279.790944 INDONESIA | 1997 | 459397882.480523 INDONESIA | 1996 | 470041273.193073 INDONESIA | 1995 | 462733223.413598 INDONESIA | 1994 | 463803955.760913 INDONESIA | 1993 | 463441738.525874 INDONESIA | 1992 | 468873253.567446 IRAN | 1998 | 272099637.00839 IRAN | 1997 | 467155871.088868 IRAN | 1996 | 461599259.506801 IRAN | 1995 | 469118085.118261 IRAN | 1994 | 465651058.21069 IRAN | 1993 | 470397666.522507 IRAN | 1992 | 464675915.272918 IRAQ | 1998 | 279140171.059 IRAQ | 1997 | 479806669.363722 IRAQ | 1996 | 478410559.184558 IRAQ | 1995 | 475419932.491437 IRAQ | 1994 | 472948562.10159 IRAQ | 1993 | 473242137.501417 IRAQ | 1992 | 469504472.338735 JAPAN | 1998 | 272648466.902788 JAPAN | 1997 | 469634826.670483 JAPAN | 1996 | 465343053.808746 JAPAN | 1995 | 463965760.605401 JAPAN | 1994 | 469667408.103748 JAPAN | 1993 | 471594998.196119 JAPAN | 1992 | 465877017.27822 JORDAN | 1998 | 275550797.488379 JORDAN | 1997 | 454530795.180731 JORDAN | 1996 | 465780241.619735 JORDAN | 1995 | 468063409.890032 JORDAN | 1994 | 469380253.045284 JORDAN | 1993 | 461618380.477219 JORDAN | 1992 | 472663937.371427 KENYA | 1998 | 276507176.42345 KENYA | 1997 | 476835271.49557 KENYA | 1996 | 477511938.849338 KENYA | 1995 | 468241462.23709 KENYA | 1994 | 479379630.316976 KENYA | 1993 | 480226497.544245 KENYA | 1992 | 466123643.587034 MOROCCO | 1998 | 276228024.170983 MOROCCO | 1997 | 477673968.255818 MOROCCO | 1996 | 484435856.271271 MOROCCO | 1995 | 476894323.603462 MOROCCO | 1994 | 482420663.822865 MOROCCO | 1993 | 477417169.054418 MOROCCO | 1992 | 483537467.131523 MOZAMBIQUE | 1998 | 276746118.342555 MOZAMBIQUE | 1997 | 461271367.761313 MOZAMBIQUE | 1996 | 460270733.976677 MOZAMBIQUE | 1995 | 459586703.934704 MOZAMBIQUE | 1994 | 457535451.21273 MOZAMBIQUE | 1993 | 461471194.628955 MOZAMBIQUE | 1992 | 457923849.834274 PERU | 1998 | 271532436.809797 PERU | 1997 | 470244510.462003 PERU | 1996 | 466319804.899604 PERU | 1995 | 463228797.988744 PERU | 1994 | 464334510.467213 PERU | 1993 | 466277436.318014 PERU | 1992 | 468934792.211731 ROMANIA | 1998 | 277060692.509682 ROMANIA | 1997 | 475361710.160247 ROMANIA | 1996 | 478936388.059071 ROMANIA | 1995 | 474118060.652432 ROMANIA | 1994 | 474082196.556677 ROMANIA | 1993 | 481700869.457292 ROMANIA | 1992 | 478258474.277332 RUSSIA | 1998 | 274806983.578864 RUSSIA | 1997 | 470822435.112943 RUSSIA | 1996 | 462691037.358151 RUSSIA | 1995 | 456007370.495541 RUSSIA | 1994 | 467120518.246463 RUSSIA | 1993 | 466753953.729597 RUSSIA | 1992 | 476119387.66882 SAUDI ARABIA | 1998 | 270142086.952137 SAUDI ARABIA | 1997 | 459423946.193013 SAUDI ARABIA | 1996 | 462063110.595316 SAUDI ARABIA | 1995 | 469935102.76207 SAUDI ARABIA | 1994 | 473939353.646207 SAUDI ARABIA | 1993 | 465272513.026159 SAUDI ARABIA | 1992 | 475865470.126734 UNITED KINGDOM | 1998 | 274943863.863475 UNITED KINGDOM | 1997 | 461122660.21153 UNITED KINGDOM | 1996 | 467726551.362265 UNITED KINGDOM | 1995 | 470894859.20774 UNITED KINGDOM | 1994 | 470582147.103605 UNITED KINGDOM | 1993 | 462125130.517637 UNITED KINGDOM | 1992 | 466593138.732893 UNITED STATES | 1998 | 276860303.365831 UNITED STATES | 1997 | 474986361.157455 UNITED STATES | 1996 | 476850509.385865 UNITED STATES | 1995 | 475357415.954681 UNITED STATES | 1994 | 476922452.899563 UNITED STATES | 1993 | 471364554.703052 UNITED STATES | 1992 | 476772398.597431 VIETNAM | 1998 | 277750507.056259 VIETNAM | 1997 | 465657340.102322 VIETNAM | 1996 | 480484865.730471 VIETNAM | 1995 | 474758145.110941 VIETNAM | 1994 | 469942910.847596 VIETNAM | 1993 | 468634538.582653 VIETNAM | 1992 | 468936931.63115 (175 rows) COMMIT; COMMIT