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 | 270807116.530611 ALGERIA | 1997 | 454921208.797884 ALGERIA | 1996 | 468671897.633825 ALGERIA | 1995 | 456019461.752004 ALGERIA | 1994 | 461249881.40035 ALGERIA | 1993 | 462793892.78055 ALGERIA | 1992 | 458245698.923038 ARGENTINA | 1998 | 274063551.962063 ARGENTINA | 1997 | 466332427.304015 ARGENTINA | 1996 | 472005931.950895 ARGENTINA | 1995 | 470448015.401194 ARGENTINA | 1994 | 473961890.180723 ARGENTINA | 1993 | 472959017.049347 ARGENTINA | 1992 | 468697851.130979 BRAZIL | 1998 | 273724836.410563 BRAZIL | 1997 | 460502666.926014 BRAZIL | 1996 | 464878773.058132 BRAZIL | 1995 | 465282634.933949 BRAZIL | 1994 | 466924151.359268 BRAZIL | 1993 | 474575343.870584 BRAZIL | 1992 | 472181896.121183 CANADA | 1998 | 273573694.069043 CANADA | 1997 | 473400116.494542 CANADA | 1996 | 484592539.374971 CANADA | 1995 | 468928902.212925 CANADA | 1994 | 478888366.263807 CANADA | 1993 | 478238672.582761 CANADA | 1992 | 477833761.913251 CHINA | 1998 | 280473937.595207 CHINA | 1997 | 467457757.25669 CHINA | 1996 | 472860969.27952 CHINA | 1995 | 472692712.620228 CHINA | 1994 | 475054148.383918 CHINA | 1993 | 464664187.790563 CHINA | 1992 | 468250715.540542 EGYPT | 1998 | 270565566.521185 EGYPT | 1997 | 465426046.028515 EGYPT | 1996 | 477557948.073605 EGYPT | 1995 | 462366042.383195 EGYPT | 1994 | 466401298.33449 EGYPT | 1993 | 474289857.176751 EGYPT | 1992 | 465941491.4827 ETHIOPIA | 1998 | 275765998.227736 ETHIOPIA | 1997 | 455470084.646461 ETHIOPIA | 1996 | 462652796.84783 ETHIOPIA | 1995 | 460406324.378817 ETHIOPIA | 1994 | 458062760.321374 ETHIOPIA | 1993 | 458146675.246846 ETHIOPIA | 1992 | 458996788.404228 FRANCE | 1998 | 268342652.50044 FRANCE | 1997 | 466792816.633761 FRANCE | 1996 | 473990597.071024 FRANCE | 1995 | 467324526.266387 FRANCE | 1994 | 467870383.211607 FRANCE | 1993 | 466960420.647768 FRANCE | 1992 | 468014738.188354 GERMANY | 1998 | 271705131.049424 GERMANY | 1997 | 469701653.180636 GERMANY | 1996 | 465417587.805433 GERMANY | 1995 | 465799166.757829 GERMANY | 1994 | 461629984.149305 GERMANY | 1993 | 472336785.59162 GERMANY | 1992 | 463397354.54541 INDIA | 1998 | 278215795.382031 INDIA | 1997 | 472382219.484042 INDIA | 1996 | 480291574.426362 INDIA | 1995 | 480399638.914503 INDIA | 1994 | 470849135.741221 INDIA | 1993 | 479136129.258602 INDIA | 1992 | 475398665.406803 INDONESIA | 1998 | 276128870.604292 INDONESIA | 1997 | 467324659.606579 INDONESIA | 1996 | 464027459.02504 INDONESIA | 1995 | 468162798.656952 INDONESIA | 1994 | 471343379.859157 INDONESIA | 1993 | 464036987.102609 INDONESIA | 1992 | 471416457.215099 IRAN | 1998 | 274496043.383471 IRAN | 1997 | 472421445.136429 IRAN | 1996 | 465652959.052605 IRAN | 1995 | 462436478.165376 IRAN | 1994 | 465409837.858123 IRAN | 1993 | 470808904.4934 IRAN | 1992 | 474714895.151008 IRAQ | 1998 | 277352935.385719 IRAQ | 1997 | 470128090.153582 IRAQ | 1996 | 472608296.101091 IRAQ | 1995 | 465943778.102738 IRAQ | 1994 | 477003195.148851 IRAQ | 1993 | 475502216.095879 IRAQ | 1992 | 468656016.251252 JAPAN | 1998 | 270681620.920887 JAPAN | 1997 | 468096473.379941 JAPAN | 1996 | 458862019.90884 JAPAN | 1995 | 460066229.552144 JAPAN | 1994 | 454762406.301808 JAPAN | 1993 | 465060439.228292 JAPAN | 1992 | 460256923.495375 JORDAN | 1998 | 275704940.992181 JORDAN | 1997 | 460784145.245207 JORDAN | 1996 | 458882012.802971 JORDAN | 1995 | 462405062.423609 JORDAN | 1994 | 455363510.526162 JORDAN | 1993 | 464224408.180103 JORDAN | 1992 | 454175043.587816 KENYA | 1998 | 275794755.369666 KENYA | 1997 | 467490027.478345 KENYA | 1996 | 468723044.299096 KENYA | 1995 | 473125234.592402 KENYA | 1994 | 470379198.201552 KENYA | 1993 | 468942641.660606 KENYA | 1992 | 473084230.853523 MOROCCO | 1998 | 265922194.306579 MOROCCO | 1997 | 455746455.621288 MOROCCO | 1996 | 457602228.415798 MOROCCO | 1995 | 459870145.461104 MOROCCO | 1994 | 469575523.197742 MOROCCO | 1993 | 467158242.588649 MOROCCO | 1992 | 458747235.595292 MOZAMBIQUE | 1998 | 268305075.441012 MOZAMBIQUE | 1997 | 449462060.828124 MOZAMBIQUE | 1996 | 458065571.46323 MOZAMBIQUE | 1995 | 458008086.715717 MOZAMBIQUE | 1994 | 458830549.548028 MOZAMBIQUE | 1993 | 447115676.569311 MOZAMBIQUE | 1992 | 456702342.44948 PERU | 1998 | 276479928.453782 PERU | 1997 | 472212295.048806 PERU | 1996 | 474042148.554415 PERU | 1995 | 474467621.713416 PERU | 1994 | 470304732.755716 PERU | 1993 | 472937395.533349 PERU | 1992 | 471638005.658124 ROMANIA | 1998 | 276499833.493049 ROMANIA | 1997 | 473282742.515127 ROMANIA | 1996 | 471621951.439462 ROMANIA | 1995 | 472114712.816473 ROMANIA | 1994 | 467952430.408929 ROMANIA | 1993 | 469887166.139931 ROMANIA | 1992 | 473033564.712705 RUSSIA | 1998 | 280435318.934318 RUSSIA | 1997 | 470808756.6365 RUSSIA | 1996 | 472970030.735604 RUSSIA | 1995 | 472363378.469431 RUSSIA | 1994 | 474963553.870698 RUSSIA | 1993 | 472198690.323175 RUSSIA | 1992 | 468455443.125129 SAUDI ARABIA | 1998 | 279825731.512881 SAUDI ARABIA | 1997 | 464600811.808152 SAUDI ARABIA | 1996 | 472587881.301307 SAUDI ARABIA | 1995 | 465581893.580572 SAUDI ARABIA | 1994 | 466194735.808036 SAUDI ARABIA | 1993 | 467043493.151363 SAUDI ARABIA | 1992 | 471783329.157969 UNITED KINGDOM | 1998 | 274149929.590536 UNITED KINGDOM | 1997 | 460833424.393413 UNITED KINGDOM | 1996 | 462610779.363441 UNITED KINGDOM | 1995 | 468638820.608715 UNITED KINGDOM | 1994 | 458607977.769102 UNITED KINGDOM | 1993 | 470467752.024944 UNITED KINGDOM | 1992 | 464213246.394182 UNITED STATES | 1998 | 278513726.041338 UNITED STATES | 1997 | 465017030.336338 UNITED STATES | 1996 | 473265870.471204 UNITED STATES | 1995 | 466091869.250976 UNITED STATES | 1994 | 482300536.510995 UNITED STATES | 1993 | 475842620.299009 UNITED STATES | 1992 | 474535651.819312 VIETNAM | 1998 | 273541372.627984 VIETNAM | 1997 | 459962362.594661 VIETNAM | 1996 | 470721888.120803 VIETNAM | 1995 | 462987759.268706 VIETNAM | 1994 | 464906967.674862 VIETNAM | 1993 | 466579880.573718 VIETNAM | 1992 | 462899560.227034 (175 rows) COMMIT; COMMIT