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 '%cream%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264346940.328146 ALGERIA | 1997 | 452001405.668619 ALGERIA | 1996 | 454458693.814371 ALGERIA | 1995 | 455068218.000445 ALGERIA | 1994 | 455038653.056709 ALGERIA | 1993 | 455170522.761712 ALGERIA | 1992 | 456164314.171078 ARGENTINA | 1998 | 268138830.969833 ARGENTINA | 1997 | 467607283.001962 ARGENTINA | 1996 | 468212389.025649 ARGENTINA | 1995 | 467289896.967948 ARGENTINA | 1994 | 462385523.601196 ARGENTINA | 1993 | 464806121.275316 ARGENTINA | 1992 | 466226831.136779 BRAZIL | 1998 | 269232429.236031 BRAZIL | 1997 | 475248614.594589 BRAZIL | 1996 | 469907834.584381 BRAZIL | 1995 | 466634400.877677 BRAZIL | 1994 | 469892291.890936 BRAZIL | 1993 | 471516008.12985 BRAZIL | 1992 | 469845159.182492 CANADA | 1998 | 279594773.787984 CANADA | 1997 | 477602552.991625 CANADA | 1996 | 488262155.232441 CANADA | 1995 | 482248752.949145 CANADA | 1994 | 484912401.350286 CANADA | 1993 | 472073686.619234 CANADA | 1992 | 483606960.918604 CHINA | 1998 | 274732838.010694 CHINA | 1997 | 463830324.693248 CHINA | 1996 | 466963008.411268 CHINA | 1995 | 462767430.280077 CHINA | 1994 | 466651494.30338 CHINA | 1993 | 468427926.029559 CHINA | 1992 | 461258601.635808 EGYPT | 1998 | 270367320.117107 EGYPT | 1997 | 460001685.950592 EGYPT | 1996 | 454597940.587969 EGYPT | 1995 | 467736195.268364 EGYPT | 1994 | 459167685.657844 EGYPT | 1993 | 459738149.777656 EGYPT | 1992 | 455765410.314993 ETHIOPIA | 1998 | 273730986.762098 ETHIOPIA | 1997 | 448263186.363951 ETHIOPIA | 1996 | 455763858.440021 ETHIOPIA | 1995 | 467400776.11083 ETHIOPIA | 1994 | 454480041.692465 ETHIOPIA | 1993 | 459819937.684107 ETHIOPIA | 1992 | 467797380.000809 FRANCE | 1998 | 271441601.675347 FRANCE | 1997 | 467550288.994892 FRANCE | 1996 | 473590156.141149 FRANCE | 1995 | 461116136.215455 FRANCE | 1994 | 469276780.856484 FRANCE | 1993 | 464454557.630423 FRANCE | 1992 | 464415688.208001 GERMANY | 1998 | 275187350.008842 GERMANY | 1997 | 465482106.911088 GERMANY | 1996 | 473374980.082778 GERMANY | 1995 | 471261004.077175 GERMANY | 1994 | 470299401.501648 GERMANY | 1993 | 467823098.620335 GERMANY | 1992 | 464450823.931149 INDIA | 1998 | 272555540.273724 INDIA | 1997 | 477201960.26615 INDIA | 1996 | 474126746.654672 INDIA | 1995 | 471194969.082575 INDIA | 1994 | 472972236.045273 INDIA | 1993 | 471318552.942693 INDIA | 1992 | 474768689.758611 INDONESIA | 1998 | 272750139.16395 INDONESIA | 1997 | 462557825.926454 INDONESIA | 1996 | 456900794.940934 INDONESIA | 1995 | 453590465.067392 INDONESIA | 1994 | 452789961.8029 INDONESIA | 1993 | 458273759.707114 INDONESIA | 1992 | 455716892.78424 IRAN | 1998 | 267201204.838884 IRAN | 1997 | 468460803.709974 IRAN | 1996 | 462556004.658315 IRAN | 1995 | 464394824.35824 IRAN | 1994 | 461308802.349023 IRAN | 1993 | 472896898.737483 IRAN | 1992 | 466568212.115982 IRAQ | 1998 | 277241212.698467 IRAQ | 1997 | 468560789.227078 IRAQ | 1996 | 481548386.375691 IRAQ | 1995 | 474580717.427145 IRAQ | 1994 | 478238612.994169 IRAQ | 1993 | 469460265.881381 IRAQ | 1992 | 476168038.313664 JAPAN | 1998 | 268639226.360286 JAPAN | 1997 | 457924997.399506 JAPAN | 1996 | 459330905.78287 JAPAN | 1995 | 455895479.271666 JAPAN | 1994 | 459523352.876816 JAPAN | 1993 | 448208082.226047 JAPAN | 1992 | 449131317.790012 JORDAN | 1998 | 270049795.415211 JORDAN | 1997 | 457889929.128058 JORDAN | 1996 | 458167563.795925 JORDAN | 1995 | 455429649.824947 JORDAN | 1994 | 458768194.891984 JORDAN | 1993 | 464424087.430726 JORDAN | 1992 | 460962838.509899 KENYA | 1998 | 270396387.394031 KENYA | 1997 | 480243978.960479 KENYA | 1996 | 479137207.198865 KENYA | 1995 | 473752616.148556 KENYA | 1994 | 477564773.992517 KENYA | 1993 | 471839186.764453 KENYA | 1992 | 474910915.445347 MOROCCO | 1998 | 277336713.519184 MOROCCO | 1997 | 460625162.941677 MOROCCO | 1996 | 472300121.774968 MOROCCO | 1995 | 467772123.778463 MOROCCO | 1994 | 470532624.108445 MOROCCO | 1993 | 472201023.64774 MOROCCO | 1992 | 471051635.287431 MOZAMBIQUE | 1998 | 270797912.626587 MOZAMBIQUE | 1997 | 461601786.780724 MOZAMBIQUE | 1996 | 462170575.580669 MOZAMBIQUE | 1995 | 454213259.607 MOZAMBIQUE | 1994 | 456783288.539026 MOZAMBIQUE | 1993 | 457996269.745155 MOZAMBIQUE | 1992 | 454185549.6975 PERU | 1998 | 272396756.209463 PERU | 1997 | 467620242.880266 PERU | 1996 | 470692015.769716 PERU | 1995 | 462733253.470132 PERU | 1994 | 459405686.183904 PERU | 1993 | 464051301.343794 PERU | 1992 | 466846992.100382 ROMANIA | 1998 | 283647279.546439 ROMANIA | 1997 | 470946155.717072 ROMANIA | 1996 | 474406338.114553 ROMANIA | 1995 | 461743038.389235 ROMANIA | 1994 | 468075584.707525 ROMANIA | 1993 | 470104177.365597 ROMANIA | 1992 | 466737572.357666 RUSSIA | 1998 | 277023453.549383 RUSSIA | 1997 | 473176921.140602 RUSSIA | 1996 | 469513550.62955 RUSSIA | 1995 | 469108936.105506 RUSSIA | 1994 | 464011336.10682 RUSSIA | 1993 | 470422097.288404 RUSSIA | 1992 | 469705535.135849 SAUDI ARABIA | 1998 | 264085181.881867 SAUDI ARABIA | 1997 | 460615811.296986 SAUDI ARABIA | 1996 | 453205519.213525 SAUDI ARABIA | 1995 | 459445527.082136 SAUDI ARABIA | 1994 | 457850087.381246 SAUDI ARABIA | 1993 | 460671632.607846 SAUDI ARABIA | 1992 | 463091146.064192 UNITED KINGDOM | 1998 | 273732470.904342 UNITED KINGDOM | 1997 | 467687177.080148 UNITED KINGDOM | 1996 | 468860198.72077 UNITED KINGDOM | 1995 | 466345880.283473 UNITED KINGDOM | 1994 | 470223380.798461 UNITED KINGDOM | 1993 | 469962239.317028 UNITED KINGDOM | 1992 | 461035641.028204 UNITED STATES | 1998 | 275264444.282158 UNITED STATES | 1997 | 474460329.963811 UNITED STATES | 1996 | 474145977.648926 UNITED STATES | 1995 | 474535701.354196 UNITED STATES | 1994 | 481387735.021039 UNITED STATES | 1993 | 474593485.134723 UNITED STATES | 1992 | 473981530.023673 VIETNAM | 1998 | 273058817.727023 VIETNAM | 1997 | 464628798.326451 VIETNAM | 1996 | 470593226.331255 VIETNAM | 1995 | 458431369.166137 VIETNAM | 1994 | 460574966.752585 VIETNAM | 1993 | 470542498.90993 VIETNAM | 1992 | 461980408.652573 (175 rows) COMMIT; COMMIT