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 '%purple%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 274079744.616718 ALGERIA | 1997 | 459942795.443963 ALGERIA | 1996 | 458026322.006948 ALGERIA | 1995 | 463051244.835439 ALGERIA | 1994 | 465994163.664442 ALGERIA | 1993 | 460833413.352521 ALGERIA | 1992 | 469378788.245114 ARGENTINA | 1998 | 273739289.418609 ARGENTINA | 1997 | 469539349.545279 ARGENTINA | 1996 | 473920480.033781 ARGENTINA | 1995 | 469582102.57794 ARGENTINA | 1994 | 463754424.467049 ARGENTINA | 1993 | 478219661.057208 ARGENTINA | 1992 | 474989551.084415 BRAZIL | 1998 | 273641474.892276 BRAZIL | 1997 | 470496283.176451 BRAZIL | 1996 | 466249833.512165 BRAZIL | 1995 | 469161163.766252 BRAZIL | 1994 | 472688305.895902 BRAZIL | 1993 | 466305792.721411 BRAZIL | 1992 | 468349290.479074 CANADA | 1998 | 280111618.348193 CANADA | 1997 | 475014157.402226 CANADA | 1996 | 481693162.819728 CANADA | 1995 | 473054887.798914 CANADA | 1994 | 476872556.269118 CANADA | 1993 | 472264466.652923 CANADA | 1992 | 476131888.596994 CHINA | 1998 | 274969341.406814 CHINA | 1997 | 461437418.895533 CHINA | 1996 | 474602122.160604 CHINA | 1995 | 471878449.55089 CHINA | 1994 | 462638766.804779 CHINA | 1993 | 471940395.633064 CHINA | 1992 | 471024061.750535 EGYPT | 1998 | 269511478.1587 EGYPT | 1997 | 458680051.857532 EGYPT | 1996 | 458956014.361804 EGYPT | 1995 | 464408679.432517 EGYPT | 1994 | 464428957.528125 EGYPT | 1993 | 461952238.101906 EGYPT | 1992 | 467565291.690605 ETHIOPIA | 1998 | 266387644.407915 ETHIOPIA | 1997 | 453315180.292707 ETHIOPIA | 1996 | 451777890.29214 ETHIOPIA | 1995 | 455033757.341967 ETHIOPIA | 1994 | 450029453.718369 ETHIOPIA | 1993 | 457976065.375488 ETHIOPIA | 1992 | 463842252.070742 FRANCE | 1998 | 265149036.800682 FRANCE | 1997 | 455959162.124644 FRANCE | 1996 | 460339032.457413 FRANCE | 1995 | 457741967.252269 FRANCE | 1994 | 453718074.124344 FRANCE | 1993 | 461627961.311329 FRANCE | 1992 | 457595114.882589 GERMANY | 1998 | 278092706.212884 GERMANY | 1997 | 484487590.625682 GERMANY | 1996 | 475366302.853419 GERMANY | 1995 | 477281980.905263 GERMANY | 1994 | 477118368.322353 GERMANY | 1993 | 470683172.523917 GERMANY | 1992 | 481074793.317914 INDIA | 1998 | 286665608.710015 INDIA | 1997 | 480980302.820029 INDIA | 1996 | 481647212.430815 INDIA | 1995 | 483191829.18012 INDIA | 1994 | 487535047.129063 INDIA | 1993 | 478899956.411177 INDIA | 1992 | 491205549.341994 INDONESIA | 1998 | 273205566.520242 INDONESIA | 1997 | 457081546.167836 INDONESIA | 1996 | 466965571.429664 INDONESIA | 1995 | 463674553.741591 INDONESIA | 1994 | 469554394.605651 INDONESIA | 1993 | 466693668.262197 INDONESIA | 1992 | 466075740.644572 IRAN | 1998 | 274696129.67028 IRAN | 1997 | 465407822.715813 IRAN | 1996 | 452546708.601798 IRAN | 1995 | 466501783.76241 IRAN | 1994 | 461582244.585563 IRAN | 1993 | 454899746.267753 IRAN | 1992 | 462332177.687464 IRAQ | 1998 | 280905417.80857 IRAQ | 1997 | 467960742.511773 IRAQ | 1996 | 476865832.038349 IRAQ | 1995 | 472529682.071371 IRAQ | 1994 | 466259045.99986 IRAQ | 1993 | 468731861.992225 IRAQ | 1992 | 478190437.17626 JAPAN | 1998 | 274211148.556099 JAPAN | 1997 | 462364290.584427 JAPAN | 1996 | 462838312.671951 JAPAN | 1995 | 465451418.942604 JAPAN | 1994 | 461330882.970483 JAPAN | 1993 | 465006024.072096 JAPAN | 1992 | 468045183.033436 JORDAN | 1998 | 273228421.827895 JORDAN | 1997 | 463260555.851051 JORDAN | 1996 | 467885163.925539 JORDAN | 1995 | 458783360.765934 JORDAN | 1994 | 465997389.2291 JORDAN | 1993 | 472002029.324179 JORDAN | 1992 | 467113434.973269 KENYA | 1998 | 279792189.270836 KENYA | 1997 | 474962461.286437 KENYA | 1996 | 473518279.525819 KENYA | 1995 | 476282163.824169 KENYA | 1994 | 471654278.765779 KENYA | 1993 | 477273591.855983 KENYA | 1992 | 477776813.061719 MOROCCO | 1998 | 278142375.679094 MOROCCO | 1997 | 477409760.169974 MOROCCO | 1996 | 480552067.62534 MOROCCO | 1995 | 483022768.363081 MOROCCO | 1994 | 482274443.291017 MOROCCO | 1993 | 474470459.910525 MOROCCO | 1992 | 479400184.933414 MOZAMBIQUE | 1998 | 262676875.445567 MOZAMBIQUE | 1997 | 454542418.422397 MOZAMBIQUE | 1996 | 460312513.513169 MOZAMBIQUE | 1995 | 458566908.641757 MOZAMBIQUE | 1994 | 452789263.111763 MOZAMBIQUE | 1993 | 458747259.200304 MOZAMBIQUE | 1992 | 453829613.026146 PERU | 1998 | 278236607.659663 PERU | 1997 | 456119619.571514 PERU | 1996 | 468822421.489752 PERU | 1995 | 458966116.470818 PERU | 1994 | 459999153.793761 PERU | 1993 | 462112295.218654 PERU | 1992 | 467356169.210776 ROMANIA | 1998 | 276573998.30532 ROMANIA | 1997 | 471316895.218762 ROMANIA | 1996 | 473967889.14128 ROMANIA | 1995 | 472935639.129862 ROMANIA | 1994 | 470329934.499414 ROMANIA | 1993 | 472024986.484148 ROMANIA | 1992 | 472059528.862129 RUSSIA | 1998 | 280039338.798033 RUSSIA | 1997 | 466371764.637777 RUSSIA | 1996 | 473308365.919009 RUSSIA | 1995 | 473234110.216395 RUSSIA | 1994 | 472912793.352393 RUSSIA | 1993 | 471491397.436261 RUSSIA | 1992 | 469658595.410654 SAUDI ARABIA | 1998 | 276329280.370966 SAUDI ARABIA | 1997 | 469172533.789213 SAUDI ARABIA | 1996 | 467112845.588919 SAUDI ARABIA | 1995 | 466093210.922938 SAUDI ARABIA | 1994 | 468520296.220487 SAUDI ARABIA | 1993 | 473370181.191728 SAUDI ARABIA | 1992 | 470289638.015565 UNITED KINGDOM | 1998 | 269335154.5962 UNITED KINGDOM | 1997 | 467441747.280351 UNITED KINGDOM | 1996 | 467467592.691297 UNITED KINGDOM | 1995 | 466066360.735497 UNITED KINGDOM | 1994 | 459321704.084997 UNITED KINGDOM | 1993 | 468181932.794555 UNITED KINGDOM | 1992 | 464209152.540577 UNITED STATES | 1998 | 272767260.047612 UNITED STATES | 1997 | 471543361.129888 UNITED STATES | 1996 | 473827185.975542 UNITED STATES | 1995 | 463930864.131765 UNITED STATES | 1994 | 476440010.218746 UNITED STATES | 1993 | 468126935.793559 UNITED STATES | 1992 | 470409890.558587 VIETNAM | 1998 | 279673340.159386 VIETNAM | 1997 | 466433062.946568 VIETNAM | 1996 | 471736281.904732 VIETNAM | 1995 | 470396570.772144 VIETNAM | 1994 | 473513019.790105 VIETNAM | 1993 | 476690673.494237 VIETNAM | 1992 | 474364977.508708 (175 rows) COMMIT; COMMIT