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 '%light%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267569753.978501 ALGERIA | 1997 | 441947850.425817 ALGERIA | 1996 | 451522923.557458 ALGERIA | 1995 | 455820065.383182 ALGERIA | 1994 | 454300236.913152 ALGERIA | 1993 | 452172131.159338 ALGERIA | 1992 | 449463955.095257 ARGENTINA | 1998 | 271734914.997629 ARGENTINA | 1997 | 459220662.658703 ARGENTINA | 1996 | 464261908.365933 ARGENTINA | 1995 | 467568296.705787 ARGENTINA | 1994 | 475456010.769848 ARGENTINA | 1993 | 464124117.988579 ARGENTINA | 1992 | 465035170.153986 BRAZIL | 1998 | 274686165.538009 BRAZIL | 1997 | 465397446.565938 BRAZIL | 1996 | 470713404.995779 BRAZIL | 1995 | 473342234.254698 BRAZIL | 1994 | 470660738.677929 BRAZIL | 1993 | 468564734.655422 BRAZIL | 1992 | 479608858.713097 CANADA | 1998 | 279034011.495223 CANADA | 1997 | 467604558.13042 CANADA | 1996 | 479826552.045476 CANADA | 1995 | 468202651.227828 CANADA | 1994 | 470539930.909715 CANADA | 1993 | 467598420.850445 CANADA | 1992 | 472876856.226466 CHINA | 1998 | 272374173.689677 CHINA | 1997 | 461689662.388913 CHINA | 1996 | 466489497.141979 CHINA | 1995 | 462086525.611646 CHINA | 1994 | 454701338.368687 CHINA | 1993 | 461667602.648188 CHINA | 1992 | 466328650.771665 EGYPT | 1998 | 274082557.371502 EGYPT | 1997 | 459297331.950805 EGYPT | 1996 | 460121395.347817 EGYPT | 1995 | 461108962.792054 EGYPT | 1994 | 470481885.334434 EGYPT | 1993 | 466201346.321855 EGYPT | 1992 | 462912650.275672 ETHIOPIA | 1998 | 271481975.480063 ETHIOPIA | 1997 | 456096609.92663 ETHIOPIA | 1996 | 462076781.955341 ETHIOPIA | 1995 | 463760357.857717 ETHIOPIA | 1994 | 456173750.359015 ETHIOPIA | 1993 | 461496720.995292 ETHIOPIA | 1992 | 455882312.458776 FRANCE | 1998 | 271838275.909036 FRANCE | 1997 | 465711181.177385 FRANCE | 1996 | 473955495.523816 FRANCE | 1995 | 458700771.42296 FRANCE | 1994 | 469014043.581404 FRANCE | 1993 | 464925911.885021 FRANCE | 1992 | 465976087.380405 GERMANY | 1998 | 275145803.689286 GERMANY | 1997 | 478165510.52801 GERMANY | 1996 | 483534173.683708 GERMANY | 1995 | 478161458.274627 GERMANY | 1994 | 472771367.155455 GERMANY | 1993 | 477861592.801091 GERMANY | 1992 | 480826031.331071 INDIA | 1998 | 275876968.320503 INDIA | 1997 | 473985016.297317 INDIA | 1996 | 470361897.154344 INDIA | 1995 | 463077867.583998 INDIA | 1994 | 472113876.562772 INDIA | 1993 | 472325611.73831 INDIA | 1992 | 469010791.451564 INDONESIA | 1998 | 270066896.320498 INDONESIA | 1997 | 461222995.967082 INDONESIA | 1996 | 461891527.079706 INDONESIA | 1995 | 460693029.450958 INDONESIA | 1994 | 467758434.842692 INDONESIA | 1993 | 464112729.503067 INDONESIA | 1992 | 465363865.530102 IRAN | 1998 | 274304637.149113 IRAN | 1997 | 467834409.121575 IRAN | 1996 | 466906368.667067 IRAN | 1995 | 468204027.001309 IRAN | 1994 | 470708941.830863 IRAN | 1993 | 463526260.36274 IRAN | 1992 | 475726423.495375 IRAQ | 1998 | 278379403.483513 IRAQ | 1997 | 469773138.626965 IRAQ | 1996 | 474528579.968479 IRAQ | 1995 | 465086904.521634 IRAQ | 1994 | 482288099.287117 IRAQ | 1993 | 474210843.793282 IRAQ | 1992 | 469825538.169717 JAPAN | 1998 | 264342300.63358 JAPAN | 1997 | 466606998.23875 JAPAN | 1996 | 459434704.688285 JAPAN | 1995 | 454755763.666313 JAPAN | 1994 | 459001975.882104 JAPAN | 1993 | 462390877.289262 JAPAN | 1992 | 464176111.775515 JORDAN | 1998 | 273253315.601247 JORDAN | 1997 | 460735950.37553 JORDAN | 1996 | 467630680.202476 JORDAN | 1995 | 458458725.383066 JORDAN | 1994 | 460456329.889487 JORDAN | 1993 | 461122573.288552 JORDAN | 1992 | 457959410.374668 KENYA | 1998 | 273368279.385876 KENYA | 1997 | 470450382.554081 KENYA | 1996 | 476042476.78998 KENYA | 1995 | 472214117.974638 KENYA | 1994 | 470120452.167244 KENYA | 1993 | 476595546.790183 KENYA | 1992 | 464407712.351118 MOROCCO | 1998 | 267974555.853446 MOROCCO | 1997 | 476100132.743477 MOROCCO | 1996 | 464732002.214198 MOROCCO | 1995 | 470685241.431131 MOROCCO | 1994 | 474343890.901986 MOROCCO | 1993 | 466996183.368044 MOROCCO | 1992 | 470355763.057261 MOZAMBIQUE | 1998 | 273295770.96132 MOZAMBIQUE | 1997 | 459299224.896979 MOZAMBIQUE | 1996 | 466312726.609036 MOZAMBIQUE | 1995 | 461887352.592258 MOZAMBIQUE | 1994 | 464478116.603968 MOZAMBIQUE | 1993 | 467160932.199185 MOZAMBIQUE | 1992 | 457891337.990407 PERU | 1998 | 274167561.026471 PERU | 1997 | 459354712.651477 PERU | 1996 | 464556670.334408 PERU | 1995 | 462501916.045243 PERU | 1994 | 464630551.61778 PERU | 1993 | 459869887.179386 PERU | 1992 | 460045262.000078 ROMANIA | 1998 | 265908077.791002 ROMANIA | 1997 | 471527956.378087 ROMANIA | 1996 | 458787001.53031 ROMANIA | 1995 | 463341555.664826 ROMANIA | 1994 | 463435962.927962 ROMANIA | 1993 | 468012602.105567 ROMANIA | 1992 | 463448908.846633 RUSSIA | 1998 | 277786167.983812 RUSSIA | 1997 | 474044077.685647 RUSSIA | 1996 | 482391531.658161 RUSSIA | 1995 | 470355306.91191 RUSSIA | 1994 | 477841183.138542 RUSSIA | 1993 | 474301363.764721 RUSSIA | 1992 | 470654522.108885 SAUDI ARABIA | 1998 | 278448576.088431 SAUDI ARABIA | 1997 | 465437115.816835 SAUDI ARABIA | 1996 | 472802417.076085 SAUDI ARABIA | 1995 | 474945744.440712 SAUDI ARABIA | 1994 | 468765203.500581 SAUDI ARABIA | 1993 | 468304836.197764 SAUDI ARABIA | 1992 | 462608976.847498 UNITED KINGDOM | 1998 | 275854578.483779 UNITED KINGDOM | 1997 | 463755234.472939 UNITED KINGDOM | 1996 | 459864052.379671 UNITED KINGDOM | 1995 | 465880039.144602 UNITED KINGDOM | 1994 | 462948059.229111 UNITED KINGDOM | 1993 | 468453997.204774 UNITED KINGDOM | 1992 | 458958265.986512 UNITED STATES | 1998 | 276463309.929744 UNITED STATES | 1997 | 472004963.086017 UNITED STATES | 1996 | 471299514.193713 UNITED STATES | 1995 | 462786205.044017 UNITED STATES | 1994 | 473118846.174337 UNITED STATES | 1993 | 467269170.222623 UNITED STATES | 1992 | 465690736.508956 VIETNAM | 1998 | 265381321.231687 VIETNAM | 1997 | 459240246.82853 VIETNAM | 1996 | 468352195.32227 VIETNAM | 1995 | 456783671.785578 VIETNAM | 1994 | 465553732.20773 VIETNAM | 1993 | 465544713.738287 VIETNAM | 1992 | 462110582.247995 (175 rows) COMMIT; COMMIT