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 '%coral%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271613389.414752 ALGERIA | 1997 | 462468349.295931 ALGERIA | 1996 | 469933603.48918 ALGERIA | 1995 | 470197462.07494 ALGERIA | 1994 | 467776320.888198 ALGERIA | 1993 | 469389895.183059 ALGERIA | 1992 | 457062490.390672 ARGENTINA | 1998 | 276139109.442492 ARGENTINA | 1997 | 459166817.507934 ARGENTINA | 1996 | 470218877.042073 ARGENTINA | 1995 | 466844437.793258 ARGENTINA | 1994 | 469887360.782274 ARGENTINA | 1993 | 471977628.681397 ARGENTINA | 1992 | 469086715.13911 BRAZIL | 1998 | 275630232.127488 BRAZIL | 1997 | 467393167.42633 BRAZIL | 1996 | 471101313.025006 BRAZIL | 1995 | 468795790.211551 BRAZIL | 1994 | 462708302.209621 BRAZIL | 1993 | 467028140.312838 BRAZIL | 1992 | 459251113.478991 CANADA | 1998 | 281005882.883078 CANADA | 1997 | 470188748.100651 CANADA | 1996 | 472781141.925312 CANADA | 1995 | 475304856.768488 CANADA | 1994 | 484576458.13448 CANADA | 1993 | 472155733.867768 CANADA | 1992 | 471439338.672973 CHINA | 1998 | 272293738.442963 CHINA | 1997 | 462316733.708289 CHINA | 1996 | 455867146.715585 CHINA | 1995 | 459618679.483519 CHINA | 1994 | 453399521.992833 CHINA | 1993 | 454057713.692943 CHINA | 1992 | 471196468.42933 EGYPT | 1998 | 277161486.946585 EGYPT | 1997 | 467068473.825122 EGYPT | 1996 | 476350930.446473 EGYPT | 1995 | 464324243.479058 EGYPT | 1994 | 465235772.048924 EGYPT | 1993 | 471244985.115407 EGYPT | 1992 | 465669704.948815 ETHIOPIA | 1998 | 258763830.223121 ETHIOPIA | 1997 | 453594778.228086 ETHIOPIA | 1996 | 454207613.603492 ETHIOPIA | 1995 | 460941114.139794 ETHIOPIA | 1994 | 458024928.842151 ETHIOPIA | 1993 | 451517005.535173 ETHIOPIA | 1992 | 462000748.557262 FRANCE | 1998 | 269205535.393797 FRANCE | 1997 | 460115825.975482 FRANCE | 1996 | 456753550.337697 FRANCE | 1995 | 462217789.021104 FRANCE | 1994 | 461611165.442315 FRANCE | 1993 | 462719235.404847 FRANCE | 1992 | 456044116.176163 GERMANY | 1998 | 272432467.478868 GERMANY | 1997 | 475954447.906882 GERMANY | 1996 | 463502471.159035 GERMANY | 1995 | 468079318.76318 GERMANY | 1994 | 474847213.25081 GERMANY | 1993 | 478291646.375557 GERMANY | 1992 | 467751606.407907 INDIA | 1998 | 278325192.477359 INDIA | 1997 | 477528173.346535 INDIA | 1996 | 479574641.001152 INDIA | 1995 | 478960023.538185 INDIA | 1994 | 476167614.225318 INDIA | 1993 | 478494520.850479 INDIA | 1992 | 475914837.695334 INDONESIA | 1998 | 270089578.917873 INDONESIA | 1997 | 461977278.726432 INDONESIA | 1996 | 466101455.520581 INDONESIA | 1995 | 466066334.357124 INDONESIA | 1994 | 465216776.185099 INDONESIA | 1993 | 462559893.77347 INDONESIA | 1992 | 471100199.456812 IRAN | 1998 | 278941613.112791 IRAN | 1997 | 476318774.077313 IRAN | 1996 | 473402221.898233 IRAN | 1995 | 468850985.636228 IRAN | 1994 | 464370843.164779 IRAN | 1993 | 465496426.137752 IRAN | 1992 | 468082195.040091 IRAQ | 1998 | 283558613.798042 IRAQ | 1997 | 472089815.795727 IRAQ | 1996 | 471361860.909783 IRAQ | 1995 | 475071830.49182 IRAQ | 1994 | 473237225.669842 IRAQ | 1993 | 471070915.084445 IRAQ | 1992 | 470499299.035689 JAPAN | 1998 | 271785297.50263 JAPAN | 1997 | 467622565.386977 JAPAN | 1996 | 468069757.022722 JAPAN | 1995 | 463230916.812734 JAPAN | 1994 | 474001276.895337 JAPAN | 1993 | 463283190.640279 JAPAN | 1992 | 460911096.988975 JORDAN | 1998 | 268200902.915008 JORDAN | 1997 | 460621852.729245 JORDAN | 1996 | 450360258.433098 JORDAN | 1995 | 455984673.513315 JORDAN | 1994 | 464699581.413343 JORDAN | 1993 | 466907248.339288 JORDAN | 1992 | 460275541.626505 KENYA | 1998 | 277058665.120638 KENYA | 1997 | 478061584.6238 KENYA | 1996 | 468593827.985764 KENYA | 1995 | 479927547.554277 KENYA | 1994 | 468594283.235677 KENYA | 1993 | 474299243.295299 KENYA | 1992 | 478134621.594147 MOROCCO | 1998 | 278531986.452734 MOROCCO | 1997 | 477157913.714594 MOROCCO | 1996 | 473035805.290309 MOROCCO | 1995 | 466108914.69044 MOROCCO | 1994 | 461620093.743177 MOROCCO | 1993 | 471521694.98658 MOROCCO | 1992 | 477182392.478131 MOZAMBIQUE | 1998 | 268550564.417566 MOZAMBIQUE | 1997 | 465006657.358337 MOZAMBIQUE | 1996 | 463783888.075906 MOZAMBIQUE | 1995 | 466361651.762868 MOZAMBIQUE | 1994 | 461302296.822234 MOZAMBIQUE | 1993 | 455014008.688903 MOZAMBIQUE | 1992 | 464806471.027731 PERU | 1998 | 277670653.474311 PERU | 1997 | 474201517.082612 PERU | 1996 | 467550873.82502 PERU | 1995 | 470477438.417577 PERU | 1994 | 467283651.767571 PERU | 1993 | 467042298.547621 PERU | 1992 | 468556513.49621 ROMANIA | 1998 | 272999972.776006 ROMANIA | 1997 | 457772432.43247 ROMANIA | 1996 | 470963588.451376 ROMANIA | 1995 | 459658913.835591 ROMANIA | 1994 | 462264747.454494 ROMANIA | 1993 | 453658476.107038 ROMANIA | 1992 | 463202913.870301 RUSSIA | 1998 | 282974654.657091 RUSSIA | 1997 | 472744075.347105 RUSSIA | 1996 | 477936058.028302 RUSSIA | 1995 | 473250072.081079 RUSSIA | 1994 | 469714591.198233 RUSSIA | 1993 | 469487591.670163 RUSSIA | 1992 | 480702143.020136 SAUDI ARABIA | 1998 | 276975718.059012 SAUDI ARABIA | 1997 | 469511861.224985 SAUDI ARABIA | 1996 | 475886970.832547 SAUDI ARABIA | 1995 | 481186892.143641 SAUDI ARABIA | 1994 | 472850648.153821 SAUDI ARABIA | 1993 | 471724280.615933 SAUDI ARABIA | 1992 | 468995225.233813 UNITED KINGDOM | 1998 | 277023951.727701 UNITED KINGDOM | 1997 | 463553121.23965 UNITED KINGDOM | 1996 | 461673262.356379 UNITED KINGDOM | 1995 | 465783191.493041 UNITED KINGDOM | 1994 | 460905898.592762 UNITED KINGDOM | 1993 | 463859856.041913 UNITED KINGDOM | 1992 | 461671358.64594 UNITED STATES | 1998 | 279455826.907894 UNITED STATES | 1997 | 471226798.565159 UNITED STATES | 1996 | 485399033.151613 UNITED STATES | 1995 | 481858726.264555 UNITED STATES | 1994 | 480896291.388144 UNITED STATES | 1993 | 471936842.280348 UNITED STATES | 1992 | 478046783.201663 VIETNAM | 1998 | 269285678.123514 VIETNAM | 1997 | 455048937.495889 VIETNAM | 1996 | 459978317.280193 VIETNAM | 1995 | 461209831.397266 VIETNAM | 1994 | 459442916.726311 VIETNAM | 1993 | 465509278.16882 VIETNAM | 1992 | 455591277.324912 (175 rows) COMMIT; COMMIT