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 '%ivory%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 262262391.3388 ALGERIA | 1997 | 458481687.947856 ALGERIA | 1996 | 461558650.404503 ALGERIA | 1995 | 461438310.551776 ALGERIA | 1994 | 459609350.031579 ALGERIA | 1993 | 454491643.9548 ALGERIA | 1992 | 468864393.272305 ARGENTINA | 1998 | 267351731.804062 ARGENTINA | 1997 | 455657389.693699 ARGENTINA | 1996 | 460607448.586733 ARGENTINA | 1995 | 454004427.282171 ARGENTINA | 1994 | 455295718.60542 ARGENTINA | 1993 | 459148482.252771 ARGENTINA | 1992 | 459224050.062502 BRAZIL | 1998 | 272125955.458826 BRAZIL | 1997 | 468162175.09308 BRAZIL | 1996 | 474308761.560992 BRAZIL | 1995 | 462974512.967501 BRAZIL | 1994 | 462429268.478756 BRAZIL | 1993 | 464178619.678963 BRAZIL | 1992 | 471775237.775288 CANADA | 1998 | 271940447.151441 CANADA | 1997 | 469889639.730278 CANADA | 1996 | 464871416.046471 CANADA | 1995 | 461281972.972401 CANADA | 1994 | 463873909.431028 CANADA | 1993 | 476638021.578369 CANADA | 1992 | 461929533.632943 CHINA | 1998 | 277115694.262879 CHINA | 1997 | 466937522.601443 CHINA | 1996 | 465479874.821838 CHINA | 1995 | 468869050.580704 CHINA | 1994 | 473082387.209176 CHINA | 1993 | 472189745.141115 CHINA | 1992 | 467840014.361831 EGYPT | 1998 | 277394216.689708 EGYPT | 1997 | 462508632.243097 EGYPT | 1996 | 463057518.794992 EGYPT | 1995 | 461197320.582217 EGYPT | 1994 | 458815484.629448 EGYPT | 1993 | 459425491.653976 EGYPT | 1992 | 467096059.224787 ETHIOPIA | 1998 | 275456605.278988 ETHIOPIA | 1997 | 459936812.786631 ETHIOPIA | 1996 | 464624938.254141 ETHIOPIA | 1995 | 458067451.996212 ETHIOPIA | 1994 | 462942085.906723 ETHIOPIA | 1993 | 457788418.833933 ETHIOPIA | 1992 | 457350606.910843 FRANCE | 1998 | 271980437.658735 FRANCE | 1997 | 455604732.1776 FRANCE | 1996 | 456556722.451097 FRANCE | 1995 | 460517439.609678 FRANCE | 1994 | 455575663.186129 FRANCE | 1993 | 455262879.160427 FRANCE | 1992 | 450630325.355189 GERMANY | 1998 | 275717413.91999 GERMANY | 1997 | 472990424.474753 GERMANY | 1996 | 471632679.570606 GERMANY | 1995 | 468799054.773522 GERMANY | 1994 | 468457756.874096 GERMANY | 1993 | 466076539.486236 GERMANY | 1992 | 469555217.302239 INDIA | 1998 | 279204489.522854 INDIA | 1997 | 478462811.439333 INDIA | 1996 | 473591402.870992 INDIA | 1995 | 474717373.758183 INDIA | 1994 | 475292577.912413 INDIA | 1993 | 476666967.030225 INDIA | 1992 | 483037490.70375 INDONESIA | 1998 | 265733339.351508 INDONESIA | 1997 | 454587320.416562 INDONESIA | 1996 | 463193476.232147 INDONESIA | 1995 | 455377077.03854 INDONESIA | 1994 | 457875876.165824 INDONESIA | 1993 | 463352877.861918 INDONESIA | 1992 | 461588146.995523 IRAN | 1998 | 267251501.62061 IRAN | 1997 | 466842443.711374 IRAN | 1996 | 468377387.704399 IRAN | 1995 | 464694961.574596 IRAN | 1994 | 461291448.972955 IRAN | 1993 | 464707583.126509 IRAN | 1992 | 465641027.91944 IRAQ | 1998 | 279114239.449094 IRAQ | 1997 | 469772214.511561 IRAQ | 1996 | 466883538.947416 IRAQ | 1995 | 463527741.636421 IRAQ | 1994 | 466078917.412505 IRAQ | 1993 | 466927459.583223 IRAQ | 1992 | 470547442.887042 JAPAN | 1998 | 269853284.718404 JAPAN | 1997 | 465310443.201543 JAPAN | 1996 | 470167270.954221 JAPAN | 1995 | 462454884.34715 JAPAN | 1994 | 468262377.706939 JAPAN | 1993 | 469608026.769892 JAPAN | 1992 | 460699362.302393 JORDAN | 1998 | 270880997.69897 JORDAN | 1997 | 460082422.405053 JORDAN | 1996 | 459948223.554227 JORDAN | 1995 | 461784215.087834 JORDAN | 1994 | 459842593.024826 JORDAN | 1993 | 457233050.745449 JORDAN | 1992 | 459832363.843507 KENYA | 1998 | 279660383.913174 KENYA | 1997 | 469125704.245393 KENYA | 1996 | 471188300.488266 KENYA | 1995 | 470167641.234228 KENYA | 1994 | 480148318.020322 KENYA | 1993 | 466463332.366868 KENYA | 1992 | 477928860.315623 MOROCCO | 1998 | 270871386.539496 MOROCCO | 1997 | 459582531.413923 MOROCCO | 1996 | 461522673.89391 MOROCCO | 1995 | 460989408.86008 MOROCCO | 1994 | 460571078.020443 MOROCCO | 1993 | 462139149.294597 MOROCCO | 1992 | 460099422.195126 MOZAMBIQUE | 1998 | 268852327.182653 MOZAMBIQUE | 1997 | 456551825.809666 MOZAMBIQUE | 1996 | 463818604.63061 MOZAMBIQUE | 1995 | 456669284.839329 MOZAMBIQUE | 1994 | 451717929.851897 MOZAMBIQUE | 1993 | 465016917.695808 MOZAMBIQUE | 1992 | 461927413.480678 PERU | 1998 | 275144520.386004 PERU | 1997 | 476158733.809847 PERU | 1996 | 472580431.994745 PERU | 1995 | 478173772.874158 PERU | 1994 | 468042782.232881 PERU | 1993 | 467241129.395433 PERU | 1992 | 469021262.201265 ROMANIA | 1998 | 272219681.144645 ROMANIA | 1997 | 460194293.068871 ROMANIA | 1996 | 471844290.05469 ROMANIA | 1995 | 466627885.180231 ROMANIA | 1994 | 465326320.735035 ROMANIA | 1993 | 457021013.452715 ROMANIA | 1992 | 466763946.220893 RUSSIA | 1998 | 275159006.787657 RUSSIA | 1997 | 467265448.022928 RUSSIA | 1996 | 473854483.046358 RUSSIA | 1995 | 469383762.403612 RUSSIA | 1994 | 467043738.520809 RUSSIA | 1993 | 474867653.242636 RUSSIA | 1992 | 466069944.079817 SAUDI ARABIA | 1998 | 265683372.642674 SAUDI ARABIA | 1997 | 470324485.83886 SAUDI ARABIA | 1996 | 468180120.208699 SAUDI ARABIA | 1995 | 465403340.524314 SAUDI ARABIA | 1994 | 472227164.920943 SAUDI ARABIA | 1993 | 464701433.388377 SAUDI ARABIA | 1992 | 477020761.385502 UNITED KINGDOM | 1998 | 267161035.278471 UNITED KINGDOM | 1997 | 454083249.005633 UNITED KINGDOM | 1996 | 460726301.431305 UNITED KINGDOM | 1995 | 455027620.569662 UNITED KINGDOM | 1994 | 455584190.013814 UNITED KINGDOM | 1993 | 454245807.612823 UNITED KINGDOM | 1992 | 461893329.717178 UNITED STATES | 1998 | 272302577.343466 UNITED STATES | 1997 | 469315574.605834 UNITED STATES | 1996 | 475404954.821413 UNITED STATES | 1995 | 464654613.003267 UNITED STATES | 1994 | 472425966.458137 UNITED STATES | 1993 | 470497567.669967 UNITED STATES | 1992 | 459438339.140472 VIETNAM | 1998 | 273134069.285625 VIETNAM | 1997 | 455993787.229693 VIETNAM | 1996 | 462757596.016877 VIETNAM | 1995 | 455558091.311961 VIETNAM | 1994 | 460649377.810063 VIETNAM | 1993 | 459661518.799526 VIETNAM | 1992 | 459649588.983755 (175 rows) COMMIT; COMMIT