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 | 267733381.260859 ALGERIA | 1997 | 441017732.019187 ALGERIA | 1996 | 451255542.141551 ALGERIA | 1995 | 456133211.364123 ALGERIA | 1994 | 454241803.484154 ALGERIA | 1993 | 451744686.482082 ALGERIA | 1992 | 449637934.651445 ARGENTINA | 1998 | 271974839.328937 ARGENTINA | 1997 | 460052056.844439 ARGENTINA | 1996 | 464674621.209649 ARGENTINA | 1995 | 468381359.247757 ARGENTINA | 1994 | 475420456.829169 ARGENTINA | 1993 | 463962761.406354 ARGENTINA | 1992 | 465295897.863082 BRAZIL | 1998 | 275131702.37698 BRAZIL | 1997 | 465215428.787859 BRAZIL | 1996 | 471315265.231635 BRAZIL | 1995 | 472493606.545978 BRAZIL | 1994 | 471368484.886945 BRAZIL | 1993 | 468451733.241677 BRAZIL | 1992 | 479882749.182579 CANADA | 1998 | 278811320.336978 CANADA | 1997 | 466640620.805407 CANADA | 1996 | 479749853.226188 CANADA | 1995 | 467698413.610905 CANADA | 1994 | 469533546.57857 CANADA | 1993 | 467055911.408808 CANADA | 1992 | 472605996.275315 CHINA | 1998 | 272978185.222833 CHINA | 1997 | 462280370.609172 CHINA | 1996 | 465636445.138606 CHINA | 1995 | 462454603.39369 CHINA | 1994 | 454721763.720329 CHINA | 1993 | 461926169.825114 CHINA | 1992 | 466188452.320478 EGYPT | 1998 | 274056790.24298 EGYPT | 1997 | 459390437.101179 EGYPT | 1996 | 460523610.222818 EGYPT | 1995 | 461281007.4926 EGYPT | 1994 | 469557070.662689 EGYPT | 1993 | 466523850.372569 EGYPT | 1992 | 462888782.345645 ETHIOPIA | 1998 | 271512467.644073 ETHIOPIA | 1997 | 456694947.766877 ETHIOPIA | 1996 | 461754100.354389 ETHIOPIA | 1995 | 463825661.06525 ETHIOPIA | 1994 | 456461488.717343 ETHIOPIA | 1993 | 461467177.723654 ETHIOPIA | 1992 | 455775468.820231 FRANCE | 1998 | 271547551.13118 FRANCE | 1997 | 466380172.310324 FRANCE | 1996 | 473843649.419054 FRANCE | 1995 | 458553552.456239 FRANCE | 1994 | 469391709.91163 FRANCE | 1993 | 464693007.964251 FRANCE | 1992 | 465946874.845463 GERMANY | 1998 | 274983337.583991 GERMANY | 1997 | 478629974.139083 GERMANY | 1996 | 482326601.899817 GERMANY | 1995 | 478265109.434828 GERMANY | 1994 | 472764071.379867 GERMANY | 1993 | 476485024.761365 GERMANY | 1992 | 480800630.765085 INDIA | 1998 | 276223136.133542 INDIA | 1997 | 474840760.813425 INDIA | 1996 | 470149386.928912 INDIA | 1995 | 462743177.029664 INDIA | 1994 | 472308150.632884 INDIA | 1993 | 472687709.587951 INDIA | 1992 | 468851603.405028 INDONESIA | 1998 | 269998843.759336 INDONESIA | 1997 | 462075341.221721 INDONESIA | 1996 | 461789612.549896 INDONESIA | 1995 | 460590777.046218 INDONESIA | 1994 | 468090305.207247 INDONESIA | 1993 | 464041702.55994 INDONESIA | 1992 | 464653969.681273 IRAN | 1998 | 274381741.686631 IRAN | 1997 | 468729871.81021 IRAN | 1996 | 466397508.161216 IRAN | 1995 | 468146671.008464 IRAN | 1994 | 470106297.787043 IRAN | 1993 | 463835636.827238 IRAN | 1992 | 475692853.543695 IRAQ | 1998 | 278025187.125245 IRAQ | 1997 | 470228349.263236 IRAQ | 1996 | 473747051.913641 IRAQ | 1995 | 464511112.149835 IRAQ | 1994 | 482587254.412003 IRAQ | 1993 | 474955519.727186 IRAQ | 1992 | 469633930.347318 JAPAN | 1998 | 265081771.974494 JAPAN | 1997 | 466964332.586815 JAPAN | 1996 | 459926661.817138 JAPAN | 1995 | 454688322.249754 JAPAN | 1994 | 459503259.20841 JAPAN | 1993 | 462991363.722 JAPAN | 1992 | 465232038.742861 JORDAN | 1998 | 272925338.688299 JORDAN | 1997 | 460635210.246806 JORDAN | 1996 | 467267951.952549 JORDAN | 1995 | 457942015.00506 JORDAN | 1994 | 461071214.104174 JORDAN | 1993 | 461535595.036837 JORDAN | 1992 | 457621962.669482 KENYA | 1998 | 273362661.691188 KENYA | 1997 | 470030365.663064 KENYA | 1996 | 475879624.73228 KENYA | 1995 | 471748467.316493 KENYA | 1994 | 470339377.647063 KENYA | 1993 | 476463977.052571 KENYA | 1992 | 465798837.563683 MOROCCO | 1998 | 267474862.461397 MOROCCO | 1997 | 475925353.78113 MOROCCO | 1996 | 465680079.319443 MOROCCO | 1995 | 470864428.17019 MOROCCO | 1994 | 474920330.64202 MOROCCO | 1993 | 467261602.517404 MOROCCO | 1992 | 470398001.421509 MOZAMBIQUE | 1998 | 272114253.149438 MOZAMBIQUE | 1997 | 460052137.555355 MOZAMBIQUE | 1996 | 466409064.646539 MOZAMBIQUE | 1995 | 462082302.573486 MOZAMBIQUE | 1994 | 464640748.106054 MOZAMBIQUE | 1993 | 467124568.456605 MOZAMBIQUE | 1992 | 457319657.09963 PERU | 1998 | 274069060.021557 PERU | 1997 | 458715283.472812 PERU | 1996 | 464645460.58961 PERU | 1995 | 462798699.159498 PERU | 1994 | 464162014.251173 PERU | 1993 | 459527840.053785 PERU | 1992 | 460513370.706854 ROMANIA | 1998 | 265680910.235068 ROMANIA | 1997 | 472053262.919756 ROMANIA | 1996 | 458555246.687917 ROMANIA | 1995 | 463075917.311454 ROMANIA | 1994 | 464025859.211452 ROMANIA | 1993 | 468858107.232784 ROMANIA | 1992 | 463993164.075636 RUSSIA | 1998 | 277558310.117047 RUSSIA | 1997 | 473781651.318415 RUSSIA | 1996 | 480524833.645467 RUSSIA | 1995 | 470502137.249768 RUSSIA | 1994 | 479052668.838081 RUSSIA | 1993 | 473858057.009308 RUSSIA | 1992 | 470388851.171657 SAUDI ARABIA | 1998 | 277711373.874991 SAUDI ARABIA | 1997 | 465530427.117787 SAUDI ARABIA | 1996 | 471962363.079423 SAUDI ARABIA | 1995 | 474129902.058021 SAUDI ARABIA | 1994 | 467814265.554824 SAUDI ARABIA | 1993 | 467663295.30485 SAUDI ARABIA | 1992 | 462622679.845866 UNITED KINGDOM | 1998 | 276392758.713516 UNITED KINGDOM | 1997 | 464377688.700315 UNITED KINGDOM | 1996 | 460045995.583935 UNITED KINGDOM | 1995 | 466282504.455631 UNITED KINGDOM | 1994 | 462679991.398715 UNITED KINGDOM | 1993 | 468068572.838788 UNITED KINGDOM | 1992 | 458922416.144971 UNITED STATES | 1998 | 276781168.463387 UNITED STATES | 1997 | 471934304.69106 UNITED STATES | 1996 | 471556678.314886 UNITED STATES | 1995 | 462545117.395974 UNITED STATES | 1994 | 473329648.547494 UNITED STATES | 1993 | 467589150.105562 UNITED STATES | 1992 | 465434709.097091 VIETNAM | 1998 | 265987701.63849 VIETNAM | 1997 | 459894034.041729 VIETNAM | 1996 | 467702237.018287 VIETNAM | 1995 | 456674455.194091 VIETNAM | 1994 | 465001401.202313 VIETNAM | 1993 | 464673532.391593 VIETNAM | 1992 | 462488417.688959 (175 rows) COMMIT; COMMIT