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 '%cyan%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 273780009.001595 ALGERIA | 1997 | 461181181.787372 ALGERIA | 1996 | 473959641.809352 ALGERIA | 1995 | 464354724.57862 ALGERIA | 1994 | 472996205.582006 ALGERIA | 1993 | 468916242.182149 ALGERIA | 1992 | 467566215.733158 ARGENTINA | 1998 | 278965416.674081 ARGENTINA | 1997 | 469055466.753085 ARGENTINA | 1996 | 475535945.016698 ARGENTINA | 1995 | 468322699.015104 ARGENTINA | 1994 | 471093286.851715 ARGENTINA | 1993 | 472645563.457825 ARGENTINA | 1992 | 470566625.738273 BRAZIL | 1998 | 279380188.024733 BRAZIL | 1997 | 476014401.250366 BRAZIL | 1996 | 474430000.7344 BRAZIL | 1995 | 478973307.046759 BRAZIL | 1994 | 475128699.735313 BRAZIL | 1993 | 482991883.093052 BRAZIL | 1992 | 488212985.566165 CANADA | 1998 | 276693900.132679 CANADA | 1997 | 477710610.309052 CANADA | 1996 | 480606909.655419 CANADA | 1995 | 479573450.848384 CANADA | 1994 | 489611948.915425 CANADA | 1993 | 474326888.320938 CANADA | 1992 | 483242820.795491 CHINA | 1998 | 266068126.811621 CHINA | 1997 | 470587194.32937 CHINA | 1996 | 462515393.943347 CHINA | 1995 | 469752541.319401 CHINA | 1994 | 458726624.774428 CHINA | 1993 | 467276395.513238 CHINA | 1992 | 466457402.049425 EGYPT | 1998 | 276016807.346373 EGYPT | 1997 | 470058914.215412 EGYPT | 1996 | 472123439.615563 EGYPT | 1995 | 465861023.379254 EGYPT | 1994 | 462410209.233312 EGYPT | 1993 | 467847982.179333 EGYPT | 1992 | 473216419.023919 ETHIOPIA | 1998 | 272359926.844693 ETHIOPIA | 1997 | 455338997.305925 ETHIOPIA | 1996 | 458834154.645093 ETHIOPIA | 1995 | 454576795.285848 ETHIOPIA | 1994 | 457922699.672524 ETHIOPIA | 1993 | 457622645.355408 ETHIOPIA | 1992 | 461936672.217804 FRANCE | 1998 | 276102784.255512 FRANCE | 1997 | 463013066.201097 FRANCE | 1996 | 464166830.409473 FRANCE | 1995 | 465336879.804125 FRANCE | 1994 | 464221623.918734 FRANCE | 1993 | 467430307.465055 FRANCE | 1992 | 455995923.040874 GERMANY | 1998 | 281151504.279196 GERMANY | 1997 | 481124971.921796 GERMANY | 1996 | 480702869.307343 GERMANY | 1995 | 483118436.433384 GERMANY | 1994 | 484361978.488519 GERMANY | 1993 | 478575179.039685 GERMANY | 1992 | 480630468.269076 INDIA | 1998 | 275481687.070531 INDIA | 1997 | 485115134.357681 INDIA | 1996 | 484276231.242079 INDIA | 1995 | 475252086.405074 INDIA | 1994 | 483824108.125708 INDIA | 1993 | 479473493.183924 INDIA | 1992 | 490734042.575375 INDONESIA | 1998 | 276092315.632132 INDONESIA | 1997 | 462342374.69568 INDONESIA | 1996 | 461998128.621469 INDONESIA | 1995 | 460689739.607117 INDONESIA | 1994 | 457136822.970063 INDONESIA | 1993 | 455651916.098622 INDONESIA | 1992 | 466209643.669879 IRAN | 1998 | 276832972.669383 IRAN | 1997 | 476419096.31303 IRAN | 1996 | 472121339.349454 IRAN | 1995 | 470920788.759372 IRAN | 1994 | 468705498.275444 IRAN | 1993 | 472154548.928636 IRAN | 1992 | 473933264.62231 IRAQ | 1998 | 280695100.387187 IRAQ | 1997 | 475464159.40319 IRAQ | 1996 | 471732950.537905 IRAQ | 1995 | 477609033.831845 IRAQ | 1994 | 470540296.662718 IRAQ | 1993 | 465964542.779397 IRAQ | 1992 | 480200280.753532 JAPAN | 1998 | 273760419.149707 JAPAN | 1997 | 466979476.649564 JAPAN | 1996 | 467399973.504145 JAPAN | 1995 | 467273941.620629 JAPAN | 1994 | 470021021.673481 JAPAN | 1993 | 467494186.069713 JAPAN | 1992 | 461137124.7926 JORDAN | 1998 | 272890074.559982 JORDAN | 1997 | 462549004.748102 JORDAN | 1996 | 450793077.389026 JORDAN | 1995 | 463073186.84915 JORDAN | 1994 | 461916345.800597 JORDAN | 1993 | 459245981.836802 JORDAN | 1992 | 465818277.927447 KENYA | 1998 | 274206704.315349 KENYA | 1997 | 473877030.935393 KENYA | 1996 | 471614531.405816 KENYA | 1995 | 477819338.349917 KENYA | 1994 | 480383044.02563 KENYA | 1993 | 480328918.506662 KENYA | 1992 | 475046029.260705 MOROCCO | 1998 | 278276985.758058 MOROCCO | 1997 | 468089199.496873 MOROCCO | 1996 | 462798084.540465 MOROCCO | 1995 | 463411787.060505 MOROCCO | 1994 | 471620338.452084 MOROCCO | 1993 | 465333330.357023 MOROCCO | 1992 | 470254901.871897 MOZAMBIQUE | 1998 | 274543755.358116 MOZAMBIQUE | 1997 | 461811664.401212 MOZAMBIQUE | 1996 | 459345182.439591 MOZAMBIQUE | 1995 | 462859985.719578 MOZAMBIQUE | 1994 | 467402947.980914 MOZAMBIQUE | 1993 | 468970301.712531 MOZAMBIQUE | 1992 | 461466589.048357 PERU | 1998 | 279301239.292614 PERU | 1997 | 478191368.948024 PERU | 1996 | 477813230.691094 PERU | 1995 | 475923956.541903 PERU | 1994 | 473797928.079305 PERU | 1993 | 467688993.92382 PERU | 1992 | 474302176.895289 ROMANIA | 1998 | 275318472.772499 ROMANIA | 1997 | 464462815.137189 ROMANIA | 1996 | 468304953.663217 ROMANIA | 1995 | 471313706.060481 ROMANIA | 1994 | 466944882.695279 ROMANIA | 1993 | 459571161.033194 ROMANIA | 1992 | 477093362.121814 RUSSIA | 1998 | 280721782.319072 RUSSIA | 1997 | 470669921.241447 RUSSIA | 1996 | 480663917.750789 RUSSIA | 1995 | 476859076.35343 RUSSIA | 1994 | 481396854.062673 RUSSIA | 1993 | 469999592.545785 RUSSIA | 1992 | 476304346.59373 SAUDI ARABIA | 1998 | 275547315.099323 SAUDI ARABIA | 1997 | 478311515.436645 SAUDI ARABIA | 1996 | 475673557.844909 SAUDI ARABIA | 1995 | 480096417.380973 SAUDI ARABIA | 1994 | 465996690.094745 SAUDI ARABIA | 1993 | 474610603.613052 SAUDI ARABIA | 1992 | 479890366.130639 UNITED KINGDOM | 1998 | 269854960.903296 UNITED KINGDOM | 1997 | 458334196.586815 UNITED KINGDOM | 1996 | 467850653.792726 UNITED KINGDOM | 1995 | 465318864.303415 UNITED KINGDOM | 1994 | 459318479.939354 UNITED KINGDOM | 1993 | 463280878.692054 UNITED KINGDOM | 1992 | 472804103.19434 UNITED STATES | 1998 | 279950412.825472 UNITED STATES | 1997 | 480057022.957962 UNITED STATES | 1996 | 474270773.299063 UNITED STATES | 1995 | 472412860.133364 UNITED STATES | 1994 | 467891236.054624 UNITED STATES | 1993 | 472807355.030963 UNITED STATES | 1992 | 469195920.319353 VIETNAM | 1998 | 271864585.365201 VIETNAM | 1997 | 472435611.982288 VIETNAM | 1996 | 469217535.356405 VIETNAM | 1995 | 473228994.821494 VIETNAM | 1994 | 468757432.746375 VIETNAM | 1993 | 479940070.387175 VIETNAM | 1992 | 476353490.537695 (175 rows) COMMIT; COMMIT