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 '%plum%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 266858877.009581 ALGERIA | 1997 | 446733277.273723 ALGERIA | 1996 | 463705903.902135 ALGERIA | 1995 | 460039850.27546 ALGERIA | 1994 | 457607751.088592 ALGERIA | 1993 | 463421596.048135 ALGERIA | 1992 | 460936372.88697 ARGENTINA | 1998 | 270052173.974545 ARGENTINA | 1997 | 468015528.97394 ARGENTINA | 1996 | 467025723.241282 ARGENTINA | 1995 | 461489138.980034 ARGENTINA | 1994 | 465924955.096015 ARGENTINA | 1993 | 465569830.197532 ARGENTINA | 1992 | 456589039.774336 BRAZIL | 1998 | 273491813.976155 BRAZIL | 1997 | 482722592.123302 BRAZIL | 1996 | 473514225.053185 BRAZIL | 1995 | 468014447.415636 BRAZIL | 1994 | 469395881.810087 BRAZIL | 1993 | 483271065.560886 BRAZIL | 1992 | 474128949.162996 CANADA | 1998 | 271148436.497666 CANADA | 1997 | 468620892.157714 CANADA | 1996 | 475889258.766937 CANADA | 1995 | 473705903.707329 CANADA | 1994 | 483202160.267674 CANADA | 1993 | 479604210.186334 CANADA | 1992 | 472770054.172595 CHINA | 1998 | 273574197.962128 CHINA | 1997 | 465822487.501705 CHINA | 1996 | 476090236.944712 CHINA | 1995 | 472850565.402682 CHINA | 1994 | 475565534.255783 CHINA | 1993 | 467745216.838339 CHINA | 1992 | 468933815.365715 EGYPT | 1998 | 274216998.199098 EGYPT | 1997 | 464869895.617627 EGYPT | 1996 | 471546068.68078 EGYPT | 1995 | 463965946.075511 EGYPT | 1994 | 454972857.64678 EGYPT | 1993 | 461697818.08348 EGYPT | 1992 | 465993380.107485 ETHIOPIA | 1998 | 268515004.111671 ETHIOPIA | 1997 | 456789081.493203 ETHIOPIA | 1996 | 466409981.777526 ETHIOPIA | 1995 | 466077782.458175 ETHIOPIA | 1994 | 464123785.673862 ETHIOPIA | 1993 | 456807508.762321 ETHIOPIA | 1992 | 467663826.861792 FRANCE | 1998 | 271215526.276325 FRANCE | 1997 | 463640793.735393 FRANCE | 1996 | 467077162.23386 FRANCE | 1995 | 470389013.489112 FRANCE | 1994 | 464967183.133964 FRANCE | 1993 | 465794388.095165 FRANCE | 1992 | 464127510.99735 GERMANY | 1998 | 272807949.575526 GERMANY | 1997 | 463515498.940989 GERMANY | 1996 | 465596482.468808 GERMANY | 1995 | 469309198.950147 GERMANY | 1994 | 469326333.037339 GERMANY | 1993 | 469124777.088966 GERMANY | 1992 | 472887884.527744 INDIA | 1998 | 271188691.304828 INDIA | 1997 | 468634465.490532 INDIA | 1996 | 477678265.592083 INDIA | 1995 | 476311047.064762 INDIA | 1994 | 466996106.514918 INDIA | 1993 | 470660354.84978 INDIA | 1992 | 478574693.450896 INDONESIA | 1998 | 271679557.602788 INDONESIA | 1997 | 468259687.029548 INDONESIA | 1996 | 463885378.176179 INDONESIA | 1995 | 462279047.483004 INDONESIA | 1994 | 459123623.668545 INDONESIA | 1993 | 465670616.686345 INDONESIA | 1992 | 468661534.536302 IRAN | 1998 | 274537527.827929 IRAN | 1997 | 468716852.570738 IRAN | 1996 | 464629141.339553 IRAN | 1995 | 469183823.549319 IRAN | 1994 | 464665345.700969 IRAN | 1993 | 463655159.46247 IRAN | 1992 | 461592950.235239 IRAQ | 1998 | 271710901.391801 IRAQ | 1997 | 468903830.150615 IRAQ | 1996 | 481993686.466832 IRAQ | 1995 | 460203097.884133 IRAQ | 1994 | 470784607.874043 IRAQ | 1993 | 474401568.649387 IRAQ | 1992 | 466719942.153591 JAPAN | 1998 | 274369699.709242 JAPAN | 1997 | 469892114.627815 JAPAN | 1996 | 471514285.478857 JAPAN | 1995 | 467061206.259899 JAPAN | 1994 | 459005574.85789 JAPAN | 1993 | 467942341.523266 JAPAN | 1992 | 461636117.017738 JORDAN | 1998 | 269670418.035277 JORDAN | 1997 | 470019596.014778 JORDAN | 1996 | 463315992.985702 JORDAN | 1995 | 462761739.606295 JORDAN | 1994 | 458181932.12557 JORDAN | 1993 | 471759477.205559 JORDAN | 1992 | 463876043.97426 KENYA | 1998 | 281244955.119945 KENYA | 1997 | 473557091.091172 KENYA | 1996 | 475472369.897881 KENYA | 1995 | 477466148.972798 KENYA | 1994 | 480692237.583474 KENYA | 1993 | 475333090.20562 KENYA | 1992 | 477884255.065833 MOROCCO | 1998 | 276525665.280553 MOROCCO | 1997 | 471490814.45478 MOROCCO | 1996 | 462956761.964795 MOROCCO | 1995 | 464385184.991584 MOROCCO | 1994 | 470864602.670681 MOROCCO | 1993 | 463738322.017841 MOROCCO | 1992 | 464973333.994442 MOZAMBIQUE | 1998 | 266108294.120458 MOZAMBIQUE | 1997 | 455256230.139938 MOZAMBIQUE | 1996 | 454173531.120276 MOZAMBIQUE | 1995 | 449603831.162984 MOZAMBIQUE | 1994 | 451165844.01858 MOZAMBIQUE | 1993 | 463628395.10949 MOZAMBIQUE | 1992 | 459827812.697805 PERU | 1998 | 271985347.240782 PERU | 1997 | 461009488.269215 PERU | 1996 | 459379316.479559 PERU | 1995 | 455049330.422035 PERU | 1994 | 455880842.818211 PERU | 1993 | 463779730.15403 PERU | 1992 | 463716954.764075 ROMANIA | 1998 | 275597344.522889 ROMANIA | 1997 | 457027132.015097 ROMANIA | 1996 | 464428641.882498 ROMANIA | 1995 | 468317802.323989 ROMANIA | 1994 | 469485896.10268 ROMANIA | 1993 | 459855349.909418 ROMANIA | 1992 | 456086011.361322 RUSSIA | 1998 | 277723083.278784 RUSSIA | 1997 | 476063901.8818 RUSSIA | 1996 | 471221505.616891 RUSSIA | 1995 | 477583306.795358 RUSSIA | 1994 | 471879176.07798 RUSSIA | 1993 | 463573573.55121 RUSSIA | 1992 | 467803692.703777 SAUDI ARABIA | 1998 | 273176121.432286 SAUDI ARABIA | 1997 | 468096527.528554 SAUDI ARABIA | 1996 | 468493012.381427 SAUDI ARABIA | 1995 | 469179109.31284 SAUDI ARABIA | 1994 | 462819145.761219 SAUDI ARABIA | 1993 | 465142219.337801 SAUDI ARABIA | 1992 | 471641011.246552 UNITED KINGDOM | 1998 | 268016970.290173 UNITED KINGDOM | 1997 | 465914864.993448 UNITED KINGDOM | 1996 | 459868262.967216 UNITED KINGDOM | 1995 | 462699025.233169 UNITED KINGDOM | 1994 | 455491126.665164 UNITED KINGDOM | 1993 | 461226711.530432 UNITED KINGDOM | 1992 | 464326661.518227 UNITED STATES | 1998 | 276958383.185032 UNITED STATES | 1997 | 481267280.586409 UNITED STATES | 1996 | 474032942.57575 UNITED STATES | 1995 | 472660962.45614 UNITED STATES | 1994 | 473307591.703152 UNITED STATES | 1993 | 473632312.387147 UNITED STATES | 1992 | 471726109.571648 VIETNAM | 1998 | 274872065.369355 VIETNAM | 1997 | 467923756.826078 VIETNAM | 1996 | 481053411.498078 VIETNAM | 1995 | 467246806.661315 VIETNAM | 1994 | 475927188.520727 VIETNAM | 1993 | 476849461.343929 VIETNAM | 1992 | 472046986.978463 (175 rows) COMMIT; COMMIT