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 '%violet%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265815466.523129 ALGERIA | 1997 | 459623536.418842 ALGERIA | 1996 | 449113156.21668 ALGERIA | 1995 | 468751958.866955 ALGERIA | 1994 | 453079540.804063 ALGERIA | 1993 | 456014612.497573 ALGERIA | 1992 | 460916350.432497 ARGENTINA | 1998 | 276921162.440099 ARGENTINA | 1997 | 477984069.015941 ARGENTINA | 1996 | 478623096.413195 ARGENTINA | 1995 | 475295628.389403 ARGENTINA | 1994 | 470417487.174096 ARGENTINA | 1993 | 470707513.725755 ARGENTINA | 1992 | 476210170.481247 BRAZIL | 1998 | 279645008.537878 BRAZIL | 1997 | 479737540.193965 BRAZIL | 1996 | 474927664.057201 BRAZIL | 1995 | 475908499.739845 BRAZIL | 1994 | 481494719.33303 BRAZIL | 1993 | 482672158.653318 BRAZIL | 1992 | 484720972.939202 CANADA | 1998 | 279935061.865501 CANADA | 1997 | 482965496.700811 CANADA | 1996 | 485019870.45788 CANADA | 1995 | 479326493.733166 CANADA | 1994 | 485204744.022285 CANADA | 1993 | 481753167.780477 CANADA | 1992 | 484258350.35897 CHINA | 1998 | 275836989.217683 CHINA | 1997 | 464571057.86943 CHINA | 1996 | 460676830.658736 CHINA | 1995 | 462448664.024484 CHINA | 1994 | 469039444.651152 CHINA | 1993 | 463410687.30947 CHINA | 1992 | 462596015.006268 EGYPT | 1998 | 273059156.514099 EGYPT | 1997 | 465488121.585856 EGYPT | 1996 | 459048135.057042 EGYPT | 1995 | 464893838.254195 EGYPT | 1994 | 466256188.356109 EGYPT | 1993 | 465717858.947243 EGYPT | 1992 | 458780105.420942 ETHIOPIA | 1998 | 270609741.666352 ETHIOPIA | 1997 | 460702341.382682 ETHIOPIA | 1996 | 461041449.872414 ETHIOPIA | 1995 | 465484660.238205 ETHIOPIA | 1994 | 452689608.905816 ETHIOPIA | 1993 | 460866021.238382 ETHIOPIA | 1992 | 469353511.681127 FRANCE | 1998 | 272069041.782327 FRANCE | 1997 | 465669361.734962 FRANCE | 1996 | 479055923.851831 FRANCE | 1995 | 473588538.479995 FRANCE | 1994 | 462287792.707749 FRANCE | 1993 | 464319958.792799 FRANCE | 1992 | 458707688.741481 GERMANY | 1998 | 278514724.969567 GERMANY | 1997 | 483075155.46812 GERMANY | 1996 | 477401343.039011 GERMANY | 1995 | 474065918.36955 GERMANY | 1994 | 477844179.595377 GERMANY | 1993 | 475788227.091533 GERMANY | 1992 | 471556734.419243 INDIA | 1998 | 268186212.124273 INDIA | 1997 | 466235119.41708 INDIA | 1996 | 474797882.01225 INDIA | 1995 | 476651725.714634 INDIA | 1994 | 475144468.967239 INDIA | 1993 | 477894698.433692 INDIA | 1992 | 477623169.049646 INDONESIA | 1998 | 266927533.76501 INDONESIA | 1997 | 460868229.063837 INDONESIA | 1996 | 466186422.309575 INDONESIA | 1995 | 469249091.04158 INDONESIA | 1994 | 464607677.437638 INDONESIA | 1993 | 468998117.044479 INDONESIA | 1992 | 462885484.672752 IRAN | 1998 | 268699360.10382 IRAN | 1997 | 457341756.123161 IRAN | 1996 | 458240492.244204 IRAN | 1995 | 462523292.896338 IRAN | 1994 | 459022697.459253 IRAN | 1993 | 461600420.134547 IRAN | 1992 | 465196304.929803 IRAQ | 1998 | 283514931.984077 IRAQ | 1997 | 479142111.513699 IRAQ | 1996 | 476687997.312687 IRAQ | 1995 | 477441241.236558 IRAQ | 1994 | 481727987.753519 IRAQ | 1993 | 483213843.724696 IRAQ | 1992 | 481954728.322267 JAPAN | 1998 | 269892566.373155 JAPAN | 1997 | 471222551.135914 JAPAN | 1996 | 467196094.839291 JAPAN | 1995 | 458863245.835573 JAPAN | 1994 | 471274298.522104 JAPAN | 1993 | 465419425.604161 JAPAN | 1992 | 469518327.248691 JORDAN | 1998 | 270750418.326381 JORDAN | 1997 | 459722690.5848 JORDAN | 1996 | 456079599.495702 JORDAN | 1995 | 460455304.225574 JORDAN | 1994 | 459832644.066531 JORDAN | 1993 | 467816585.233773 JORDAN | 1992 | 461372669.810399 KENYA | 1998 | 279145601.444562 KENYA | 1997 | 474862821.312536 KENYA | 1996 | 479923226.55933 KENYA | 1995 | 472741709.485915 KENYA | 1994 | 478762225.553674 KENYA | 1993 | 482004152.616087 KENYA | 1992 | 479855023.660385 MOROCCO | 1998 | 268012089.497262 MOROCCO | 1997 | 464495780.195777 MOROCCO | 1996 | 468230433.465227 MOROCCO | 1995 | 472830888.73968 MOROCCO | 1994 | 462225500.061883 MOROCCO | 1993 | 464776680.976882 MOROCCO | 1992 | 466020054.916981 MOZAMBIQUE | 1998 | 270532831.668825 MOZAMBIQUE | 1997 | 456077915.747808 MOZAMBIQUE | 1996 | 469553868.718924 MOZAMBIQUE | 1995 | 459792810.091274 MOZAMBIQUE | 1994 | 459583176.651576 MOZAMBIQUE | 1993 | 459529543.391825 MOZAMBIQUE | 1992 | 463115741.007567 PERU | 1998 | 275564842.473816 PERU | 1997 | 473582808.638812 PERU | 1996 | 478139991.158966 PERU | 1995 | 474303285.262042 PERU | 1994 | 472219557.291088 PERU | 1993 | 473127123.98905 PERU | 1992 | 472111943.726936 ROMANIA | 1998 | 276796800.509559 ROMANIA | 1997 | 467858701.905611 ROMANIA | 1996 | 471087596.552761 ROMANIA | 1995 | 464210750.441869 ROMANIA | 1994 | 470544969.371895 ROMANIA | 1993 | 472557352.822886 ROMANIA | 1992 | 477875838.794565 RUSSIA | 1998 | 275610131.098473 RUSSIA | 1997 | 465748872.729429 RUSSIA | 1996 | 471576771.046358 RUSSIA | 1995 | 466213253.594363 RUSSIA | 1994 | 471595541.902054 RUSSIA | 1993 | 476374352.845599 RUSSIA | 1992 | 470802788.112218 SAUDI ARABIA | 1998 | 274556801.67095 SAUDI ARABIA | 1997 | 466281531.55455 SAUDI ARABIA | 1996 | 466598856.548316 SAUDI ARABIA | 1995 | 469230408.158 SAUDI ARABIA | 1994 | 467889447.050831 SAUDI ARABIA | 1993 | 476679294.064579 SAUDI ARABIA | 1992 | 468002371.842827 UNITED KINGDOM | 1998 | 277825450.094326 UNITED KINGDOM | 1997 | 463330634.945656 UNITED KINGDOM | 1996 | 470610876.742925 UNITED KINGDOM | 1995 | 468661885.701405 UNITED KINGDOM | 1994 | 470948430.893829 UNITED KINGDOM | 1993 | 462261175.374767 UNITED KINGDOM | 1992 | 468776332.985936 UNITED STATES | 1998 | 272890615.249838 UNITED STATES | 1997 | 475411222.977813 UNITED STATES | 1996 | 461254325.957998 UNITED STATES | 1995 | 469594424.493218 UNITED STATES | 1994 | 471133818.235524 UNITED STATES | 1993 | 470385292.034268 UNITED STATES | 1992 | 469580947.862683 VIETNAM | 1998 | 271792544.666633 VIETNAM | 1997 | 464917382.167814 VIETNAM | 1996 | 466792713.503209 VIETNAM | 1995 | 465007434.488964 VIETNAM | 1994 | 466463410.096052 VIETNAM | 1993 | 464311946.245529 VIETNAM | 1992 | 467939393.997262 (175 rows) COMMIT; COMMIT