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 | 270975621.751794 ALGERIA | 1997 | 453795055.160563 ALGERIA | 1996 | 467938121.489595 ALGERIA | 1995 | 456518101.86589 ALGERIA | 1994 | 461765343.049523 ALGERIA | 1993 | 463233033.442262 ALGERIA | 1992 | 458190710.511931 ARGENTINA | 1998 | 274395758.099127 ARGENTINA | 1997 | 466294681.911503 ARGENTINA | 1996 | 471790604.220168 ARGENTINA | 1995 | 470081218.549328 ARGENTINA | 1994 | 474177070.325521 ARGENTINA | 1993 | 472313660.195573 ARGENTINA | 1992 | 468107270.275874 BRAZIL | 1998 | 274229750.967199 BRAZIL | 1997 | 460701741.731809 BRAZIL | 1996 | 464413389.140866 BRAZIL | 1995 | 466685821.648095 BRAZIL | 1994 | 466029712.199083 BRAZIL | 1993 | 473982011.139532 BRAZIL | 1992 | 472379741.731939 CANADA | 1998 | 274397308.932317 CANADA | 1997 | 475197408.004885 CANADA | 1996 | 484949763.733785 CANADA | 1995 | 469162240.975031 CANADA | 1994 | 477997941.581228 CANADA | 1993 | 477358689.056173 CANADA | 1992 | 477192618.238116 CHINA | 1998 | 279874209.067734 CHINA | 1997 | 468136534.43307 CHINA | 1996 | 472952257.733002 CHINA | 1995 | 472777878.81568 CHINA | 1994 | 474734346.71344 CHINA | 1993 | 464442493.575936 CHINA | 1992 | 467899398.162409 EGYPT | 1998 | 271515444.576002 EGYPT | 1997 | 465717827.279549 EGYPT | 1996 | 477251959.013744 EGYPT | 1995 | 461741677.505916 EGYPT | 1994 | 466559862.141499 EGYPT | 1993 | 473373625.932506 EGYPT | 1992 | 466229247.291885 ETHIOPIA | 1998 | 276238638.347966 ETHIOPIA | 1997 | 455362466.030238 ETHIOPIA | 1996 | 462502019.646523 ETHIOPIA | 1995 | 461178951.92202 ETHIOPIA | 1994 | 457989849.093925 ETHIOPIA | 1993 | 457647653.536933 ETHIOPIA | 1992 | 459420428.96939 FRANCE | 1998 | 268695108.829335 FRANCE | 1997 | 465591759.937483 FRANCE | 1996 | 474383580.604165 FRANCE | 1995 | 467721464.73776 FRANCE | 1994 | 468256988.118825 FRANCE | 1993 | 466426168.726087 FRANCE | 1992 | 468277680.817396 GERMANY | 1998 | 271562604.021359 GERMANY | 1997 | 469789354.436318 GERMANY | 1996 | 465935545.106655 GERMANY | 1995 | 465389673.259953 GERMANY | 1994 | 462095269.244126 GERMANY | 1993 | 472584598.831473 GERMANY | 1992 | 462936434.287477 INDIA | 1998 | 277939778.372804 INDIA | 1997 | 472441112.587414 INDIA | 1996 | 480570216.275646 INDIA | 1995 | 480153872.555418 INDIA | 1994 | 470542837.728373 INDIA | 1993 | 478793775.645525 INDIA | 1992 | 474913556.082353 INDONESIA | 1998 | 276000128.693328 INDONESIA | 1997 | 467700679.456578 INDONESIA | 1996 | 463874990.465404 INDONESIA | 1995 | 467736002.204807 INDONESIA | 1994 | 471144990.352163 INDONESIA | 1993 | 463068964.47855 INDONESIA | 1992 | 470984214.852642 IRAN | 1998 | 274453891.781702 IRAN | 1997 | 472509746.108742 IRAN | 1996 | 465507888.701682 IRAN | 1995 | 462884307.393224 IRAN | 1994 | 465572196.905425 IRAN | 1993 | 470188542.418968 IRAN | 1992 | 473966314.417319 IRAQ | 1998 | 277838941.749063 IRAQ | 1997 | 470523358.402676 IRAQ | 1996 | 473149566.744241 IRAQ | 1995 | 466939586.323274 IRAQ | 1994 | 477266086.965929 IRAQ | 1993 | 475552914.263132 IRAQ | 1992 | 469265061.515956 JAPAN | 1998 | 270639531.003876 JAPAN | 1997 | 467920999.785124 JAPAN | 1996 | 458745729.701467 JAPAN | 1995 | 460333353.090747 JAPAN | 1994 | 454876331.110004 JAPAN | 1993 | 464728845.526221 JAPAN | 1992 | 461222228.95973 JORDAN | 1998 | 275866678.596951 JORDAN | 1997 | 461536949.388052 JORDAN | 1996 | 459534361.391652 JORDAN | 1995 | 462288656.737355 JORDAN | 1994 | 455418526.132945 JORDAN | 1993 | 463417367.808374 JORDAN | 1992 | 454878572.757192 KENYA | 1998 | 275894535.294571 KENYA | 1997 | 467815941.395746 KENYA | 1996 | 468762763.033914 KENYA | 1995 | 472720348.348228 KENYA | 1994 | 471042787.893523 KENYA | 1993 | 469304031.334793 KENYA | 1992 | 473149541.011786 MOROCCO | 1998 | 266342094.798193 MOROCCO | 1997 | 455787319.017357 MOROCCO | 1996 | 457997068.822883 MOROCCO | 1995 | 459220268.026384 MOROCCO | 1994 | 468821369.97495 MOROCCO | 1993 | 466495694.535741 MOROCCO | 1992 | 458495973.311288 MOZAMBIQUE | 1998 | 268981369.310358 MOZAMBIQUE | 1997 | 448549470.914884 MOZAMBIQUE | 1996 | 457574960.91035 MOZAMBIQUE | 1995 | 457983808.370374 MOZAMBIQUE | 1994 | 459189989.788054 MOZAMBIQUE | 1993 | 447157936.460714 MOZAMBIQUE | 1992 | 456999533.282533 PERU | 1998 | 276395589.080959 PERU | 1997 | 471717015.406285 PERU | 1996 | 473991036.999921 PERU | 1995 | 475136785.516612 PERU | 1994 | 470789722.629661 PERU | 1993 | 473033447.138692 PERU | 1992 | 471071474.644703 ROMANIA | 1998 | 276933671.917363 ROMANIA | 1997 | 473085655.625248 ROMANIA | 1996 | 471117014.895076 ROMANIA | 1995 | 471529293.362523 ROMANIA | 1994 | 468591068.320231 ROMANIA | 1993 | 470211715.024454 ROMANIA | 1992 | 473602617.236832 RUSSIA | 1998 | 280403803.317807 RUSSIA | 1997 | 471445237.240311 RUSSIA | 1996 | 472406320.228837 RUSSIA | 1995 | 472607297.115377 RUSSIA | 1994 | 474431486.776725 RUSSIA | 1993 | 472479555.961534 RUSSIA | 1992 | 468611963.506702 SAUDI ARABIA | 1998 | 279691742.136489 SAUDI ARABIA | 1997 | 464408327.859479 SAUDI ARABIA | 1996 | 471927093.289403 SAUDI ARABIA | 1995 | 465165087.162346 SAUDI ARABIA | 1994 | 466514730.531095 SAUDI ARABIA | 1993 | 466927603.203652 SAUDI ARABIA | 1992 | 472008468.059752 UNITED KINGDOM | 1998 | 273763263.775625 UNITED KINGDOM | 1997 | 460117296.792613 UNITED KINGDOM | 1996 | 463173088.24162 UNITED KINGDOM | 1995 | 468997609.241343 UNITED KINGDOM | 1994 | 458681514.455981 UNITED KINGDOM | 1993 | 470834332.831012 UNITED KINGDOM | 1992 | 464861665.397933 UNITED STATES | 1998 | 278544882.161533 UNITED STATES | 1997 | 464551044.536264 UNITED STATES | 1996 | 472868303.766618 UNITED STATES | 1995 | 465911058.400983 UNITED STATES | 1994 | 482314219.342297 UNITED STATES | 1993 | 475962736.319865 UNITED STATES | 1992 | 474950434.34406 VIETNAM | 1998 | 273423757.190167 VIETNAM | 1997 | 459639001.846287 VIETNAM | 1996 | 470630934.483186 VIETNAM | 1995 | 462940090.690315 VIETNAM | 1994 | 465803870.711197 VIETNAM | 1993 | 465813973.477537 VIETNAM | 1992 | 463156882.852346 (175 rows) COMMIT; COMMIT