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 | 261601164.332698 ALGERIA | 1997 | 452797317.758471 ALGERIA | 1996 | 457404479.876932 ALGERIA | 1995 | 452058690.507027 ALGERIA | 1994 | 450987324.627829 ALGERIA | 1993 | 457771301.882661 ALGERIA | 1992 | 456426199.140511 ARGENTINA | 1998 | 281088672.495598 ARGENTINA | 1997 | 471119739.126296 ARGENTINA | 1996 | 474581433.871175 ARGENTINA | 1995 | 471404870.818309 ARGENTINA | 1994 | 467023889.246761 ARGENTINA | 1993 | 469115967.953974 ARGENTINA | 1992 | 475045283.116404 BRAZIL | 1998 | 270029359.0162 BRAZIL | 1997 | 469879605.494027 BRAZIL | 1996 | 468396607.533645 BRAZIL | 1995 | 461723332.502253 BRAZIL | 1994 | 467111649.461147 BRAZIL | 1993 | 466175138.532445 BRAZIL | 1992 | 470916101.26247 CANADA | 1998 | 267328665.332714 CANADA | 1997 | 467539730.521846 CANADA | 1996 | 474202401.316225 CANADA | 1995 | 466810746.525593 CANADA | 1994 | 477875599.851642 CANADA | 1993 | 465556441.323252 CANADA | 1992 | 468198269.80722 CHINA | 1998 | 281655125.450984 CHINA | 1997 | 472876205.132424 CHINA | 1996 | 474400793.784709 CHINA | 1995 | 469875330.821763 CHINA | 1994 | 464930479.516648 CHINA | 1993 | 482105022.394222 CHINA | 1992 | 474492404.022422 EGYPT | 1998 | 281328383.452968 EGYPT | 1997 | 473442630.961734 EGYPT | 1996 | 475463668.084603 EGYPT | 1995 | 466554182.75301 EGYPT | 1994 | 478788188.732335 EGYPT | 1993 | 469701696.96277 EGYPT | 1992 | 474094448.747962 ETHIOPIA | 1998 | 264380900.122786 ETHIOPIA | 1997 | 447745086.07286 ETHIOPIA | 1996 | 440433431.943703 ETHIOPIA | 1995 | 446464719.001188 ETHIOPIA | 1994 | 454808949.475884 ETHIOPIA | 1993 | 448906078.216692 ETHIOPIA | 1992 | 449688887.510725 FRANCE | 1998 | 272059413.199518 FRANCE | 1997 | 450258676.761293 FRANCE | 1996 | 461001893.375384 FRANCE | 1995 | 464790454.044292 FRANCE | 1994 | 457917807.543292 FRANCE | 1993 | 462278504.088971 FRANCE | 1992 | 462509069.49646 GERMANY | 1998 | 273378169.038848 GERMANY | 1997 | 476787871.287641 GERMANY | 1996 | 472530034.976172 GERMANY | 1995 | 479296107.167504 GERMANY | 1994 | 479581009.1407 GERMANY | 1993 | 477645897.240042 GERMANY | 1992 | 479860191.576957 INDIA | 1998 | 275714448.201173 INDIA | 1997 | 469133396.855636 INDIA | 1996 | 476443552.933076 INDIA | 1995 | 487763050.867078 INDIA | 1994 | 476646532.385835 INDIA | 1993 | 475393421.761422 INDIA | 1992 | 480266455.775437 INDONESIA | 1998 | 275694378.190348 INDONESIA | 1997 | 457196937.355532 INDONESIA | 1996 | 468804792.353696 INDONESIA | 1995 | 474128343.817119 INDONESIA | 1994 | 467482109.136514 INDONESIA | 1993 | 463360664.993789 INDONESIA | 1992 | 468668299.528821 IRAN | 1998 | 271059697.236923 IRAN | 1997 | 461540675.872544 IRAN | 1996 | 467732343.45155 IRAN | 1995 | 475636135.340541 IRAN | 1994 | 469918499.305128 IRAN | 1993 | 470358761.51515 IRAN | 1992 | 479181089.390952 IRAQ | 1998 | 275595969.59561 IRAQ | 1997 | 468266993.429365 IRAQ | 1996 | 478780200.038939 IRAQ | 1995 | 470024886.069484 IRAQ | 1994 | 469903664.264215 IRAQ | 1993 | 468942586.338498 IRAQ | 1992 | 477856752.837676 JAPAN | 1998 | 264806062.149962 JAPAN | 1997 | 451240764.614297 JAPAN | 1996 | 464973417.878352 JAPAN | 1995 | 452064269.668848 JAPAN | 1994 | 458968630.078982 JAPAN | 1993 | 462867060.82566 JAPAN | 1992 | 449308641.564769 JORDAN | 1998 | 261913803.88427 JORDAN | 1997 | 463822903.425077 JORDAN | 1996 | 461090874.193597 JORDAN | 1995 | 452449769.691127 JORDAN | 1994 | 462846670.825149 JORDAN | 1993 | 451544521.600835 JORDAN | 1992 | 456904962.899278 KENYA | 1998 | 274806809.311816 KENYA | 1997 | 464089326.429331 KENYA | 1996 | 466858140.734431 KENYA | 1995 | 464709903.230191 KENYA | 1994 | 470244375.083762 KENYA | 1993 | 470300931.448034 KENYA | 1992 | 463426197.71337 MOROCCO | 1998 | 271583020.507574 MOROCCO | 1997 | 468801246.487498 MOROCCO | 1996 | 471098410.597063 MOROCCO | 1995 | 468022544.254268 MOROCCO | 1994 | 465660849.778305 MOROCCO | 1993 | 465151600.580782 MOROCCO | 1992 | 468362807.533095 MOZAMBIQUE | 1998 | 265088375.676715 MOZAMBIQUE | 1997 | 462988147.632907 MOZAMBIQUE | 1996 | 461444682.748625 MOZAMBIQUE | 1995 | 457437619.383265 MOZAMBIQUE | 1994 | 461343728.537288 MOZAMBIQUE | 1993 | 464612020.101622 MOZAMBIQUE | 1992 | 452968579.055079 PERU | 1998 | 278764184.432616 PERU | 1997 | 465031170.870507 PERU | 1996 | 458105726.637796 PERU | 1995 | 458241922.743941 PERU | 1994 | 461186150.225573 PERU | 1993 | 460616485.512628 PERU | 1992 | 462579004.232251 ROMANIA | 1998 | 273922557.500215 ROMANIA | 1997 | 467000308.511723 ROMANIA | 1996 | 465818586.310113 ROMANIA | 1995 | 460534049.867639 ROMANIA | 1994 | 472949980.865919 ROMANIA | 1993 | 470674271.714286 ROMANIA | 1992 | 468009652.170413 RUSSIA | 1998 | 268949897.943485 RUSSIA | 1997 | 460911519.397462 RUSSIA | 1996 | 463686632.329543 RUSSIA | 1995 | 455175591.178413 RUSSIA | 1994 | 465418989.281897 RUSSIA | 1993 | 465536403.37109 RUSSIA | 1992 | 461535370.123678 SAUDI ARABIA | 1998 | 275614226.377337 SAUDI ARABIA | 1997 | 466004767.408686 SAUDI ARABIA | 1996 | 468691668.300748 SAUDI ARABIA | 1995 | 463574434.280541 SAUDI ARABIA | 1994 | 464404509.290454 SAUDI ARABIA | 1993 | 464002811.227155 SAUDI ARABIA | 1992 | 471895898.37221 UNITED KINGDOM | 1998 | 269543211.542922 UNITED KINGDOM | 1997 | 457105650.896455 UNITED KINGDOM | 1996 | 459394785.286613 UNITED KINGDOM | 1995 | 448170180.447427 UNITED KINGDOM | 1994 | 451143115.331081 UNITED KINGDOM | 1993 | 466014213.262517 UNITED KINGDOM | 1992 | 462440514.330063 UNITED STATES | 1998 | 270461189.590669 UNITED STATES | 1997 | 459100193.52602 UNITED STATES | 1996 | 459147008.697167 UNITED STATES | 1995 | 462582298.020767 UNITED STATES | 1994 | 470040296.89256 UNITED STATES | 1993 | 469494144.209864 UNITED STATES | 1992 | 465123508.655121 VIETNAM | 1998 | 276807257.803955 VIETNAM | 1997 | 473694929.58259 VIETNAM | 1996 | 474054456.76293 VIETNAM | 1995 | 460896947.863946 VIETNAM | 1994 | 471091323.774103 VIETNAM | 1993 | 469122781.429715 VIETNAM | 1992 | 478684545.83928 (175 rows) COMMIT; COMMIT