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 | 267477585.606267 ALGERIA | 1997 | 457766386.662744 ALGERIA | 1996 | 456682713.196388 ALGERIA | 1995 | 458960376.025315 ALGERIA | 1994 | 457537669.973207 ALGERIA | 1993 | 462113561.135609 ALGERIA | 1992 | 463909092.810521 ARGENTINA | 1998 | 269908644.813632 ARGENTINA | 1997 | 468471943.197 ARGENTINA | 1996 | 470899621.946555 ARGENTINA | 1995 | 472710275.786966 ARGENTINA | 1994 | 470186730.117672 ARGENTINA | 1993 | 466229491.634918 ARGENTINA | 1992 | 475367537.193985 BRAZIL | 1998 | 270379789.785848 BRAZIL | 1997 | 469385982.207181 BRAZIL | 1996 | 465847591.370742 BRAZIL | 1995 | 468475870.270353 BRAZIL | 1994 | 469518176.160436 BRAZIL | 1993 | 468963475.294161 BRAZIL | 1992 | 462950374.779474 CANADA | 1998 | 272961432.224396 CANADA | 1997 | 474157891.432518 CANADA | 1996 | 479577082.134916 CANADA | 1995 | 467312409.166542 CANADA | 1994 | 475597448.592351 CANADA | 1993 | 468861938.118289 CANADA | 1992 | 468697844.179889 CHINA | 1998 | 266949782.331051 CHINA | 1997 | 471489568.044707 CHINA | 1996 | 462108390.291872 CHINA | 1995 | 461139816.269832 CHINA | 1994 | 460693872.095519 CHINA | 1993 | 464414843.649566 CHINA | 1992 | 463597831.550291 EGYPT | 1998 | 274579204.060778 EGYPT | 1997 | 472080361.737186 EGYPT | 1996 | 461081387.953889 EGYPT | 1995 | 453173222.058411 EGYPT | 1994 | 456554043.998285 EGYPT | 1993 | 466298805.868546 EGYPT | 1992 | 461842740.278798 ETHIOPIA | 1998 | 271561246.697597 ETHIOPIA | 1997 | 464511940.7105 ETHIOPIA | 1996 | 465651654.424624 ETHIOPIA | 1995 | 468668454.900976 ETHIOPIA | 1994 | 462574485.440242 ETHIOPIA | 1993 | 465097635.90533 ETHIOPIA | 1992 | 457981078.920548 FRANCE | 1998 | 267250469.265682 FRANCE | 1997 | 460672289.807284 FRANCE | 1996 | 460910048.50509 FRANCE | 1995 | 469380915.537543 FRANCE | 1994 | 460758016.506821 FRANCE | 1993 | 456518411.344681 FRANCE | 1992 | 470689754.2128 GERMANY | 1998 | 278858610.478562 GERMANY | 1997 | 476992259.016113 GERMANY | 1996 | 463024669.849131 GERMANY | 1995 | 473874591.839714 GERMANY | 1994 | 474772352.92039 GERMANY | 1993 | 476755529.291724 GERMANY | 1992 | 478113922.823709 INDIA | 1998 | 278310174.574684 INDIA | 1997 | 467333415.64806 INDIA | 1996 | 472705626.437173 INDIA | 1995 | 475013754.854286 INDIA | 1994 | 473768369.005739 INDIA | 1993 | 470126264.498359 INDIA | 1992 | 472959409.639014 INDONESIA | 1998 | 269265477.804541 INDONESIA | 1997 | 451547702.417589 INDONESIA | 1996 | 459607079.609666 INDONESIA | 1995 | 461688751.230705 INDONESIA | 1994 | 462892537.279616 INDONESIA | 1993 | 469409571.521323 INDONESIA | 1992 | 463568452.839555 IRAN | 1998 | 271620114.957481 IRAN | 1997 | 457874332.019801 IRAN | 1996 | 461059053.705424 IRAN | 1995 | 465222872.678001 IRAN | 1994 | 461692457.965495 IRAN | 1993 | 472629179.474194 IRAN | 1992 | 470457977.594066 IRAQ | 1998 | 274547135.770604 IRAQ | 1997 | 464606828.781549 IRAQ | 1996 | 474931904.409763 IRAQ | 1995 | 472883154.756769 IRAQ | 1994 | 470490278.046114 IRAQ | 1993 | 462788324.785876 IRAQ | 1992 | 464676881.173753 JAPAN | 1998 | 271830116.505199 JAPAN | 1997 | 459363257.180939 JAPAN | 1996 | 464066678.937028 JAPAN | 1995 | 463770173.81786 JAPAN | 1994 | 458518563.908587 JAPAN | 1993 | 463878488.676979 JAPAN | 1992 | 465114947.655965 JORDAN | 1998 | 266521422.511622 JORDAN | 1997 | 456242023.468424 JORDAN | 1996 | 455495318.354335 JORDAN | 1995 | 461513121.075228 JORDAN | 1994 | 457241735.195813 JORDAN | 1993 | 459515929.773056 JORDAN | 1992 | 453519834.840237 KENYA | 1998 | 277152547.493104 KENYA | 1997 | 465280072.019629 KENYA | 1996 | 458241968.219863 KENYA | 1995 | 467584932.673379 KENYA | 1994 | 458442375.675046 KENYA | 1993 | 464811415.195253 KENYA | 1992 | 466087013.508311 MOROCCO | 1998 | 275897441.117158 MOROCCO | 1997 | 476778342.349145 MOROCCO | 1996 | 474758175.242477 MOROCCO | 1995 | 477086689.358372 MOROCCO | 1994 | 472582748.404827 MOROCCO | 1993 | 474302508.432255 MOROCCO | 1992 | 478751027.718427 MOZAMBIQUE | 1998 | 272173925.602255 MOZAMBIQUE | 1997 | 459350491.077214 MOZAMBIQUE | 1996 | 461867646.664761 MOZAMBIQUE | 1995 | 457256930.451935 MOZAMBIQUE | 1994 | 458127473.202969 MOZAMBIQUE | 1993 | 459253258.386708 MOZAMBIQUE | 1992 | 459950008.859813 PERU | 1998 | 266607813.313908 PERU | 1997 | 454088625.772918 PERU | 1996 | 465425080.541076 PERU | 1995 | 468588935.919972 PERU | 1994 | 464797393.532733 PERU | 1993 | 459861311.732243 PERU | 1992 | 466013115.822317 ROMANIA | 1998 | 275911060.796445 ROMANIA | 1997 | 462472021.360054 ROMANIA | 1996 | 472670657.465648 ROMANIA | 1995 | 464854073.515097 ROMANIA | 1994 | 466333381.744271 ROMANIA | 1993 | 462205672.518804 ROMANIA | 1992 | 462952818.226681 RUSSIA | 1998 | 270788770.515402 RUSSIA | 1997 | 463634880.21866 RUSSIA | 1996 | 469459528.801119 RUSSIA | 1995 | 455381782.095024 RUSSIA | 1994 | 466338339.287777 RUSSIA | 1993 | 460114624.139029 RUSSIA | 1992 | 471844959.787318 SAUDI ARABIA | 1998 | 269749814.225047 SAUDI ARABIA | 1997 | 465873642.52443 SAUDI ARABIA | 1996 | 473221776.138734 SAUDI ARABIA | 1995 | 469016744.932639 SAUDI ARABIA | 1994 | 468105393.68219 SAUDI ARABIA | 1993 | 468780269.276438 SAUDI ARABIA | 1992 | 468151185.078986 UNITED KINGDOM | 1998 | 269258678.098169 UNITED KINGDOM | 1997 | 455452555.027186 UNITED KINGDOM | 1996 | 459165391.054861 UNITED KINGDOM | 1995 | 450116200.955225 UNITED KINGDOM | 1994 | 453525483.251482 UNITED KINGDOM | 1993 | 451838482.662694 UNITED KINGDOM | 1992 | 456737639.192017 UNITED STATES | 1998 | 276520602.428581 UNITED STATES | 1997 | 468782255.854438 UNITED STATES | 1996 | 474888169.353879 UNITED STATES | 1995 | 468101029.340559 UNITED STATES | 1994 | 471832867.668147 UNITED STATES | 1993 | 465024383.830914 UNITED STATES | 1992 | 467008028.803047 VIETNAM | 1998 | 262812084.06272 VIETNAM | 1997 | 455495654.108858 VIETNAM | 1996 | 456615489.091928 VIETNAM | 1995 | 458144793.580096 VIETNAM | 1994 | 457630494.228322 VIETNAM | 1993 | 471345339.533266 VIETNAM | 1992 | 464322492.327522 (175 rows) COMMIT; COMMIT