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 '%firebrick%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264357389.899553 ALGERIA | 1997 | 458520337.970119 ALGERIA | 1996 | 461285852.93582 ALGERIA | 1995 | 457425848.720311 ALGERIA | 1994 | 460149622.242718 ALGERIA | 1993 | 460401593.969002 ALGERIA | 1992 | 465005695.81895 ARGENTINA | 1998 | 273535612.410568 ARGENTINA | 1997 | 474119196.590459 ARGENTINA | 1996 | 461179157.151494 ARGENTINA | 1995 | 471445883.163338 ARGENTINA | 1994 | 470101085.433907 ARGENTINA | 1993 | 464422090.967906 ARGENTINA | 1992 | 467321795.45435 BRAZIL | 1998 | 281388443.100931 BRAZIL | 1997 | 476610324.723395 BRAZIL | 1996 | 471920105.844281 BRAZIL | 1995 | 470273471.394141 BRAZIL | 1994 | 473148310.968723 BRAZIL | 1993 | 468428719.961531 BRAZIL | 1992 | 475985829.556608 CANADA | 1998 | 277483338.535839 CANADA | 1997 | 474580620.117048 CANADA | 1996 | 474181429.701299 CANADA | 1995 | 471852551.336813 CANADA | 1994 | 469493138.283904 CANADA | 1993 | 476172960.239419 CANADA | 1992 | 470918943.893468 CHINA | 1998 | 268299853.73032 CHINA | 1997 | 458618214.142371 CHINA | 1996 | 458750987.125283 CHINA | 1995 | 455067586.290382 CHINA | 1994 | 453824201.693623 CHINA | 1993 | 455735261.485731 CHINA | 1992 | 455680397.030896 EGYPT | 1998 | 269708212.019123 EGYPT | 1997 | 473623164.900744 EGYPT | 1996 | 475817555.31879 EGYPT | 1995 | 471935195.257853 EGYPT | 1994 | 467798925.010989 EGYPT | 1993 | 471661306.942899 EGYPT | 1992 | 473744388.273269 ETHIOPIA | 1998 | 268590892.859197 ETHIOPIA | 1997 | 451102151.397232 ETHIOPIA | 1996 | 460663921.628767 ETHIOPIA | 1995 | 456535480.410151 ETHIOPIA | 1994 | 460174310.264 ETHIOPIA | 1993 | 459673572.055863 ETHIOPIA | 1992 | 457527075.070587 FRANCE | 1998 | 270195817.010477 FRANCE | 1997 | 457990539.161903 FRANCE | 1996 | 463043371.631649 FRANCE | 1995 | 451864150.451963 FRANCE | 1994 | 462139991.251475 FRANCE | 1993 | 451829833.891091 FRANCE | 1992 | 459252902.812636 GERMANY | 1998 | 280244507.003595 GERMANY | 1997 | 480227301.642897 GERMANY | 1996 | 474332265.644433 GERMANY | 1995 | 481962407.057729 GERMANY | 1994 | 480448589.338397 GERMANY | 1993 | 479618153.81564 GERMANY | 1992 | 481367887.807037 INDIA | 1998 | 278952624.573271 INDIA | 1997 | 469461111.279748 INDIA | 1996 | 467355915.224887 INDIA | 1995 | 477479194.089723 INDIA | 1994 | 470863961.517924 INDIA | 1993 | 469131959.456345 INDIA | 1992 | 477823341.627155 INDONESIA | 1998 | 266980669.390187 INDONESIA | 1997 | 464367165.107456 INDONESIA | 1996 | 464271030.927108 INDONESIA | 1995 | 465167521.113291 INDONESIA | 1994 | 467151339.942077 INDONESIA | 1993 | 464599532.141766 INDONESIA | 1992 | 466446438.510146 IRAN | 1998 | 273835451.587372 IRAN | 1997 | 474706803.810354 IRAN | 1996 | 470019489.38896 IRAN | 1995 | 464419178.862902 IRAN | 1994 | 470136729.265678 IRAN | 1993 | 470618615.742675 IRAN | 1992 | 474549791.303253 IRAQ | 1998 | 277364309.010218 IRAQ | 1997 | 469300119.833949 IRAQ | 1996 | 476255539.493705 IRAQ | 1995 | 482470444.123469 IRAQ | 1994 | 471308222.003755 IRAQ | 1993 | 476884882.269468 IRAQ | 1992 | 476868337.502879 JAPAN | 1998 | 275312158.795527 JAPAN | 1997 | 473768400.28114 JAPAN | 1996 | 473256589.382072 JAPAN | 1995 | 471044704.001867 JAPAN | 1994 | 476461847.493712 JAPAN | 1993 | 466182846.688358 JAPAN | 1992 | 468396784.605179 JORDAN | 1998 | 261647244.009912 JORDAN | 1997 | 449392752.210208 JORDAN | 1996 | 454715001.098761 JORDAN | 1995 | 445646614.459199 JORDAN | 1994 | 455807623.193612 JORDAN | 1993 | 455336700.290428 JORDAN | 1992 | 450850734.215866 KENYA | 1998 | 274618514.487672 KENYA | 1997 | 468510513.63577 KENYA | 1996 | 476907072.913623 KENYA | 1995 | 469364763.71759 KENYA | 1994 | 475786950.862283 KENYA | 1993 | 468991828.823227 KENYA | 1992 | 483492034.637462 MOROCCO | 1998 | 274545715.195739 MOROCCO | 1997 | 468537884.670362 MOROCCO | 1996 | 470637713.901275 MOROCCO | 1995 | 464270653.668738 MOROCCO | 1994 | 469070249.284802 MOROCCO | 1993 | 467546239.953843 MOROCCO | 1992 | 473560891.48261 MOZAMBIQUE | 1998 | 266212952.527844 MOZAMBIQUE | 1997 | 453279764.433685 MOZAMBIQUE | 1996 | 469040655.490153 MOZAMBIQUE | 1995 | 456615050.371071 MOZAMBIQUE | 1994 | 452112510.052633 MOZAMBIQUE | 1993 | 461166395.33997 MOZAMBIQUE | 1992 | 461464123.892241 PERU | 1998 | 275277433.596773 PERU | 1997 | 468125747.614541 PERU | 1996 | 469582638.595772 PERU | 1995 | 465950971.182777 PERU | 1994 | 459799090.256649 PERU | 1993 | 470239233.391718 PERU | 1992 | 467724104.657471 ROMANIA | 1998 | 273709309.407856 ROMANIA | 1997 | 460992849.953028 ROMANIA | 1996 | 467844054.442103 ROMANIA | 1995 | 463518429.7864 ROMANIA | 1994 | 462986482.366321 ROMANIA | 1993 | 468975168.371894 ROMANIA | 1992 | 468807242.250107 RUSSIA | 1998 | 277503502.847892 RUSSIA | 1997 | 468467056.157536 RUSSIA | 1996 | 474721959.956159 RUSSIA | 1995 | 472079918.105468 RUSSIA | 1994 | 467857650.906463 RUSSIA | 1993 | 470271886.571543 RUSSIA | 1992 | 462635387.713181 SAUDI ARABIA | 1998 | 284273244.072697 SAUDI ARABIA | 1997 | 472100555.656782 SAUDI ARABIA | 1996 | 476495895.908619 SAUDI ARABIA | 1995 | 476067163.89567 SAUDI ARABIA | 1994 | 469181988.682827 SAUDI ARABIA | 1993 | 473601692.182512 SAUDI ARABIA | 1992 | 472233814.552802 UNITED KINGDOM | 1998 | 272310254.02405 UNITED KINGDOM | 1997 | 466451464.874438 UNITED KINGDOM | 1996 | 464621727.062382 UNITED KINGDOM | 1995 | 461973955.71748 UNITED KINGDOM | 1994 | 463275333.740915 UNITED KINGDOM | 1993 | 464130605.824488 UNITED KINGDOM | 1992 | 468701110.266884 UNITED STATES | 1998 | 271529917.890174 UNITED STATES | 1997 | 456985327.27091 UNITED STATES | 1996 | 463011487.812738 UNITED STATES | 1995 | 467407583.902775 UNITED STATES | 1994 | 470918497.165481 UNITED STATES | 1993 | 466724592.554941 UNITED STATES | 1992 | 464111996.95287 VIETNAM | 1998 | 278244103.508733 VIETNAM | 1997 | 463728735.165492 VIETNAM | 1996 | 465599397.214036 VIETNAM | 1995 | 467977049.687356 VIETNAM | 1994 | 464396294.774472 VIETNAM | 1993 | 471203702.537352 VIETNAM | 1992 | 459913934.439431 (175 rows) COMMIT; COMMIT