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 '%lemon%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265421082.54073 ALGERIA | 1997 | 447503089.209838 ALGERIA | 1996 | 455299986.732839 ALGERIA | 1995 | 451398187.177125 ALGERIA | 1994 | 458089168.893003 ALGERIA | 1993 | 453445240.373152 ALGERIA | 1992 | 446038604.661683 ARGENTINA | 1998 | 276258047.56644 ARGENTINA | 1997 | 464336308.229794 ARGENTINA | 1996 | 463783952.577767 ARGENTINA | 1995 | 467061759.221076 ARGENTINA | 1994 | 470719316.2433 ARGENTINA | 1993 | 464990532.313128 ARGENTINA | 1992 | 458367269.197607 BRAZIL | 1998 | 280677672.556149 BRAZIL | 1997 | 479563281.529176 BRAZIL | 1996 | 481320038.838244 BRAZIL | 1995 | 465569284.408417 BRAZIL | 1994 | 465081022.127235 BRAZIL | 1993 | 473902680.138207 BRAZIL | 1992 | 474698239.115224 CANADA | 1998 | 276638899.459923 CANADA | 1997 | 475741199.667643 CANADA | 1996 | 480140031.329845 CANADA | 1995 | 476918382.022315 CANADA | 1994 | 480076434.994624 CANADA | 1993 | 474377385.810588 CANADA | 1992 | 476740997.499079 CHINA | 1998 | 266382734.949721 CHINA | 1997 | 466621245.340478 CHINA | 1996 | 468486939.973247 CHINA | 1995 | 463014197.792984 CHINA | 1994 | 458804518.365415 CHINA | 1993 | 464185245.17425 CHINA | 1992 | 462510404.747976 EGYPT | 1998 | 268590489.407329 EGYPT | 1997 | 458986802.30198 EGYPT | 1996 | 463063566.095092 EGYPT | 1995 | 454197643.748884 EGYPT | 1994 | 466422871.591577 EGYPT | 1993 | 472486459.821919 EGYPT | 1992 | 464950732.291773 ETHIOPIA | 1998 | 275314387.466921 ETHIOPIA | 1997 | 461110035.340674 ETHIOPIA | 1996 | 470068393.655348 ETHIOPIA | 1995 | 468792655.890385 ETHIOPIA | 1994 | 468645227.522086 ETHIOPIA | 1993 | 467519794.133194 ETHIOPIA | 1992 | 464019940.401441 FRANCE | 1998 | 268350151.226115 FRANCE | 1997 | 453415349.501865 FRANCE | 1996 | 466650153.729947 FRANCE | 1995 | 455588942.136819 FRANCE | 1994 | 460323882.499634 FRANCE | 1993 | 458795445.13718 FRANCE | 1992 | 450193993.833575 GERMANY | 1998 | 278971579.013883 GERMANY | 1997 | 479192587.225643 GERMANY | 1996 | 479503006.626226 GERMANY | 1995 | 475186577.862332 GERMANY | 1994 | 472652581.893352 GERMANY | 1993 | 466367639.600258 GERMANY | 1992 | 482557128.05998 INDIA | 1998 | 279570544.988495 INDIA | 1997 | 478009281.651601 INDIA | 1996 | 478272836.069151 INDIA | 1995 | 473122088.510696 INDIA | 1994 | 476304257.638115 INDIA | 1993 | 472464994.580985 INDIA | 1992 | 482838285.233584 INDONESIA | 1998 | 269816250.6044 INDONESIA | 1997 | 453755426.39785 INDONESIA | 1996 | 452179640.860544 INDONESIA | 1995 | 452244607.865204 INDONESIA | 1994 | 453401083.238401 INDONESIA | 1993 | 460331546.370453 INDONESIA | 1992 | 459719760.601292 IRAN | 1998 | 269234522.455259 IRAN | 1997 | 463638848.020996 IRAN | 1996 | 472061820.070869 IRAN | 1995 | 464338138.282337 IRAN | 1994 | 464847457.728026 IRAN | 1993 | 463499145.391066 IRAN | 1992 | 471001787.934202 IRAQ | 1998 | 279849256.270134 IRAQ | 1997 | 476480951.508741 IRAQ | 1996 | 473532532.473512 IRAQ | 1995 | 477437560.777841 IRAQ | 1994 | 477170164.081064 IRAQ | 1993 | 478296286.541086 IRAQ | 1992 | 473623277.292257 JAPAN | 1998 | 266825934.930175 JAPAN | 1997 | 464442353.229579 JAPAN | 1996 | 466254628.117597 JAPAN | 1995 | 466243580.548188 JAPAN | 1994 | 467696215.958475 JAPAN | 1993 | 467402043.073315 JAPAN | 1992 | 468206593.736586 JORDAN | 1998 | 273647204.594767 JORDAN | 1997 | 463901213.402054 JORDAN | 1996 | 470325583.346038 JORDAN | 1995 | 473299838.960938 JORDAN | 1994 | 465492843.936179 JORDAN | 1993 | 463505678.7698 JORDAN | 1992 | 456022743.46127 KENYA | 1998 | 274694727.638431 KENYA | 1997 | 466082094.775116 KENYA | 1996 | 468249185.49742 KENYA | 1995 | 466731764.715181 KENYA | 1994 | 466019909.225421 KENYA | 1993 | 470271083.442952 KENYA | 1992 | 472048513.196141 MOROCCO | 1998 | 271127747.263782 MOROCCO | 1997 | 465093351.782814 MOROCCO | 1996 | 465828571.905888 MOROCCO | 1995 | 464133553.787127 MOROCCO | 1994 | 472946358.262312 MOROCCO | 1993 | 468020692.99543 MOROCCO | 1992 | 466860843.606295 MOZAMBIQUE | 1998 | 268125313.96714 MOZAMBIQUE | 1997 | 464536264.681578 MOZAMBIQUE | 1996 | 466622017.60816 MOZAMBIQUE | 1995 | 465088366.99579 MOZAMBIQUE | 1994 | 457180453.344302 MOZAMBIQUE | 1993 | 455451791.306483 MOZAMBIQUE | 1992 | 460287993.142129 PERU | 1998 | 274000054.839523 PERU | 1997 | 471406890.619155 PERU | 1996 | 472195019.060561 PERU | 1995 | 473788926.652219 PERU | 1994 | 465033559.379411 PERU | 1993 | 471191228.018057 PERU | 1992 | 475796586.464382 ROMANIA | 1998 | 270153942.534678 ROMANIA | 1997 | 472654005.144434 ROMANIA | 1996 | 470090292.333981 ROMANIA | 1995 | 465397037.746008 ROMANIA | 1994 | 462614457.196511 ROMANIA | 1993 | 473735196.221133 ROMANIA | 1992 | 473328836.988673 RUSSIA | 1998 | 279512544.460109 RUSSIA | 1997 | 465940793.088533 RUSSIA | 1996 | 475088059.486818 RUSSIA | 1995 | 469333909.726823 RUSSIA | 1994 | 468208892.123586 RUSSIA | 1993 | 472785218.597902 RUSSIA | 1992 | 466101088.600756 SAUDI ARABIA | 1998 | 274378527.187779 SAUDI ARABIA | 1997 | 467597651.310097 SAUDI ARABIA | 1996 | 467060482.675237 SAUDI ARABIA | 1995 | 473751337.865233 SAUDI ARABIA | 1994 | 463187040.366315 SAUDI ARABIA | 1993 | 470319607.756687 SAUDI ARABIA | 1992 | 475351132.372009 UNITED KINGDOM | 1998 | 270434662.13356 UNITED KINGDOM | 1997 | 458293918.319462 UNITED KINGDOM | 1996 | 462745425.776923 UNITED KINGDOM | 1995 | 463274336.493201 UNITED KINGDOM | 1994 | 458771436.193586 UNITED KINGDOM | 1993 | 462089862.77299 UNITED KINGDOM | 1992 | 460409058.815485 UNITED STATES | 1998 | 273954354.030765 UNITED STATES | 1997 | 471885294.860957 UNITED STATES | 1996 | 481337640.344767 UNITED STATES | 1995 | 472022816.745923 UNITED STATES | 1994 | 474458682.952343 UNITED STATES | 1993 | 487689680.784437 UNITED STATES | 1992 | 477465265.109388 VIETNAM | 1998 | 269966522.874994 VIETNAM | 1997 | 462842413.491459 VIETNAM | 1996 | 472346545.644432 VIETNAM | 1995 | 467601530.321855 VIETNAM | 1994 | 472081627.087449 VIETNAM | 1993 | 470706390.893425 VIETNAM | 1992 | 466534527.578682 (175 rows) COMMIT; COMMIT