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 '%smoke%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 266962282.586582 ALGERIA | 1997 | 457767615.391617 ALGERIA | 1996 | 456144210.250364 ALGERIA | 1995 | 459457304.841356 ALGERIA | 1994 | 457444307.699228 ALGERIA | 1993 | 462414215.332441 ALGERIA | 1992 | 464273256.479031 ARGENTINA | 1998 | 269356089.143589 ARGENTINA | 1997 | 468732150.688375 ARGENTINA | 1996 | 470749962.785722 ARGENTINA | 1995 | 472648361.664407 ARGENTINA | 1994 | 470130897.034313 ARGENTINA | 1993 | 466504363.264551 ARGENTINA | 1992 | 475100680.097657 BRAZIL | 1998 | 269870144.926311 BRAZIL | 1997 | 469060352.319008 BRAZIL | 1996 | 465932558.398573 BRAZIL | 1995 | 468436441.517808 BRAZIL | 1994 | 469090347.594688 BRAZIL | 1993 | 468374364.409197 BRAZIL | 1992 | 463117340.320258 CANADA | 1998 | 273035140.57075 CANADA | 1997 | 474259333.41051 CANADA | 1996 | 479474928.798267 CANADA | 1995 | 468076222.241759 CANADA | 1994 | 475172975.594775 CANADA | 1993 | 468581360.381155 CANADA | 1992 | 468866488.898597 CHINA | 1998 | 266891725.262162 CHINA | 1997 | 471473558.642923 CHINA | 1996 | 462296798.431617 CHINA | 1995 | 461307401.512303 CHINA | 1994 | 460405943.750557 CHINA | 1993 | 464442513.207366 CHINA | 1992 | 462555587.372783 EGYPT | 1998 | 274580097.287327 EGYPT | 1997 | 472634233.262762 EGYPT | 1996 | 461489833.331298 EGYPT | 1995 | 453186793.919679 EGYPT | 1994 | 456802193.806699 EGYPT | 1993 | 466189308.293335 EGYPT | 1992 | 462447068.450251 ETHIOPIA | 1998 | 271063129.365521 ETHIOPIA | 1997 | 464241505.239641 ETHIOPIA | 1996 | 466192521.488056 ETHIOPIA | 1995 | 468525794.998161 ETHIOPIA | 1994 | 462743934.3608 ETHIOPIA | 1993 | 465456927.78057 ETHIOPIA | 1992 | 458234887.217592 FRANCE | 1998 | 267439901.876506 FRANCE | 1997 | 460151567.778401 FRANCE | 1996 | 461027001.936157 FRANCE | 1995 | 469548965.655732 FRANCE | 1994 | 460887754.75303 FRANCE | 1993 | 456486429.882537 FRANCE | 1992 | 470408367.894371 GERMANY | 1998 | 278440229.576756 GERMANY | 1997 | 477533550.635897 GERMANY | 1996 | 463192819.203768 GERMANY | 1995 | 473744441.460499 GERMANY | 1994 | 474954732.078021 GERMANY | 1993 | 476866320.834968 GERMANY | 1992 | 478059059.768714 INDIA | 1998 | 277826481.436102 INDIA | 1997 | 467177746.003558 INDIA | 1996 | 472555099.706058 INDIA | 1995 | 475042643.208405 INDIA | 1994 | 473203627.693569 INDIA | 1993 | 470244463.526368 INDIA | 1992 | 473418625.1706 INDONESIA | 1998 | 269012692.741323 INDONESIA | 1997 | 451920286.779803 INDONESIA | 1996 | 459555350.480642 INDONESIA | 1995 | 461682560.634615 INDONESIA | 1994 | 462374165.701459 INDONESIA | 1993 | 469539589.703093 INDONESIA | 1992 | 463394207.868149 IRAN | 1998 | 271736702.565661 IRAN | 1997 | 457393632.706488 IRAN | 1996 | 461038676.311719 IRAN | 1995 | 465112373.539845 IRAN | 1994 | 461305883.868508 IRAN | 1993 | 473139579.688731 IRAN | 1992 | 470840676.894782 IRAQ | 1998 | 274755037.236947 IRAQ | 1997 | 464380119.35927 IRAQ | 1996 | 474606343.503607 IRAQ | 1995 | 472976088.862736 IRAQ | 1994 | 470400435.017658 IRAQ | 1993 | 463438152.338641 IRAQ | 1992 | 464623894.985889 JAPAN | 1998 | 271989214.171514 JAPAN | 1997 | 459732858.743622 JAPAN | 1996 | 463891437.766156 JAPAN | 1995 | 463716931.609088 JAPAN | 1994 | 458913302.242047 JAPAN | 1993 | 464538000.786488 JAPAN | 1992 | 465541766.54786 JORDAN | 1998 | 266877991.034824 JORDAN | 1997 | 455887884.702159 JORDAN | 1996 | 455279925.510743 JORDAN | 1995 | 461553338.572204 JORDAN | 1994 | 457104484.666686 JORDAN | 1993 | 459699761.316345 JORDAN | 1992 | 453435552.263448 KENYA | 1998 | 276939499.871568 KENYA | 1997 | 465202923.738305 KENYA | 1996 | 457856857.027372 KENYA | 1995 | 467790886.62772 KENYA | 1994 | 459196667.406856 KENYA | 1993 | 464354183.927872 KENYA | 1992 | 465951242.491329 MOROCCO | 1998 | 275680964.303826 MOROCCO | 1997 | 476647812.612658 MOROCCO | 1996 | 475278361.019469 MOROCCO | 1995 | 476755235.465291 MOROCCO | 1994 | 472493173.753112 MOROCCO | 1993 | 474497470.611045 MOROCCO | 1992 | 478329244.689705 MOZAMBIQUE | 1998 | 272122737.659907 MOZAMBIQUE | 1997 | 460314896.609916 MOZAMBIQUE | 1996 | 461837139.412105 MOZAMBIQUE | 1995 | 456640576.82827 MOZAMBIQUE | 1994 | 458750951.975346 MOZAMBIQUE | 1993 | 458818661.372753 MOZAMBIQUE | 1992 | 459720542.753956 PERU | 1998 | 266435139.589424 PERU | 1997 | 453665778.063691 PERU | 1996 | 465205183.726161 PERU | 1995 | 468115465.734391 PERU | 1994 | 464879961.184912 PERU | 1993 | 460703663.265934 PERU | 1992 | 466108893.218477 ROMANIA | 1998 | 276187887.579477 ROMANIA | 1997 | 462039656.048114 ROMANIA | 1996 | 472796946.420937 ROMANIA | 1995 | 464591350.481373 ROMANIA | 1994 | 465600157.596794 ROMANIA | 1993 | 462490489.831822 ROMANIA | 1992 | 462793177.619085 RUSSIA | 1998 | 270407246.220793 RUSSIA | 1997 | 463313077.807817 RUSSIA | 1996 | 469944148.647284 RUSSIA | 1995 | 455420216.781266 RUSSIA | 1994 | 466626714.417897 RUSSIA | 1993 | 460100385.333259 RUSSIA | 1992 | 471853240.429267 SAUDI ARABIA | 1998 | 269900983.236406 SAUDI ARABIA | 1997 | 466344566.457969 SAUDI ARABIA | 1996 | 473623428.40212 SAUDI ARABIA | 1995 | 468721775.516865 SAUDI ARABIA | 1994 | 468477631.592793 SAUDI ARABIA | 1993 | 468869038.441889 SAUDI ARABIA | 1992 | 468470383.153987 UNITED KINGDOM | 1998 | 269100673.197708 UNITED KINGDOM | 1997 | 455252693.450286 UNITED KINGDOM | 1996 | 459419654.118072 UNITED KINGDOM | 1995 | 449407288.727349 UNITED KINGDOM | 1994 | 453180457.877388 UNITED KINGDOM | 1993 | 451576915.361916 UNITED KINGDOM | 1992 | 456123434.453323 UNITED STATES | 1998 | 276229467.599918 UNITED STATES | 1997 | 468077621.261478 UNITED STATES | 1996 | 474895888.03835 UNITED STATES | 1995 | 468239267.288036 UNITED STATES | 1994 | 471356540.418095 UNITED STATES | 1993 | 464758415.465771 UNITED STATES | 1992 | 466832790.935003 VIETNAM | 1998 | 262923769.338501 VIETNAM | 1997 | 455291840.570903 VIETNAM | 1996 | 456795695.849163 VIETNAM | 1995 | 458569203.125707 VIETNAM | 1994 | 458234103.108528 VIETNAM | 1993 | 471692041.896577 VIETNAM | 1992 | 463653834.926688 (175 rows) COMMIT; COMMIT