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 '%turquoise%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 260622651.556123 ALGERIA | 1997 | 456012813.526979 ALGERIA | 1996 | 452738540.40872 ALGERIA | 1995 | 445531446.509862 ALGERIA | 1994 | 453183235.31452 ALGERIA | 1993 | 442293687.135663 ALGERIA | 1992 | 448605566.301154 ARGENTINA | 1998 | 267227868.481457 ARGENTINA | 1997 | 459742124.413194 ARGENTINA | 1996 | 469183749.061744 ARGENTINA | 1995 | 474244546.104778 ARGENTINA | 1994 | 467829756.692943 ARGENTINA | 1993 | 461272555.271918 ARGENTINA | 1992 | 465117592.932943 BRAZIL | 1998 | 266267962.320046 BRAZIL | 1997 | 464159481.612903 BRAZIL | 1996 | 466337304.798709 BRAZIL | 1995 | 460615703.450588 BRAZIL | 1994 | 462135753.500002 BRAZIL | 1993 | 455632409.574623 BRAZIL | 1992 | 466695004.909625 CANADA | 1998 | 278745031.736602 CANADA | 1997 | 482087351.948274 CANADA | 1996 | 485904126.457137 CANADA | 1995 | 480904785.984626 CANADA | 1994 | 484469470.324496 CANADA | 1993 | 486525663.779366 CANADA | 1992 | 489525801.310007 CHINA | 1998 | 269481125.866315 CHINA | 1997 | 444599248.692143 CHINA | 1996 | 450775875.062084 CHINA | 1995 | 448949454.487558 CHINA | 1994 | 445218768.582759 CHINA | 1993 | 454107456.316095 CHINA | 1992 | 450986218.164389 EGYPT | 1998 | 268986697.356248 EGYPT | 1997 | 466702387.725865 EGYPT | 1996 | 469686048.381206 EGYPT | 1995 | 462942350.267141 EGYPT | 1994 | 458259130.610544 EGYPT | 1993 | 469990614.52282 EGYPT | 1992 | 464282633.237361 ETHIOPIA | 1998 | 263722659.464904 ETHIOPIA | 1997 | 452670199.721387 ETHIOPIA | 1996 | 456069549.114256 ETHIOPIA | 1995 | 457300873.281372 ETHIOPIA | 1994 | 454032812.421289 ETHIOPIA | 1993 | 455354652.843096 ETHIOPIA | 1992 | 460647763.90122 FRANCE | 1998 | 267975212.68461 FRANCE | 1997 | 459639657.153522 FRANCE | 1996 | 464476399.838611 FRANCE | 1995 | 460346847.862014 FRANCE | 1994 | 456987156.114257 FRANCE | 1993 | 462323271.532391 FRANCE | 1992 | 456574834.598538 GERMANY | 1998 | 276496602.97405 GERMANY | 1997 | 475149942.759442 GERMANY | 1996 | 478924130.379117 GERMANY | 1995 | 476853150.872939 GERMANY | 1994 | 474056317.932131 GERMANY | 1993 | 471951235.42901 GERMANY | 1992 | 469930067.780192 INDIA | 1998 | 270430744.342076 INDIA | 1997 | 471612750.98675 INDIA | 1996 | 473566105.308461 INDIA | 1995 | 468447039.496656 INDIA | 1994 | 465371012.899783 INDIA | 1993 | 463473243.65047 INDIA | 1992 | 466364890.379921 INDONESIA | 1998 | 270787245.085364 INDONESIA | 1997 | 458708145.086846 INDONESIA | 1996 | 462766728.113645 INDONESIA | 1995 | 464185204.737753 INDONESIA | 1994 | 461314230.335398 INDONESIA | 1993 | 463891170.522058 INDONESIA | 1992 | 464917945.599643 IRAN | 1998 | 269946726.915245 IRAN | 1997 | 460270885.028558 IRAN | 1996 | 464908304.119112 IRAN | 1995 | 461543506.874539 IRAN | 1994 | 464801944.523721 IRAN | 1993 | 466853838.264966 IRAN | 1992 | 458320866.606117 IRAQ | 1998 | 279912390.265668 IRAQ | 1997 | 476823157.865917 IRAQ | 1996 | 476218392.933512 IRAQ | 1995 | 483765298.203705 IRAQ | 1994 | 483114535.909325 IRAQ | 1993 | 477266236.250514 IRAQ | 1992 | 479613929.124532 JAPAN | 1998 | 271099171.531609 JAPAN | 1997 | 463791509.894338 JAPAN | 1996 | 466858212.492525 JAPAN | 1995 | 464658832.702469 JAPAN | 1994 | 460653594.959804 JAPAN | 1993 | 454761916.912096 JAPAN | 1992 | 456219026.694632 JORDAN | 1998 | 267239404.713501 JORDAN | 1997 | 447654874.700062 JORDAN | 1996 | 452187546.089973 JORDAN | 1995 | 452469925.723402 JORDAN | 1994 | 458128872.225419 JORDAN | 1993 | 455665996.959377 JORDAN | 1992 | 456518878.44322 KENYA | 1998 | 272390359.607555 KENYA | 1997 | 471970371.081562 KENYA | 1996 | 469922053.275664 KENYA | 1995 | 462890058.915444 KENYA | 1994 | 471076905.943442 KENYA | 1993 | 470266826.871124 KENYA | 1992 | 466805644.97301 MOROCCO | 1998 | 269950413.535178 MOROCCO | 1997 | 466788251.758409 MOROCCO | 1996 | 471529587.988556 MOROCCO | 1995 | 467951626.498227 MOROCCO | 1994 | 463543937.975935 MOROCCO | 1993 | 460024648.63738 MOROCCO | 1992 | 470709523.058579 MOZAMBIQUE | 1998 | 276489071.183304 MOZAMBIQUE | 1997 | 462938722.294177 MOZAMBIQUE | 1996 | 461967195.857225 MOZAMBIQUE | 1995 | 465195610.931695 MOZAMBIQUE | 1994 | 466451503.241011 MOZAMBIQUE | 1993 | 468158854.069225 MOZAMBIQUE | 1992 | 458084788.811302 PERU | 1998 | 272215319.000186 PERU | 1997 | 462407067.709248 PERU | 1996 | 457276362.345692 PERU | 1995 | 463475527.192654 PERU | 1994 | 465208785.184994 PERU | 1993 | 466408718.118716 PERU | 1992 | 459424250.924629 ROMANIA | 1998 | 271692518.124056 ROMANIA | 1997 | 459000371.678899 ROMANIA | 1996 | 454927235.140212 ROMANIA | 1995 | 465448857.743194 ROMANIA | 1994 | 467165559.779297 ROMANIA | 1993 | 457998255.167185 ROMANIA | 1992 | 468637607.648433 RUSSIA | 1998 | 279341636.882417 RUSSIA | 1997 | 475666779.936923 RUSSIA | 1996 | 470862835.874758 RUSSIA | 1995 | 470286235.413752 RUSSIA | 1994 | 469806634.104847 RUSSIA | 1993 | 472689043.395465 RUSSIA | 1992 | 473010542.549151 SAUDI ARABIA | 1998 | 271033980.961781 SAUDI ARABIA | 1997 | 466611182.896823 SAUDI ARABIA | 1996 | 468984803.760671 SAUDI ARABIA | 1995 | 467672413.416896 SAUDI ARABIA | 1994 | 469051929.011509 SAUDI ARABIA | 1993 | 465452343.23972 SAUDI ARABIA | 1992 | 468785803.07421 UNITED KINGDOM | 1998 | 269038580.601294 UNITED KINGDOM | 1997 | 455362059.538417 UNITED KINGDOM | 1996 | 456438140.69801 UNITED KINGDOM | 1995 | 458265601.979291 UNITED KINGDOM | 1994 | 453511622.168587 UNITED KINGDOM | 1993 | 457862933.154293 UNITED KINGDOM | 1992 | 460969507.668331 UNITED STATES | 1998 | 282419787.771344 UNITED STATES | 1997 | 473089660.524113 UNITED STATES | 1996 | 482646183.321533 UNITED STATES | 1995 | 476912864.952739 UNITED STATES | 1994 | 473458009.210456 UNITED STATES | 1993 | 475075814.572384 UNITED STATES | 1992 | 488268098.079876 VIETNAM | 1998 | 271195045.570908 VIETNAM | 1997 | 464078638.063186 VIETNAM | 1996 | 475521091.45457 VIETNAM | 1995 | 466942918.859221 VIETNAM | 1994 | 466996252.939459 VIETNAM | 1993 | 474030130.021684 VIETNAM | 1992 | 464557265.914737 (175 rows) COMMIT; COMMIT