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 | 266961033.578716 ALGERIA | 1997 | 457638351.105994 ALGERIA | 1996 | 456000756.864268 ALGERIA | 1995 | 460135150.305207 ALGERIA | 1994 | 456917845.79643 ALGERIA | 1993 | 461338161.874895 ALGERIA | 1992 | 464590488.884641 ARGENTINA | 1998 | 269424452.710697 ARGENTINA | 1997 | 468996335.894398 ARGENTINA | 1996 | 471205840.653253 ARGENTINA | 1995 | 472897515.780052 ARGENTINA | 1994 | 469786003.624402 ARGENTINA | 1993 | 466631042.839146 ARGENTINA | 1992 | 474316132.102447 BRAZIL | 1998 | 269629747.280207 BRAZIL | 1997 | 469326339.82972 BRAZIL | 1996 | 465608686.550821 BRAZIL | 1995 | 468159000.829492 BRAZIL | 1994 | 469034722.300962 BRAZIL | 1993 | 468119986.852969 BRAZIL | 1992 | 463523838.501891 CANADA | 1998 | 272785501.591197 CANADA | 1997 | 474063548.214987 CANADA | 1996 | 479744546.955646 CANADA | 1995 | 467842451.154482 CANADA | 1994 | 475385351.196469 CANADA | 1993 | 468388461.399799 CANADA | 1992 | 469019808.925452 CHINA | 1998 | 267193869.724897 CHINA | 1997 | 471996760.053419 CHINA | 1996 | 462512671.961222 CHINA | 1995 | 461856550.782001 CHINA | 1994 | 460001467.401247 CHINA | 1993 | 464918227.345652 CHINA | 1992 | 463182112.19189 EGYPT | 1998 | 274597127.861639 EGYPT | 1997 | 472394107.761508 EGYPT | 1996 | 461329043.589619 EGYPT | 1995 | 452949550.904052 EGYPT | 1994 | 456718101.978928 EGYPT | 1993 | 466324385.995544 EGYPT | 1992 | 462461894.844997 ETHIOPIA | 1998 | 271607900.395815 ETHIOPIA | 1997 | 464467964.62081 ETHIOPIA | 1996 | 465983926.088332 ETHIOPIA | 1995 | 468137553.709072 ETHIOPIA | 1994 | 463029092.079993 ETHIOPIA | 1993 | 465589459.65729 ETHIOPIA | 1992 | 457993459.697171 FRANCE | 1998 | 267332153.510826 FRANCE | 1997 | 460028920.448559 FRANCE | 1996 | 460956896.179852 FRANCE | 1995 | 469682407.509421 FRANCE | 1994 | 460554908.054465 FRANCE | 1993 | 456523869.546522 FRANCE | 1992 | 470098499.543935 GERMANY | 1998 | 278556492.878745 GERMANY | 1997 | 477546362.542199 GERMANY | 1996 | 462848289.962217 GERMANY | 1995 | 473604670.588436 GERMANY | 1994 | 475311116.043458 GERMANY | 1993 | 477596979.669479 GERMANY | 1992 | 478293849.840485 INDIA | 1998 | 277657635.960117 INDIA | 1997 | 467132634.316245 INDIA | 1996 | 473058706.197668 INDIA | 1995 | 475048316.808815 INDIA | 1994 | 473178035.981965 INDIA | 1993 | 470236234.475474 INDIA | 1992 | 473784917.179289 INDONESIA | 1998 | 268403319.478855 INDONESIA | 1997 | 452145467.579199 INDONESIA | 1996 | 460423830.057538 INDONESIA | 1995 | 461120715.935199 INDONESIA | 1994 | 462156309.91919 INDONESIA | 1993 | 469535158.314869 INDONESIA | 1992 | 463277808.649448 IRAN | 1998 | 271606480.624993 IRAN | 1997 | 458285059.265222 IRAN | 1996 | 461539808.310586 IRAN | 1995 | 465029713.060631 IRAN | 1994 | 460290327.601763 IRAN | 1993 | 472794429.165074 IRAN | 1992 | 470958930.726442 IRAQ | 1998 | 274566221.319378 IRAQ | 1997 | 463787225.094735 IRAQ | 1996 | 475033956.976168 IRAQ | 1995 | 472352247.439452 IRAQ | 1994 | 469987479.548413 IRAQ | 1993 | 463725366.705696 IRAQ | 1992 | 464904059.247881 JAPAN | 1998 | 271890732.019242 JAPAN | 1997 | 459195397.781169 JAPAN | 1996 | 463976805.088187 JAPAN | 1995 | 463023115.086996 JAPAN | 1994 | 459189474.676594 JAPAN | 1993 | 464915352.650533 JAPAN | 1992 | 465825983.989334 JORDAN | 1998 | 266640562.052136 JORDAN | 1997 | 456521367.262908 JORDAN | 1996 | 455181588.525596 JORDAN | 1995 | 461671166.923928 JORDAN | 1994 | 457028658.694944 JORDAN | 1993 | 459321215.886907 JORDAN | 1992 | 453572231.133189 KENYA | 1998 | 276606963.663151 KENYA | 1997 | 465162993.878223 KENYA | 1996 | 457824839.714517 KENYA | 1995 | 468066548.251752 KENYA | 1994 | 459089340.574984 KENYA | 1993 | 464245284.038964 KENYA | 1992 | 465871612.033791 MOROCCO | 1998 | 275705991.517659 MOROCCO | 1997 | 476208694.528558 MOROCCO | 1996 | 475451942.395484 MOROCCO | 1995 | 476308216.373712 MOROCCO | 1994 | 472485595.024613 MOROCCO | 1993 | 474000558.551956 MOROCCO | 1992 | 479123505.814972 MOZAMBIQUE | 1998 | 272205172.082335 MOZAMBIQUE | 1997 | 460804465.980134 MOZAMBIQUE | 1996 | 462024273.037563 MOZAMBIQUE | 1995 | 456771349.774272 MOZAMBIQUE | 1994 | 459042608.530404 MOZAMBIQUE | 1993 | 458918163.2967 MOZAMBIQUE | 1992 | 460449473.047572 PERU | 1998 | 266023370.944579 PERU | 1997 | 454092410.676668 PERU | 1996 | 464995298.067486 PERU | 1995 | 467939779.00258 PERU | 1994 | 464619818.807376 PERU | 1993 | 460449496.413249 PERU | 1992 | 465901937.228192 ROMANIA | 1998 | 276328466.77548 ROMANIA | 1997 | 461671249.563542 ROMANIA | 1996 | 472432185.630436 ROMANIA | 1995 | 465030576.440822 ROMANIA | 1994 | 466006096.967329 ROMANIA | 1993 | 462543494.842528 ROMANIA | 1992 | 462991843.340399 RUSSIA | 1998 | 270252773.768113 RUSSIA | 1997 | 463429755.246492 RUSSIA | 1996 | 470016393.208695 RUSSIA | 1995 | 455538043.65582 RUSSIA | 1994 | 466968839.890504 RUSSIA | 1993 | 460301319.962836 RUSSIA | 1992 | 471539550.97667 SAUDI ARABIA | 1998 | 269769760.095621 SAUDI ARABIA | 1997 | 465444906.588058 SAUDI ARABIA | 1996 | 474342764.441034 SAUDI ARABIA | 1995 | 469002922.163738 SAUDI ARABIA | 1994 | 468409479.166568 SAUDI ARABIA | 1993 | 468845701.512552 SAUDI ARABIA | 1992 | 468833135.066679 UNITED KINGDOM | 1998 | 269187462.210039 UNITED KINGDOM | 1997 | 455128478.296904 UNITED KINGDOM | 1996 | 459494332.563146 UNITED KINGDOM | 1995 | 449521051.211512 UNITED KINGDOM | 1994 | 453004380.645881 UNITED KINGDOM | 1993 | 451173101.356011 UNITED KINGDOM | 1992 | 456390985.387108 UNITED STATES | 1998 | 276012199.951141 UNITED STATES | 1997 | 468280299.391053 UNITED STATES | 1996 | 475111085.02916 UNITED STATES | 1995 | 468643734.974333 UNITED STATES | 1994 | 471252801.87145 UNITED STATES | 1993 | 464616916.262179 UNITED STATES | 1992 | 465960164.032517 VIETNAM | 1998 | 263265482.448288 VIETNAM | 1997 | 454855051.962997 VIETNAM | 1996 | 456817226.292794 VIETNAM | 1995 | 458559227.270884 VIETNAM | 1994 | 458578138.29079 VIETNAM | 1993 | 471683968.400818 VIETNAM | 1992 | 463497214.078309 (175 rows) COMMIT; COMMIT