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 '%peach%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261721417.023542 ALGERIA | 1997 | 453042287.857282 ALGERIA | 1996 | 457647487.151909 ALGERIA | 1995 | 452588451.924766 ALGERIA | 1994 | 451437153.545157 ALGERIA | 1993 | 458184266.601227 ALGERIA | 1992 | 456640487.046817 ARGENTINA | 1998 | 281393293.608867 ARGENTINA | 1997 | 471680824.763175 ARGENTINA | 1996 | 475071089.266828 ARGENTINA | 1995 | 471848858.422235 ARGENTINA | 1994 | 467411894.321519 ARGENTINA | 1993 | 469537766.642588 ARGENTINA | 1992 | 475846530.427965 BRAZIL | 1998 | 270381508.370645 BRAZIL | 1997 | 470236392.987515 BRAZIL | 1996 | 468886518.704974 BRAZIL | 1995 | 462029516.371823 BRAZIL | 1994 | 467850207.561325 BRAZIL | 1993 | 466671610.283897 BRAZIL | 1992 | 471505065.755561 CANADA | 1998 | 267725705.678809 CANADA | 1997 | 467863572.903235 CANADA | 1996 | 474658045.743963 CANADA | 1995 | 467348189.811372 CANADA | 1994 | 478360828.464418 CANADA | 1993 | 466033111.805187 CANADA | 1992 | 468671959.014044 CHINA | 1998 | 281852675.448701 CHINA | 1997 | 473317229.215388 CHINA | 1996 | 474805158.95053 CHINA | 1995 | 470243487.827981 CHINA | 1994 | 465412571.568879 CHINA | 1993 | 482541350.788651 CHINA | 1992 | 474900595.907614 EGYPT | 1998 | 281513804.753299 EGYPT | 1997 | 473710938.99177 EGYPT | 1996 | 476122725.780178 EGYPT | 1995 | 467113858.085241 EGYPT | 1994 | 479341364.498545 EGYPT | 1993 | 470166266.901304 EGYPT | 1992 | 474715153.764914 ETHIOPIA | 1998 | 264741215.971932 ETHIOPIA | 1997 | 448611094.947046 ETHIOPIA | 1996 | 440777287.008214 ETHIOPIA | 1995 | 446866697.306812 ETHIOPIA | 1994 | 455361133.767146 ETHIOPIA | 1993 | 449376391.068803 ETHIOPIA | 1992 | 449936121.28726 FRANCE | 1998 | 272250412.874288 FRANCE | 1997 | 450688678.472163 FRANCE | 1996 | 461383048.269979 FRANCE | 1995 | 465211171.734585 FRANCE | 1994 | 458347058.601864 FRANCE | 1993 | 462799257.730469 FRANCE | 1992 | 463091984.30562 GERMANY | 1998 | 273695926.706778 GERMANY | 1997 | 477453691.372969 GERMANY | 1996 | 472756469.558056 GERMANY | 1995 | 479858061.08815 GERMANY | 1994 | 480129366.788825 GERMANY | 1993 | 478206032.237297 GERMANY | 1992 | 480251809.935179 INDIA | 1998 | 276158700.062688 INDIA | 1997 | 469455420.221222 INDIA | 1996 | 477085784.460116 INDIA | 1995 | 488131824.228276 INDIA | 1994 | 477119032.35483 INDIA | 1993 | 475863857.222774 INDIA | 1992 | 480711844.913295 INDONESIA | 1998 | 276054350.657162 INDONESIA | 1997 | 457546516.849727 INDONESIA | 1996 | 469516147.728981 INDONESIA | 1995 | 474687527.577875 INDONESIA | 1994 | 467866932.581213 INDONESIA | 1993 | 463757577.855828 INDONESIA | 1992 | 469092045.518231 IRAN | 1998 | 271372918.353154 IRAN | 1997 | 461998446.570973 IRAN | 1996 | 468199473.674417 IRAN | 1995 | 476157427.081256 IRAN | 1994 | 470392262.097455 IRAN | 1993 | 470689501.98905 IRAN | 1992 | 479707946.061224 IRAQ | 1998 | 276124041.439114 IRAQ | 1997 | 468893975.218807 IRAQ | 1996 | 479335431.719965 IRAQ | 1995 | 470650775.491865 IRAQ | 1994 | 470387359.678831 IRAQ | 1993 | 469540611.161454 IRAQ | 1992 | 478428569.249647 JAPAN | 1998 | 265193510.5867 JAPAN | 1997 | 451545539.001804 JAPAN | 1996 | 465394625.96287 JAPAN | 1995 | 452646385.191255 JAPAN | 1994 | 459299566.180721 JAPAN | 1993 | 463163247.813342 JAPAN | 1992 | 449786354.370134 JORDAN | 1998 | 262270765.329284 JORDAN | 1997 | 464107609.334821 JORDAN | 1996 | 461271543.939331 JORDAN | 1995 | 452683835.513708 JORDAN | 1994 | 463347880.278038 JORDAN | 1993 | 451968999.411742 JORDAN | 1992 | 457411177.420457 KENYA | 1998 | 275040099.81942 KENYA | 1997 | 464775457.005657 KENYA | 1996 | 467292012.590582 KENYA | 1995 | 465112798.403036 KENYA | 1994 | 470408175.4158 KENYA | 1993 | 470682452.05115 KENYA | 1992 | 463954533.927801 MOROCCO | 1998 | 271753668.200458 MOROCCO | 1997 | 469461165.876702 MOROCCO | 1996 | 471638487.622579 MOROCCO | 1995 | 468366514.692054 MOROCCO | 1994 | 466174152.503644 MOROCCO | 1993 | 465446508.247726 MOROCCO | 1992 | 468713395.089406 MOZAMBIQUE | 1998 | 265219474.718333 MOZAMBIQUE | 1997 | 463354342.335812 MOZAMBIQUE | 1996 | 462027923.436526 MOZAMBIQUE | 1995 | 457785897.884477 MOZAMBIQUE | 1994 | 461932572.373266 MOZAMBIQUE | 1993 | 464895227.22737 MOZAMBIQUE | 1992 | 453434003.775484 PERU | 1998 | 279008912.162719 PERU | 1997 | 465294615.159631 PERU | 1996 | 458570674.303572 PERU | 1995 | 458628792.491884 PERU | 1994 | 461607217.31253 PERU | 1993 | 461155066.892971 PERU | 1992 | 462919730.556924 ROMANIA | 1998 | 274216545.651618 ROMANIA | 1997 | 467346200.468973 ROMANIA | 1996 | 466362621.693621 ROMANIA | 1995 | 460965392.524157 ROMANIA | 1994 | 473386141.171093 ROMANIA | 1993 | 471065427.319486 ROMANIA | 1992 | 468596735.582146 RUSSIA | 1998 | 269272271.464245 RUSSIA | 1997 | 461140975.908455 RUSSIA | 1996 | 464117783.437422 RUSSIA | 1995 | 455647729.662825 RUSSIA | 1994 | 465848584.953312 RUSSIA | 1993 | 466112360.407158 RUSSIA | 1992 | 461909298.827769 SAUDI ARABIA | 1998 | 275919992.67309 SAUDI ARABIA | 1997 | 466377495.095694 SAUDI ARABIA | 1996 | 468901483.511008 SAUDI ARABIA | 1995 | 463998018.218166 SAUDI ARABIA | 1994 | 464695649.161252 SAUDI ARABIA | 1993 | 464340617.550379 SAUDI ARABIA | 1992 | 472366878.382714 UNITED KINGDOM | 1998 | 269673457.107723 UNITED KINGDOM | 1997 | 457694404.850906 UNITED KINGDOM | 1996 | 459717338.713089 UNITED KINGDOM | 1995 | 448467635.740235 UNITED KINGDOM | 1994 | 451696403.732142 UNITED KINGDOM | 1993 | 466235068.855418 UNITED KINGDOM | 1992 | 462812142.369127 UNITED STATES | 1998 | 270697358.645624 UNITED STATES | 1997 | 459493554.059985 UNITED STATES | 1996 | 459531409.805702 UNITED STATES | 1995 | 463099646.623058 UNITED STATES | 1994 | 470430521.225362 UNITED STATES | 1993 | 470175872.256399 UNITED STATES | 1992 | 465488057.568089 VIETNAM | 1998 | 277199374.046748 VIETNAM | 1997 | 474484316.877356 VIETNAM | 1996 | 474655698.62566 VIETNAM | 1995 | 461309899.556716 VIETNAM | 1994 | 471549907.008162 VIETNAM | 1993 | 469375281.707486 VIETNAM | 1992 | 479394795.899837 (175 rows) COMMIT; COMMIT