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 '%rosy%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270891372.197552 ALGERIA | 1997 | 454796709.362414 ALGERIA | 1996 | 468666662.614079 ALGERIA | 1995 | 456067499.845509 ALGERIA | 1994 | 461000704.990748 ALGERIA | 1993 | 463188106.715999 ALGERIA | 1992 | 458318398.477472 ARGENTINA | 1998 | 273948643.603603 ARGENTINA | 1997 | 466358718.668322 ARGENTINA | 1996 | 472220785.375507 ARGENTINA | 1995 | 470478408.463952 ARGENTINA | 1994 | 473847536.110925 ARGENTINA | 1993 | 473274548.108371 ARGENTINA | 1992 | 468517488.42254 BRAZIL | 1998 | 273692903.583334 BRAZIL | 1997 | 460692970.103433 BRAZIL | 1996 | 464951395.806245 BRAZIL | 1995 | 465340882.324721 BRAZIL | 1994 | 466587008.012765 BRAZIL | 1993 | 474309894.673521 BRAZIL | 1992 | 472439530.943601 CANADA | 1998 | 273660793.512595 CANADA | 1997 | 473197684.706045 CANADA | 1996 | 484312445.177729 CANADA | 1995 | 469243543.312555 CANADA | 1994 | 478898540.952038 CANADA | 1993 | 478378090.168348 CANADA | 1992 | 477986416.626474 CHINA | 1998 | 280385965.68063 CHINA | 1997 | 467262787.460997 CHINA | 1996 | 472999146.934952 CHINA | 1995 | 472424745.632884 CHINA | 1994 | 474762159.453228 CHINA | 1993 | 465026776.805475 CHINA | 1992 | 468717257.552913 EGYPT | 1998 | 270288392.700397 EGYPT | 1997 | 465561509.358031 EGYPT | 1996 | 477619874.234089 EGYPT | 1995 | 462254961.976116 EGYPT | 1994 | 466540871.295762 EGYPT | 1993 | 474246479.201555 EGYPT | 1992 | 466056717.872247 ETHIOPIA | 1998 | 275613482.834331 ETHIOPIA | 1997 | 455568601.604849 ETHIOPIA | 1996 | 462948862.229327 ETHIOPIA | 1995 | 460385718.888161 ETHIOPIA | 1994 | 457962802.847278 ETHIOPIA | 1993 | 457908920.888768 ETHIOPIA | 1992 | 458890135.641312 FRANCE | 1998 | 268375840.98375 FRANCE | 1997 | 466996563.37697 FRANCE | 1996 | 474215191.187109 FRANCE | 1995 | 467547889.380605 FRANCE | 1994 | 467731758.954454 FRANCE | 1993 | 466944768.687905 FRANCE | 1992 | 467996383.613762 GERMANY | 1998 | 271669964.245175 GERMANY | 1997 | 469736330.662177 GERMANY | 1996 | 465495959.81169 GERMANY | 1995 | 465995650.787694 GERMANY | 1994 | 461520926.080789 GERMANY | 1993 | 472705468.262556 GERMANY | 1992 | 463600989.7101 INDIA | 1998 | 278065935.177165 INDIA | 1997 | 472410917.546177 INDIA | 1996 | 480226844.799758 INDIA | 1995 | 480789420.898312 INDIA | 1994 | 470703313.079289 INDIA | 1993 | 479255044.86465 INDIA | 1992 | 475281293.786782 INDONESIA | 1998 | 276001124.486277 INDONESIA | 1997 | 467277934.877399 INDONESIA | 1996 | 463735764.245979 INDONESIA | 1995 | 468394184.130604 INDONESIA | 1994 | 471384407.29767 INDONESIA | 1993 | 463970757.988655 INDONESIA | 1992 | 471646777.950393 IRAN | 1998 | 274657010.934171 IRAN | 1997 | 472475359.733119 IRAN | 1996 | 465422845.567694 IRAN | 1995 | 462503128.482837 IRAN | 1994 | 465411366.775262 IRAN | 1993 | 471104018.733793 IRAN | 1992 | 474797734.203012 IRAQ | 1998 | 277343656.065645 IRAQ | 1997 | 470378116.395822 IRAQ | 1996 | 472359821.452052 IRAQ | 1995 | 466052282.519975 IRAQ | 1994 | 476867828.560222 IRAQ | 1993 | 475221045.024304 IRAQ | 1992 | 468663578.85689 JAPAN | 1998 | 270680622.017473 JAPAN | 1997 | 468033267.989746 JAPAN | 1996 | 458792554.548867 JAPAN | 1995 | 460162568.503138 JAPAN | 1994 | 454575945.896897 JAPAN | 1993 | 465275393.201291 JAPAN | 1992 | 460319828.291686 JORDAN | 1998 | 275513985.88057 JORDAN | 1997 | 460747293.738761 JORDAN | 1996 | 458881714.084494 JORDAN | 1995 | 462429293.187317 JORDAN | 1994 | 455467282.679116 JORDAN | 1993 | 464312755.82326 JORDAN | 1992 | 453817064.731282 KENYA | 1998 | 275797372.147457 KENYA | 1997 | 467612847.38662 KENYA | 1996 | 468741604.692295 KENYA | 1995 | 473252779.709025 KENYA | 1994 | 470502831.064814 KENYA | 1993 | 468867166.030533 KENYA | 1992 | 472944115.37646 MOROCCO | 1998 | 265913711.040248 MOROCCO | 1997 | 455812832.699501 MOROCCO | 1996 | 457380083.294848 MOROCCO | 1995 | 459950032.227128 MOROCCO | 1994 | 469534959.169257 MOROCCO | 1993 | 467456293.697824 MOROCCO | 1992 | 458584778.636499 MOZAMBIQUE | 1998 | 268054017.923191 MOZAMBIQUE | 1997 | 449501802.22972 MOZAMBIQUE | 1996 | 457995234.459764 MOZAMBIQUE | 1995 | 457881729.650488 MOZAMBIQUE | 1994 | 459045420.533596 MOZAMBIQUE | 1993 | 446944736.652259 MOZAMBIQUE | 1992 | 456620071.050826 PERU | 1998 | 276411968.26106 PERU | 1997 | 472597452.488248 PERU | 1996 | 473985587.544853 PERU | 1995 | 474555274.259821 PERU | 1994 | 470099911.572133 PERU | 1993 | 472856894.704615 PERU | 1992 | 471788237.12388 ROMANIA | 1998 | 276503213.69824 ROMANIA | 1997 | 473130712.174811 ROMANIA | 1996 | 471412613.289815 ROMANIA | 1995 | 472010168.060703 ROMANIA | 1994 | 467914592.453486 ROMANIA | 1993 | 470069475.27126 ROMANIA | 1992 | 472966510.910605 RUSSIA | 1998 | 280410097.204082 RUSSIA | 1997 | 470663687.434224 RUSSIA | 1996 | 472642984.49549 RUSSIA | 1995 | 472421908.326878 RUSSIA | 1994 | 475226993.714676 RUSSIA | 1993 | 472261590.699725 RUSSIA | 1992 | 468610555.97013 SAUDI ARABIA | 1998 | 279839360.605555 SAUDI ARABIA | 1997 | 464248258.626139 SAUDI ARABIA | 1996 | 472505842.200608 SAUDI ARABIA | 1995 | 465656832.298647 SAUDI ARABIA | 1994 | 466256397.130954 SAUDI ARABIA | 1993 | 466869350.749994 SAUDI ARABIA | 1992 | 471688781.25522 UNITED KINGDOM | 1998 | 274251175.954049 UNITED KINGDOM | 1997 | 460707219.475218 UNITED KINGDOM | 1996 | 462764860.507465 UNITED KINGDOM | 1995 | 468684243.847017 UNITED KINGDOM | 1994 | 458744759.950315 UNITED KINGDOM | 1993 | 470351807.185233 UNITED KINGDOM | 1992 | 464215152.617463 UNITED STATES | 1998 | 278509205.01034 UNITED STATES | 1997 | 465028901.688661 UNITED STATES | 1996 | 473223536.000641 UNITED STATES | 1995 | 466114616.466953 UNITED STATES | 1994 | 482373188.498745 UNITED STATES | 1993 | 475842377.75897 UNITED STATES | 1992 | 474501803.429318 VIETNAM | 1998 | 273595756.821367 VIETNAM | 1997 | 460169854.34506 VIETNAM | 1996 | 470721188.454788 VIETNAM | 1995 | 462625740.859854 VIETNAM | 1994 | 465085413.436083 VIETNAM | 1993 | 466788255.036346 VIETNAM | 1992 | 463015718.512537 (175 rows) COMMIT; COMMIT