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 '%medium%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265717924.038358 ALGERIA | 1997 | 463515718.501545 ALGERIA | 1996 | 459450002.615339 ALGERIA | 1995 | 466368166.229397 ALGERIA | 1994 | 461651975.125086 ALGERIA | 1993 | 468243470.935537 ALGERIA | 1992 | 466808233.997105 ARGENTINA | 1998 | 275429875.35743 ARGENTINA | 1997 | 465731806.7842 ARGENTINA | 1996 | 472510435.074236 ARGENTINA | 1995 | 469000676.305977 ARGENTINA | 1994 | 469888988.868325 ARGENTINA | 1993 | 473641661.55218 ARGENTINA | 1992 | 470564656.299171 BRAZIL | 1998 | 272469428.36599 BRAZIL | 1997 | 462667568.571157 BRAZIL | 1996 | 476125582.297764 BRAZIL | 1995 | 468188541.46591 BRAZIL | 1994 | 460675158.579165 BRAZIL | 1993 | 462946732.229367 BRAZIL | 1992 | 471319098.010053 CANADA | 1998 | 273637636.676676 CANADA | 1997 | 476522063.020774 CANADA | 1996 | 477032448.409509 CANADA | 1995 | 476277257.223958 CANADA | 1994 | 476761495.571737 CANADA | 1993 | 474155760.307117 CANADA | 1992 | 479333603.640276 CHINA | 1998 | 269340435.079284 CHINA | 1997 | 462554777.051848 CHINA | 1996 | 465996025.438907 CHINA | 1995 | 463956077.754877 CHINA | 1994 | 465658708.153724 CHINA | 1993 | 466846909.464542 CHINA | 1992 | 470261828.031085 EGYPT | 1998 | 270126900.006596 EGYPT | 1997 | 465539258.019212 EGYPT | 1996 | 464748141.414672 EGYPT | 1995 | 458376792.822833 EGYPT | 1994 | 458246150.901345 EGYPT | 1993 | 469768134.635173 EGYPT | 1992 | 463386962.54868 ETHIOPIA | 1998 | 265107650.702577 ETHIOPIA | 1997 | 463631354.998402 ETHIOPIA | 1996 | 465265035.265024 ETHIOPIA | 1995 | 465660523.487996 ETHIOPIA | 1994 | 462295025.125698 ETHIOPIA | 1993 | 458564433.711135 ETHIOPIA | 1992 | 468839412.240488 FRANCE | 1998 | 263435567.145966 FRANCE | 1997 | 439762815.894854 FRANCE | 1996 | 457670273.26562 FRANCE | 1995 | 449410309.493295 FRANCE | 1994 | 453802891.333488 FRANCE | 1993 | 459285705.337237 FRANCE | 1992 | 448696363.391398 GERMANY | 1998 | 278003555.077432 GERMANY | 1997 | 477264765.360355 GERMANY | 1996 | 473453565.313105 GERMANY | 1995 | 475581221.561725 GERMANY | 1994 | 474095234.842273 GERMANY | 1993 | 482684707.512351 GERMANY | 1992 | 473838171.737366 INDIA | 1998 | 284355922.998784 INDIA | 1997 | 483088103.589527 INDIA | 1996 | 485265014.296061 INDIA | 1995 | 481884107.930456 INDIA | 1994 | 490189556.057046 INDIA | 1993 | 480546959.44097 INDIA | 1992 | 485717270.860531 INDONESIA | 1998 | 272644724.41727 INDONESIA | 1997 | 463081631.34587 INDONESIA | 1996 | 471325920.479629 INDONESIA | 1995 | 465869244.623403 INDONESIA | 1994 | 460400692.623053 INDONESIA | 1993 | 473167905.743128 INDONESIA | 1992 | 464119199.553622 IRAN | 1998 | 270781697.564364 IRAN | 1997 | 456197869.409887 IRAN | 1996 | 454728462.945156 IRAN | 1995 | 457577816.322903 IRAN | 1994 | 456985066.288842 IRAN | 1993 | 460548404.719112 IRAN | 1992 | 458098155.9599 IRAQ | 1998 | 276945844.363125 IRAQ | 1997 | 474175563.833772 IRAQ | 1996 | 472538298.498221 IRAQ | 1995 | 474658204.126552 IRAQ | 1994 | 466525360.315581 IRAQ | 1993 | 473467795.315467 IRAQ | 1992 | 467188231.157898 JAPAN | 1998 | 274552015.021504 JAPAN | 1997 | 461441013.41482 JAPAN | 1996 | 472013802.245423 JAPAN | 1995 | 460789526.573577 JAPAN | 1994 | 476249875.257978 JAPAN | 1993 | 466891335.324685 JAPAN | 1992 | 468603496.938173 JORDAN | 1998 | 269706360.196527 JORDAN | 1997 | 460081680.891315 JORDAN | 1996 | 460593373.526722 JORDAN | 1995 | 459751540.549987 JORDAN | 1994 | 457709841.297968 JORDAN | 1993 | 465156080.159869 JORDAN | 1992 | 465314893.542596 KENYA | 1998 | 275857865.765054 KENYA | 1997 | 471228509.170619 KENYA | 1996 | 473469261.75779 KENYA | 1995 | 473851060.826932 KENYA | 1994 | 463523897.988168 KENYA | 1993 | 467838619.770525 KENYA | 1992 | 470550458.068915 MOROCCO | 1998 | 275287247.285622 MOROCCO | 1997 | 466325912.802439 MOROCCO | 1996 | 466870836.683091 MOROCCO | 1995 | 476677271.999349 MOROCCO | 1994 | 470617102.549373 MOROCCO | 1993 | 473635124.277039 MOROCCO | 1992 | 469004759.772975 MOZAMBIQUE | 1998 | 271671818.636033 MOZAMBIQUE | 1997 | 464994689.257631 MOZAMBIQUE | 1996 | 466620221.472741 MOZAMBIQUE | 1995 | 465816094.135074 MOZAMBIQUE | 1994 | 455955104.375163 MOZAMBIQUE | 1993 | 459800033.077343 MOZAMBIQUE | 1992 | 455820889.870351 PERU | 1998 | 274745303.700186 PERU | 1997 | 468240066.792316 PERU | 1996 | 464207850.617079 PERU | 1995 | 464160815.637889 PERU | 1994 | 463506619.645493 PERU | 1993 | 459863228.225026 PERU | 1992 | 469198126.460078 ROMANIA | 1998 | 267140348.798271 ROMANIA | 1997 | 461608467.181419 ROMANIA | 1996 | 467738771.457259 ROMANIA | 1995 | 465373186.264111 ROMANIA | 1994 | 468050153.969172 ROMANIA | 1993 | 461763303.836497 ROMANIA | 1992 | 467938965.477294 RUSSIA | 1998 | 268960898.010239 RUSSIA | 1997 | 467770027.353221 RUSSIA | 1996 | 469312702.444084 RUSSIA | 1995 | 470412327.676948 RUSSIA | 1994 | 469304671.710475 RUSSIA | 1993 | 460682237.067076 RUSSIA | 1992 | 467715762.07398 SAUDI ARABIA | 1998 | 272668397.544664 SAUDI ARABIA | 1997 | 465533235.369858 SAUDI ARABIA | 1996 | 464963707.109974 SAUDI ARABIA | 1995 | 471152288.488073 SAUDI ARABIA | 1994 | 460625823.7419 SAUDI ARABIA | 1993 | 469682852.328467 SAUDI ARABIA | 1992 | 466823170.795905 UNITED KINGDOM | 1998 | 266325695.136358 UNITED KINGDOM | 1997 | 470109983.120501 UNITED KINGDOM | 1996 | 464810911.376723 UNITED KINGDOM | 1995 | 454708903.978114 UNITED KINGDOM | 1994 | 458340039.895282 UNITED KINGDOM | 1993 | 466758950.169422 UNITED KINGDOM | 1992 | 458147645.68147 UNITED STATES | 1998 | 279725407.069709 UNITED STATES | 1997 | 475821351.275979 UNITED STATES | 1996 | 479462985.016522 UNITED STATES | 1995 | 470283724.162792 UNITED STATES | 1994 | 477584242.79211 UNITED STATES | 1993 | 479689762.320437 UNITED STATES | 1992 | 467540017.597737 VIETNAM | 1998 | 269188752.587786 VIETNAM | 1997 | 462949148.486477 VIETNAM | 1996 | 474603122.971531 VIETNAM | 1995 | 462895714.942577 VIETNAM | 1994 | 470519863.60625 VIETNAM | 1993 | 467961748.493488 VIETNAM | 1992 | 474348377.884392 (175 rows) COMMIT; COMMIT