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 '%deep%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272419657.391843 ALGERIA | 1997 | 457173498.368668 ALGERIA | 1996 | 463939548.910582 ALGERIA | 1995 | 453664902.000978 ALGERIA | 1994 | 453667905.847141 ALGERIA | 1993 | 453144808.930295 ALGERIA | 1992 | 460261659.422981 ARGENTINA | 1998 | 284189131.233963 ARGENTINA | 1997 | 479408340.468055 ARGENTINA | 1996 | 476691116.537385 ARGENTINA | 1995 | 480116801.772463 ARGENTINA | 1994 | 476371094.644192 ARGENTINA | 1993 | 483697304.29315 ARGENTINA | 1992 | 485622649.567706 BRAZIL | 1998 | 277584200.388742 BRAZIL | 1997 | 455301228.765522 BRAZIL | 1996 | 462701142.55582 BRAZIL | 1995 | 465083727.576055 BRAZIL | 1994 | 460547828.95871 BRAZIL | 1993 | 461549340.988822 BRAZIL | 1992 | 462707949.393062 CANADA | 1998 | 272891564.674755 CANADA | 1997 | 469166825.041893 CANADA | 1996 | 477862887.680913 CANADA | 1995 | 466254747.928949 CANADA | 1994 | 475122801.112036 CANADA | 1993 | 469150497.85644 CANADA | 1992 | 468875453.693088 CHINA | 1998 | 274243621.394877 CHINA | 1997 | 458993689.835071 CHINA | 1996 | 466971538.977881 CHINA | 1995 | 459773958.520418 CHINA | 1994 | 466118447.789572 CHINA | 1993 | 461836234.949856 CHINA | 1992 | 469078123.250645 EGYPT | 1998 | 270339540.609697 EGYPT | 1997 | 460074578.912559 EGYPT | 1996 | 466236952.037528 EGYPT | 1995 | 463240828.393016 EGYPT | 1994 | 463950340.564568 EGYPT | 1993 | 467053400.852737 EGYPT | 1992 | 460630321.448169 ETHIOPIA | 1998 | 260486230.871929 ETHIOPIA | 1997 | 452111339.570675 ETHIOPIA | 1996 | 449137016.713332 ETHIOPIA | 1995 | 450190290.772282 ETHIOPIA | 1994 | 447633853.075792 ETHIOPIA | 1993 | 453459591.460739 ETHIOPIA | 1992 | 462578079.357802 FRANCE | 1998 | 264602570.371717 FRANCE | 1997 | 455777101.276487 FRANCE | 1996 | 455122263.142481 FRANCE | 1995 | 456371879.609562 FRANCE | 1994 | 454637115.589989 FRANCE | 1993 | 461106953.309269 FRANCE | 1992 | 448686955.886661 GERMANY | 1998 | 274322644.567127 GERMANY | 1997 | 486429942.067167 GERMANY | 1996 | 477356693.12735 GERMANY | 1995 | 487155877.562448 GERMANY | 1994 | 476843929.706844 GERMANY | 1993 | 478800870.481869 GERMANY | 1992 | 476229624.74421 INDIA | 1998 | 277351885.869738 INDIA | 1997 | 479780625.86293 INDIA | 1996 | 474488706.610163 INDIA | 1995 | 477404470.095992 INDIA | 1994 | 464824875.280089 INDIA | 1993 | 481797042.898424 INDIA | 1992 | 474628891.385157 INDONESIA | 1998 | 271916573.014496 INDONESIA | 1997 | 462646701.758462 INDONESIA | 1996 | 463159777.94127 INDONESIA | 1995 | 454105233.82018 INDONESIA | 1994 | 458099177.015479 INDONESIA | 1993 | 459645333.142938 INDONESIA | 1992 | 458122017.85816 IRAN | 1998 | 264779165.861867 IRAN | 1997 | 461354882.305117 IRAN | 1996 | 466072377.919072 IRAN | 1995 | 470398210.179664 IRAN | 1994 | 468153835.681126 IRAN | 1993 | 466506758.134474 IRAN | 1992 | 466910263.431499 IRAQ | 1998 | 271638565.741963 IRAQ | 1997 | 463636843.727222 IRAQ | 1996 | 470521316.423167 IRAQ | 1995 | 467321739.863651 IRAQ | 1994 | 468104279.363148 IRAQ | 1993 | 468492506.933276 IRAQ | 1992 | 471895854.134612 JAPAN | 1998 | 270078616.998859 JAPAN | 1997 | 465588795.469976 JAPAN | 1996 | 457584492.432182 JAPAN | 1995 | 458088970.743376 JAPAN | 1994 | 468945462.704516 JAPAN | 1993 | 467476378.668939 JAPAN | 1992 | 463432548.216249 JORDAN | 1998 | 270121955.77254 JORDAN | 1997 | 461025483.693753 JORDAN | 1996 | 454645494.558844 JORDAN | 1995 | 463888265.530597 JORDAN | 1994 | 462776355.954313 JORDAN | 1993 | 460443935.683411 JORDAN | 1992 | 458160078.2745 KENYA | 1998 | 275712020.350022 KENYA | 1997 | 466768753.954413 KENYA | 1996 | 469219982.550442 KENYA | 1995 | 464192786.763325 KENYA | 1994 | 469438310.757881 KENYA | 1993 | 474042217.481138 KENYA | 1992 | 476360071.480969 MOROCCO | 1998 | 275988948.427405 MOROCCO | 1997 | 468775142.219675 MOROCCO | 1996 | 463666315.276388 MOROCCO | 1995 | 464030025.594523 MOROCCO | 1994 | 464228295.688117 MOROCCO | 1993 | 460494226.505059 MOROCCO | 1992 | 463264117.366546 MOZAMBIQUE | 1998 | 274918257.475441 MOZAMBIQUE | 1997 | 463308600.446281 MOZAMBIQUE | 1996 | 468795271.406736 MOZAMBIQUE | 1995 | 469260268.736979 MOZAMBIQUE | 1994 | 472256302.486015 MOZAMBIQUE | 1993 | 458397825.083879 MOZAMBIQUE | 1992 | 467377318.199467 PERU | 1998 | 270733707.688483 PERU | 1997 | 460135289.224583 PERU | 1996 | 466808120.747762 PERU | 1995 | 459696406.209849 PERU | 1994 | 456040163.195607 PERU | 1993 | 457120183.458933 PERU | 1992 | 463169635.990365 ROMANIA | 1998 | 269016892.92868 ROMANIA | 1997 | 464474074.392222 ROMANIA | 1996 | 464927546.125053 ROMANIA | 1995 | 454438987.07839 ROMANIA | 1994 | 462412222.555285 ROMANIA | 1993 | 460600494.122199 ROMANIA | 1992 | 465221829.287186 RUSSIA | 1998 | 276433784.513615 RUSSIA | 1997 | 473638111.714375 RUSSIA | 1996 | 473338214.218438 RUSSIA | 1995 | 473278075.261496 RUSSIA | 1994 | 465521527.562152 RUSSIA | 1993 | 471678224.711665 RUSSIA | 1992 | 474339746.603328 SAUDI ARABIA | 1998 | 275176947.588876 SAUDI ARABIA | 1997 | 473308780.419443 SAUDI ARABIA | 1996 | 476011489.710387 SAUDI ARABIA | 1995 | 471262679.333269 SAUDI ARABIA | 1994 | 468480557.664588 SAUDI ARABIA | 1993 | 469415899.894304 SAUDI ARABIA | 1992 | 467331866.031996 UNITED KINGDOM | 1998 | 272207320.550092 UNITED KINGDOM | 1997 | 463263538.963689 UNITED KINGDOM | 1996 | 462687338.562528 UNITED KINGDOM | 1995 | 468857918.772499 UNITED KINGDOM | 1994 | 464647328.160187 UNITED KINGDOM | 1993 | 467426716.717482 UNITED KINGDOM | 1992 | 463539054.50822 UNITED STATES | 1998 | 276779406.324316 UNITED STATES | 1997 | 472149171.549906 UNITED STATES | 1996 | 475359209.464818 UNITED STATES | 1995 | 468061274.164602 UNITED STATES | 1994 | 469559597.888631 UNITED STATES | 1993 | 465369287.376503 UNITED STATES | 1992 | 463949693.769143 VIETNAM | 1998 | 279205892.164299 VIETNAM | 1997 | 469323843.278878 VIETNAM | 1996 | 473427396.842219 VIETNAM | 1995 | 464085758.960241 VIETNAM | 1994 | 471810652.966424 VIETNAM | 1993 | 468737021.507762 VIETNAM | 1992 | 470738435.660952 (175 rows) COMMIT; COMMIT