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 '%red%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271463170.933629 ALGERIA | 1997 | 454070549.584368 ALGERIA | 1996 | 457943634.698534 ALGERIA | 1995 | 454500273.093923 ALGERIA | 1994 | 457044094.316027 ALGERIA | 1993 | 460064385.344445 ALGERIA | 1992 | 455767254.085108 ARGENTINA | 1998 | 277035707.777824 ARGENTINA | 1997 | 465821411.160257 ARGENTINA | 1996 | 476083028.510643 ARGENTINA | 1995 | 468415946.662124 ARGENTINA | 1994 | 468552140.795691 ARGENTINA | 1993 | 464708217.250099 ARGENTINA | 1992 | 468723702.222426 BRAZIL | 1998 | 272653236.623141 BRAZIL | 1997 | 465561481.605871 BRAZIL | 1996 | 470587164.08887 BRAZIL | 1995 | 472023648.12147 BRAZIL | 1994 | 462986130.642751 BRAZIL | 1993 | 468203276.894635 BRAZIL | 1992 | 466861486.705768 CANADA | 1998 | 281877144.770099 CANADA | 1997 | 476679918.906523 CANADA | 1996 | 483236453.300964 CANADA | 1995 | 485705433.660651 CANADA | 1994 | 487960858.91927 CANADA | 1993 | 474160237.594783 CANADA | 1992 | 478980141.573938 CHINA | 1998 | 281339204.20189 CHINA | 1997 | 476078231.550344 CHINA | 1996 | 467831147.504321 CHINA | 1995 | 465836671.607678 CHINA | 1994 | 469110737.806565 CHINA | 1993 | 471273498.217914 CHINA | 1992 | 479126797.351253 EGYPT | 1998 | 276191337.89676 EGYPT | 1997 | 467561923.04751 EGYPT | 1996 | 469274081.41845 EGYPT | 1995 | 468562611.468261 EGYPT | 1994 | 472190480.273497 EGYPT | 1993 | 466519537.121503 EGYPT | 1992 | 472900106.698234 ETHIOPIA | 1998 | 270195233.613571 ETHIOPIA | 1997 | 461644182.196421 ETHIOPIA | 1996 | 464534463.041496 ETHIOPIA | 1995 | 470280680.319939 ETHIOPIA | 1994 | 462133632.476973 ETHIOPIA | 1993 | 464236713.222884 ETHIOPIA | 1992 | 455610455.800802 FRANCE | 1998 | 279287195.488483 FRANCE | 1997 | 475714404.945181 FRANCE | 1996 | 479247043.970026 FRANCE | 1995 | 473776736.279528 FRANCE | 1994 | 475218123.478396 FRANCE | 1993 | 466819497.731911 FRANCE | 1992 | 471135074.893434 GERMANY | 1998 | 280163284.414669 GERMANY | 1997 | 475614650.090181 GERMANY | 1996 | 481172386.444612 GERMANY | 1995 | 479622720.134532 GERMANY | 1994 | 481890579.265795 GERMANY | 1993 | 478817535.580191 GERMANY | 1992 | 478231521.001504 INDIA | 1998 | 279859497.909209 INDIA | 1997 | 474100584.696865 INDIA | 1996 | 473342297.684132 INDIA | 1995 | 476155193.519212 INDIA | 1994 | 476047497.398063 INDIA | 1993 | 468175402.488777 INDIA | 1992 | 481676317.949983 INDONESIA | 1998 | 273125416.87944 INDONESIA | 1997 | 457744141.757953 INDONESIA | 1996 | 464103731.430255 INDONESIA | 1995 | 458255525.097115 INDONESIA | 1994 | 462525898.339242 INDONESIA | 1993 | 459250492.00759 INDONESIA | 1992 | 450443174.102719 IRAN | 1998 | 270697803.839296 IRAN | 1997 | 464292651.370414 IRAN | 1996 | 463581693.195054 IRAN | 1995 | 462962006.496044 IRAN | 1994 | 466304298.26672 IRAN | 1993 | 461124988.233938 IRAN | 1992 | 464325756.57861 IRAQ | 1998 | 285840447.069166 IRAQ | 1997 | 471161150.813667 IRAQ | 1996 | 487305413.2001 IRAQ | 1995 | 475600482.874581 IRAQ | 1994 | 474184544.672695 IRAQ | 1993 | 480628700.678062 IRAQ | 1992 | 475274119.333962 JAPAN | 1998 | 276674014.220086 JAPAN | 1997 | 465996709.658289 JAPAN | 1996 | 460142012.8565 JAPAN | 1995 | 469404430.69849 JAPAN | 1994 | 462945144.089538 JAPAN | 1993 | 469665645.237126 JAPAN | 1992 | 464809533.922165 JORDAN | 1998 | 271387921.244365 JORDAN | 1997 | 457360196.926716 JORDAN | 1996 | 458314408.235906 JORDAN | 1995 | 455052003.021448 JORDAN | 1994 | 459932780.807373 JORDAN | 1993 | 456511077.585381 JORDAN | 1992 | 458855476.1866 KENYA | 1998 | 271137893.036851 KENYA | 1997 | 468225505.663525 KENYA | 1996 | 476081713.601668 KENYA | 1995 | 474747199.217524 KENYA | 1994 | 478834420.803364 KENYA | 1993 | 471063764.379349 KENYA | 1992 | 471769153.669048 MOROCCO | 1998 | 278187324.123214 MOROCCO | 1997 | 465448890.880728 MOROCCO | 1996 | 469890631.578571 MOROCCO | 1995 | 469352481.328447 MOROCCO | 1994 | 472792421.037071 MOROCCO | 1993 | 466319081.609875 MOROCCO | 1992 | 474610360.720715 MOZAMBIQUE | 1998 | 270341770.953105 MOZAMBIQUE | 1997 | 460181429.553227 MOZAMBIQUE | 1996 | 453412883.154194 MOZAMBIQUE | 1995 | 457357057.893526 MOZAMBIQUE | 1994 | 459743615.406569 MOZAMBIQUE | 1993 | 450970954.038236 MOZAMBIQUE | 1992 | 454413090.97965 PERU | 1998 | 279742446.041858 PERU | 1997 | 468071158.268895 PERU | 1996 | 469154943.837011 PERU | 1995 | 465918385.257043 PERU | 1994 | 471198915.044853 PERU | 1993 | 459002722.883194 PERU | 1992 | 466566465.708159 ROMANIA | 1998 | 270076018.740406 ROMANIA | 1997 | 461941906.40747 ROMANIA | 1996 | 466640448.662447 ROMANIA | 1995 | 461077873.737014 ROMANIA | 1994 | 453602963.925479 ROMANIA | 1993 | 461230197.106631 ROMANIA | 1992 | 464193005.505606 RUSSIA | 1998 | 276164297.691741 RUSSIA | 1997 | 472747584.225776 RUSSIA | 1996 | 470294263.781514 RUSSIA | 1995 | 478784963.120149 RUSSIA | 1994 | 462597197.803278 RUSSIA | 1993 | 474507262.261425 RUSSIA | 1992 | 476393391.407099 SAUDI ARABIA | 1998 | 270717515.204672 SAUDI ARABIA | 1997 | 463358752.968111 SAUDI ARABIA | 1996 | 465906246.140394 SAUDI ARABIA | 1995 | 464275750.676025 SAUDI ARABIA | 1994 | 470287779.158183 SAUDI ARABIA | 1993 | 461707040.646639 SAUDI ARABIA | 1992 | 470354072.929126 UNITED KINGDOM | 1998 | 273055572.357158 UNITED KINGDOM | 1997 | 465951471.80833 UNITED KINGDOM | 1996 | 469520992.930119 UNITED KINGDOM | 1995 | 465095063.386935 UNITED KINGDOM | 1994 | 465149136.16374 UNITED KINGDOM | 1993 | 473935635.157975 UNITED KINGDOM | 1992 | 466062923.263947 UNITED STATES | 1998 | 274904492.321379 UNITED STATES | 1997 | 473179276.702492 UNITED STATES | 1996 | 467615557.503738 UNITED STATES | 1995 | 461478339.988617 UNITED STATES | 1994 | 468620461.899062 UNITED STATES | 1993 | 479125797.646877 UNITED STATES | 1992 | 472237683.81948 VIETNAM | 1998 | 276705330.165728 VIETNAM | 1997 | 473247491.006168 VIETNAM | 1996 | 476753126.057122 VIETNAM | 1995 | 469031657.091615 VIETNAM | 1994 | 468641007.681502 VIETNAM | 1993 | 465363090.175892 VIETNAM | 1992 | 476755694.136543 (175 rows) COMMIT; COMMIT