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 '%royal%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 260709549.310095 ALGERIA | 1997 | 459213830.916939 ALGERIA | 1996 | 463408439.663173 ALGERIA | 1995 | 453175016.37372 ALGERIA | 1994 | 452890058.569777 ALGERIA | 1993 | 460129036.508531 ALGERIA | 1992 | 461621191.589147 ARGENTINA | 1998 | 288183773.700907 ARGENTINA | 1997 | 482807238.542673 ARGENTINA | 1996 | 490289795.205554 ARGENTINA | 1995 | 484101871.775412 ARGENTINA | 1994 | 487599396.482624 ARGENTINA | 1993 | 476561662.125689 ARGENTINA | 1992 | 480265414.093982 BRAZIL | 1998 | 276207677.190391 BRAZIL | 1997 | 470916310.736519 BRAZIL | 1996 | 471830887.7584 BRAZIL | 1995 | 470172618.435836 BRAZIL | 1994 | 463224771.463327 BRAZIL | 1993 | 476587902.658787 BRAZIL | 1992 | 470577452.878622 CANADA | 1998 | 275952775.588063 CANADA | 1997 | 474737816.993077 CANADA | 1996 | 473439277.935406 CANADA | 1995 | 472753500.668757 CANADA | 1994 | 476889281.264486 CANADA | 1993 | 472006528.683708 CANADA | 1992 | 469989793.279732 CHINA | 1998 | 269685163.804398 CHINA | 1997 | 464438852.639799 CHINA | 1996 | 466983259.696047 CHINA | 1995 | 467408141.380476 CHINA | 1994 | 467975904.182415 CHINA | 1993 | 471023390.909545 CHINA | 1992 | 465829774.161076 EGYPT | 1998 | 270562226.077382 EGYPT | 1997 | 461635544.275519 EGYPT | 1996 | 463069604.639785 EGYPT | 1995 | 465023540.611233 EGYPT | 1994 | 461337157.238576 EGYPT | 1993 | 460591566.109921 EGYPT | 1992 | 457351751.960701 ETHIOPIA | 1998 | 270317810.172729 ETHIOPIA | 1997 | 462101182.246398 ETHIOPIA | 1996 | 455764102.73549 ETHIOPIA | 1995 | 458022591.394337 ETHIOPIA | 1994 | 458191534.216997 ETHIOPIA | 1993 | 462668677.009771 ETHIOPIA | 1992 | 463631913.976818 FRANCE | 1998 | 276095451.267602 FRANCE | 1997 | 463256978.422974 FRANCE | 1996 | 462967717.224997 FRANCE | 1995 | 460631025.230405 FRANCE | 1994 | 464404016.14192 FRANCE | 1993 | 465230994.471893 FRANCE | 1992 | 462852922.776131 GERMANY | 1998 | 277818329.253692 GERMANY | 1997 | 475734162.564156 GERMANY | 1996 | 481934407.434922 GERMANY | 1995 | 474542975.706042 GERMANY | 1994 | 477918647.766383 GERMANY | 1993 | 482369199.36378 GERMANY | 1992 | 477381471.717307 INDIA | 1998 | 279976330.748547 INDIA | 1997 | 465258243.396091 INDIA | 1996 | 468339981.887166 INDIA | 1995 | 462076748.708173 INDIA | 1994 | 474582815.541336 INDIA | 1993 | 478246141.91136 INDIA | 1992 | 476625126.916874 INDONESIA | 1998 | 275213820.001777 INDONESIA | 1997 | 468012870.950254 INDONESIA | 1996 | 463242698.612233 INDONESIA | 1995 | 467098495.793988 INDONESIA | 1994 | 465509346.429138 INDONESIA | 1993 | 474663500.394774 INDONESIA | 1992 | 459667685.355641 IRAN | 1998 | 268564850.600926 IRAN | 1997 | 456358671.625791 IRAN | 1996 | 456974997.135128 IRAN | 1995 | 464158625.522968 IRAN | 1994 | 459223496.949798 IRAN | 1993 | 457940434.950066 IRAN | 1992 | 462271838.213534 IRAQ | 1998 | 273669356.912302 IRAQ | 1997 | 467207740.782355 IRAQ | 1996 | 475462303.577202 IRAQ | 1995 | 466096343.879355 IRAQ | 1994 | 469274671.177229 IRAQ | 1993 | 470227021.387962 IRAQ | 1992 | 471997591.133788 JAPAN | 1998 | 264652367.848965 JAPAN | 1997 | 457672432.711102 JAPAN | 1996 | 462897257.751031 JAPAN | 1995 | 456715021.918564 JAPAN | 1994 | 469818185.076128 JAPAN | 1993 | 465774400.871448 JAPAN | 1992 | 462922696.436112 JORDAN | 1998 | 274010622.519833 JORDAN | 1997 | 465596771.152783 JORDAN | 1996 | 464341237.719516 JORDAN | 1995 | 458779084.876357 JORDAN | 1994 | 457090018.844564 JORDAN | 1993 | 464709313.022815 JORDAN | 1992 | 459703914.848828 KENYA | 1998 | 279024240.082648 KENYA | 1997 | 472568032.497736 KENYA | 1996 | 471411675.806517 KENYA | 1995 | 474370354.480653 KENYA | 1994 | 468359854.369842 KENYA | 1993 | 471135504.874497 KENYA | 1992 | 472080551.762137 MOROCCO | 1998 | 270321756.224423 MOROCCO | 1997 | 463302663.551005 MOROCCO | 1996 | 471025017.876724 MOROCCO | 1995 | 469239267.752758 MOROCCO | 1994 | 462424431.744648 MOROCCO | 1993 | 465580936.27598 MOROCCO | 1992 | 465408011.77048 MOZAMBIQUE | 1998 | 273598103.209096 MOZAMBIQUE | 1997 | 470205399.319733 MOZAMBIQUE | 1996 | 465055069.006161 MOZAMBIQUE | 1995 | 459167292.41522 MOZAMBIQUE | 1994 | 470374743.422514 MOZAMBIQUE | 1993 | 473401360.761636 MOZAMBIQUE | 1992 | 461023380.886448 PERU | 1998 | 279199026.999052 PERU | 1997 | 470511154.248599 PERU | 1996 | 471506938.037137 PERU | 1995 | 466559938.67023 PERU | 1994 | 464184146.343163 PERU | 1993 | 453879939.061603 PERU | 1992 | 463183910.587925 ROMANIA | 1998 | 277939056.447243 ROMANIA | 1997 | 462644109.746558 ROMANIA | 1996 | 476622025.925492 ROMANIA | 1995 | 472332357.291574 ROMANIA | 1994 | 470162088.998631 ROMANIA | 1993 | 474913716.441999 ROMANIA | 1992 | 474562900.836801 RUSSIA | 1998 | 276623436.860898 RUSSIA | 1997 | 468035474.50088 RUSSIA | 1996 | 474852237.742012 RUSSIA | 1995 | 469658694.238819 RUSSIA | 1994 | 474888884.592017 RUSSIA | 1993 | 477226258.718943 RUSSIA | 1992 | 472755227.882646 SAUDI ARABIA | 1998 | 274954437.991183 SAUDI ARABIA | 1997 | 465263496.638856 SAUDI ARABIA | 1996 | 459126776.057246 SAUDI ARABIA | 1995 | 464410426.253993 SAUDI ARABIA | 1994 | 459097773.368383 SAUDI ARABIA | 1993 | 468004104.698376 SAUDI ARABIA | 1992 | 460158097.328189 UNITED KINGDOM | 1998 | 275412800.715326 UNITED KINGDOM | 1997 | 465658097.628783 UNITED KINGDOM | 1996 | 466239932.481021 UNITED KINGDOM | 1995 | 459768145.881359 UNITED KINGDOM | 1994 | 462171670.381105 UNITED KINGDOM | 1993 | 469679487.427387 UNITED KINGDOM | 1992 | 465782645.401885 UNITED STATES | 1998 | 280228747.593426 UNITED STATES | 1997 | 477499832.848064 UNITED STATES | 1996 | 476931695.286132 UNITED STATES | 1995 | 476546099.774074 UNITED STATES | 1994 | 482477148.867096 UNITED STATES | 1993 | 475087258.492288 UNITED STATES | 1992 | 476288390.249268 VIETNAM | 1998 | 273773466.835794 VIETNAM | 1997 | 469955983.903799 VIETNAM | 1996 | 475458774.932448 VIETNAM | 1995 | 462374991.617862 VIETNAM | 1994 | 467289928.96467 VIETNAM | 1993 | 469632799.711668 VIETNAM | 1992 | 473842676.883306 (175 rows) COMMIT; COMMIT