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 '%lace%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264124094.451888 ALGERIA | 1997 | 451250264.702781 ALGERIA | 1996 | 451664956.219102 ALGERIA | 1995 | 453864804.979456 ALGERIA | 1994 | 458050095.1623 ALGERIA | 1993 | 454368020.94765 ALGERIA | 1992 | 450337282.742703 ARGENTINA | 1998 | 268237960.723002 ARGENTINA | 1997 | 460617920.266032 ARGENTINA | 1996 | 461754193.845349 ARGENTINA | 1995 | 455288182.673466 ARGENTINA | 1994 | 459899383.466639 ARGENTINA | 1993 | 462061656.414495 ARGENTINA | 1992 | 460276827.859127 BRAZIL | 1998 | 276803765.023569 BRAZIL | 1997 | 472594650.390044 BRAZIL | 1996 | 476475695.798368 BRAZIL | 1995 | 482117032.870794 BRAZIL | 1994 | 475586588.511339 BRAZIL | 1993 | 473046732.390675 BRAZIL | 1992 | 471903772.339221 CANADA | 1998 | 280146680.711578 CANADA | 1997 | 476003877.953903 CANADA | 1996 | 481076326.26895 CANADA | 1995 | 470243189.328444 CANADA | 1994 | 471463845.296672 CANADA | 1993 | 476199924.044749 CANADA | 1992 | 476012610.592371 CHINA | 1998 | 272485425.769693 CHINA | 1997 | 463524661.481904 CHINA | 1996 | 462050295.825566 CHINA | 1995 | 457617846.567076 CHINA | 1994 | 464216546.324029 CHINA | 1993 | 463015145.843121 CHINA | 1992 | 459087170.776945 EGYPT | 1998 | 275463431.843148 EGYPT | 1997 | 463761258.257437 EGYPT | 1996 | 461768406.878703 EGYPT | 1995 | 462432094.979858 EGYPT | 1994 | 462023522.952517 EGYPT | 1993 | 468532256.394517 EGYPT | 1992 | 468952267.110664 ETHIOPIA | 1998 | 272293878.414425 ETHIOPIA | 1997 | 466347315.830117 ETHIOPIA | 1996 | 463440905.381942 ETHIOPIA | 1995 | 468098818.536685 ETHIOPIA | 1994 | 472206191.333959 ETHIOPIA | 1993 | 469265013.553078 ETHIOPIA | 1992 | 463347771.177654 FRANCE | 1998 | 262616242.075583 FRANCE | 1997 | 453820482.613998 FRANCE | 1996 | 454781800.600371 FRANCE | 1995 | 462300052.51848 FRANCE | 1994 | 458309346.412538 FRANCE | 1993 | 456324521.757349 FRANCE | 1992 | 453433462.342081 GERMANY | 1998 | 274791084.884985 GERMANY | 1997 | 465484811.152367 GERMANY | 1996 | 466790690.012963 GERMANY | 1995 | 474342414.792333 GERMANY | 1994 | 472005569.514732 GERMANY | 1993 | 473337119.480073 GERMANY | 1992 | 467773238.230217 INDIA | 1998 | 285068686.420359 INDIA | 1997 | 475103099.583774 INDIA | 1996 | 482953973.005808 INDIA | 1995 | 479447671.511151 INDIA | 1994 | 474473327.329452 INDIA | 1993 | 481171805.989013 INDIA | 1992 | 478242983.953669 INDONESIA | 1998 | 270891476.938491 INDONESIA | 1997 | 472784315.208473 INDONESIA | 1996 | 467327762.158712 INDONESIA | 1995 | 463929385.408655 INDONESIA | 1994 | 473883450.684931 INDONESIA | 1993 | 466712549.497531 INDONESIA | 1992 | 467487664.595791 IRAN | 1998 | 269916802.69785 IRAN | 1997 | 467096773.100717 IRAN | 1996 | 459947002.022025 IRAN | 1995 | 465283102.968192 IRAN | 1994 | 457754538.493699 IRAN | 1993 | 462406527.586911 IRAN | 1992 | 467312893.644116 IRAQ | 1998 | 272516261.750946 IRAQ | 1997 | 470014194.236051 IRAQ | 1996 | 467302759.918335 IRAQ | 1995 | 468741058.812562 IRAQ | 1994 | 477560631.314381 IRAQ | 1993 | 462278570.760783 IRAQ | 1992 | 466513813.709632 JAPAN | 1998 | 275490878.042245 JAPAN | 1997 | 465993668.484478 JAPAN | 1996 | 466944904.671239 JAPAN | 1995 | 474063346.347176 JAPAN | 1994 | 458411588.873036 JAPAN | 1993 | 470089720.753103 JAPAN | 1992 | 473356298.435596 JORDAN | 1998 | 269992151.620016 JORDAN | 1997 | 460118396.95267 JORDAN | 1996 | 462870591.784627 JORDAN | 1995 | 462238210.628796 JORDAN | 1994 | 465614359.491161 JORDAN | 1993 | 466574090.589837 JORDAN | 1992 | 463580206.052298 KENYA | 1998 | 278344042.140079 KENYA | 1997 | 474329076.784424 KENYA | 1996 | 470057593.168729 KENYA | 1995 | 468513159.378065 KENYA | 1994 | 473678282.361987 KENYA | 1993 | 473783152.590437 KENYA | 1992 | 485196006.692672 MOROCCO | 1998 | 272641810.303191 MOROCCO | 1997 | 476876569.886311 MOROCCO | 1996 | 466900260.690675 MOROCCO | 1995 | 461849770.682234 MOROCCO | 1994 | 468110442.588054 MOROCCO | 1993 | 468570680.785436 MOROCCO | 1992 | 465055501.080705 MOZAMBIQUE | 1998 | 267644531.967686 MOZAMBIQUE | 1997 | 460446829.669554 MOZAMBIQUE | 1996 | 464896135.394723 MOZAMBIQUE | 1995 | 454064685.19515 MOZAMBIQUE | 1994 | 457534044.671128 MOZAMBIQUE | 1993 | 455771884.175335 MOZAMBIQUE | 1992 | 449900902.923085 PERU | 1998 | 269994769.307256 PERU | 1997 | 459335342.675723 PERU | 1996 | 469599703.39343 PERU | 1995 | 463483779.422358 PERU | 1994 | 459458750.404334 PERU | 1993 | 467813121.608804 PERU | 1992 | 465663719.222586 ROMANIA | 1998 | 271991968.894564 ROMANIA | 1997 | 463646909.809773 ROMANIA | 1996 | 473100740.554036 ROMANIA | 1995 | 467562561.913251 ROMANIA | 1994 | 473850590.777516 ROMANIA | 1993 | 474955773.382632 ROMANIA | 1992 | 469769463.343678 RUSSIA | 1998 | 275828177.440685 RUSSIA | 1997 | 471598353.225157 RUSSIA | 1996 | 473643703.483344 RUSSIA | 1995 | 462699315.570603 RUSSIA | 1994 | 470247034.804391 RUSSIA | 1993 | 463897931.868556 RUSSIA | 1992 | 474391276.079484 SAUDI ARABIA | 1998 | 272883910.091859 SAUDI ARABIA | 1997 | 468231027.911621 SAUDI ARABIA | 1996 | 466649311.145869 SAUDI ARABIA | 1995 | 468655559.593644 SAUDI ARABIA | 1994 | 466546870.59556 SAUDI ARABIA | 1993 | 468586276.559304 SAUDI ARABIA | 1992 | 469223636.308474 UNITED KINGDOM | 1998 | 277715491.889779 UNITED KINGDOM | 1997 | 465098050.606518 UNITED KINGDOM | 1996 | 464926581.406727 UNITED KINGDOM | 1995 | 464817715.811222 UNITED KINGDOM | 1994 | 472552110.274899 UNITED KINGDOM | 1993 | 461291425.274778 UNITED KINGDOM | 1992 | 454360751.108285 UNITED STATES | 1998 | 262883343.593837 UNITED STATES | 1997 | 460322968.542466 UNITED STATES | 1996 | 465188977.281315 UNITED STATES | 1995 | 458373572.632699 UNITED STATES | 1994 | 458571289.171507 UNITED STATES | 1993 | 463488844.081206 UNITED STATES | 1992 | 454667121.31728 VIETNAM | 1998 | 271425643.687635 VIETNAM | 1997 | 465055906.792828 VIETNAM | 1996 | 477844840.565225 VIETNAM | 1995 | 470164586.173227 VIETNAM | 1994 | 464438248.522097 VIETNAM | 1993 | 467853643.295237 VIETNAM | 1992 | 471368147.670228 (175 rows) COMMIT; COMMIT