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 '%thistle%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267204861.213389 ALGERIA | 1997 | 463732070.519706 ALGERIA | 1996 | 455730003.860687 ALGERIA | 1995 | 457159919.789759 ALGERIA | 1994 | 455276519.499057 ALGERIA | 1993 | 452403538.216537 ALGERIA | 1992 | 456750615.96629 ARGENTINA | 1998 | 274143313.030083 ARGENTINA | 1997 | 475315286.089124 ARGENTINA | 1996 | 467680377.174678 ARGENTINA | 1995 | 467071027.516991 ARGENTINA | 1994 | 469883558.362731 ARGENTINA | 1993 | 475056171.098945 ARGENTINA | 1992 | 469273758.937206 BRAZIL | 1998 | 269861022.43355 BRAZIL | 1997 | 462286533.85327 BRAZIL | 1996 | 459221281.716997 BRAZIL | 1995 | 457842485.57561 BRAZIL | 1994 | 457571299.629958 BRAZIL | 1993 | 460816613.495264 BRAZIL | 1992 | 466875229.333975 CANADA | 1998 | 276761153.910458 CANADA | 1997 | 477196492.583424 CANADA | 1996 | 479132777.269188 CANADA | 1995 | 472915862.285428 CANADA | 1994 | 483277419.397569 CANADA | 1993 | 479990831.506988 CANADA | 1992 | 481525631.266108 CHINA | 1998 | 270404938.595625 CHINA | 1997 | 457863890.829214 CHINA | 1996 | 459699853.742323 CHINA | 1995 | 457712720.913592 CHINA | 1994 | 459536540.515625 CHINA | 1993 | 463196019.483586 CHINA | 1992 | 464470291.341665 EGYPT | 1998 | 268915402.806667 EGYPT | 1997 | 464399814.052034 EGYPT | 1996 | 459058079.793085 EGYPT | 1995 | 467962255.640832 EGYPT | 1994 | 459732558.015333 EGYPT | 1993 | 460696546.961307 EGYPT | 1992 | 463669789.428237 ETHIOPIA | 1998 | 266535143.286308 ETHIOPIA | 1997 | 458998284.823608 ETHIOPIA | 1996 | 455203566.560816 ETHIOPIA | 1995 | 458871396.944258 ETHIOPIA | 1994 | 460379783.886731 ETHIOPIA | 1993 | 453506387.561839 ETHIOPIA | 1992 | 456799903.135907 FRANCE | 1998 | 271825407.832295 FRANCE | 1997 | 458034945.190642 FRANCE | 1996 | 461256462.54138 FRANCE | 1995 | 454206183.176504 FRANCE | 1994 | 453781368.120414 FRANCE | 1993 | 459409487.151036 FRANCE | 1992 | 462802428.959731 GERMANY | 1998 | 270986795.384003 GERMANY | 1997 | 465247002.838169 GERMANY | 1996 | 464636129.963108 GERMANY | 1995 | 465776316.713553 GERMANY | 1994 | 468072270.717737 GERMANY | 1993 | 470565568.499198 GERMANY | 1992 | 467117912.753905 INDIA | 1998 | 274700438.476858 INDIA | 1997 | 474937682.013415 INDIA | 1996 | 474932753.602801 INDIA | 1995 | 478066897.464414 INDIA | 1994 | 474753684.272428 INDIA | 1993 | 474773946.430683 INDIA | 1992 | 470535176.203952 INDONESIA | 1998 | 272268735.932294 INDONESIA | 1997 | 449752594.843957 INDONESIA | 1996 | 455449256.678638 INDONESIA | 1995 | 455029586.373001 INDONESIA | 1994 | 455275146.082957 INDONESIA | 1993 | 461941606.804596 INDONESIA | 1992 | 456928366.712783 IRAN | 1998 | 264587102.626724 IRAN | 1997 | 469656263.382999 IRAN | 1996 | 461942798.055047 IRAN | 1995 | 462191229.82538 IRAN | 1994 | 459103385.155981 IRAN | 1993 | 460051628.360713 IRAN | 1992 | 465125411.399853 IRAQ | 1998 | 272354474.755326 IRAQ | 1997 | 466593898.315597 IRAQ | 1996 | 471482092.610108 IRAQ | 1995 | 464112061.370486 IRAQ | 1994 | 467006133.466764 IRAQ | 1993 | 462225071.229293 IRAQ | 1992 | 465101230.534054 JAPAN | 1998 | 270098610.765292 JAPAN | 1997 | 451317012.273019 JAPAN | 1996 | 456139324.686917 JAPAN | 1995 | 454956923.449342 JAPAN | 1994 | 452767385.249897 JAPAN | 1993 | 464091438.259626 JAPAN | 1992 | 451634522.852967 JORDAN | 1998 | 268172632.400227 JORDAN | 1997 | 457024380.751278 JORDAN | 1996 | 458993799.32413 JORDAN | 1995 | 447022288.291384 JORDAN | 1994 | 455904796.65175 JORDAN | 1993 | 453671143.898764 JORDAN | 1992 | 457397011.270606 KENYA | 1998 | 270558726.142027 KENYA | 1997 | 464932638.473221 KENYA | 1996 | 473390920.661221 KENYA | 1995 | 466976207.662644 KENYA | 1994 | 466226283.532899 KENYA | 1993 | 474932662.432077 KENYA | 1992 | 472007641.102577 MOROCCO | 1998 | 266829811.0035 MOROCCO | 1997 | 463061380.379937 MOROCCO | 1996 | 463369905.179937 MOROCCO | 1995 | 458530598.655398 MOROCCO | 1994 | 458618937.966196 MOROCCO | 1993 | 465470063.353402 MOROCCO | 1992 | 465659517.915674 MOZAMBIQUE | 1998 | 269298696.937962 MOZAMBIQUE | 1997 | 449736768.131637 MOZAMBIQUE | 1996 | 454888857.39032 MOZAMBIQUE | 1995 | 452049240.011491 MOZAMBIQUE | 1994 | 457990223.655425 MOZAMBIQUE | 1993 | 455469469.020893 MOZAMBIQUE | 1992 | 448044937.133178 PERU | 1998 | 269698801.72313 PERU | 1997 | 464331528.212302 PERU | 1996 | 467734970.178674 PERU | 1995 | 456348321.368113 PERU | 1994 | 467059449.497631 PERU | 1993 | 465066806.290514 PERU | 1992 | 460724559.633433 ROMANIA | 1998 | 268333415.789396 ROMANIA | 1997 | 459239715.312607 ROMANIA | 1996 | 461085881.462036 ROMANIA | 1995 | 456626379.97664 ROMANIA | 1994 | 463565871.562108 ROMANIA | 1993 | 460021203.630662 ROMANIA | 1992 | 465116380.585908 RUSSIA | 1998 | 280212306.34112 RUSSIA | 1997 | 467384813.211531 RUSSIA | 1996 | 466817542.972238 RUSSIA | 1995 | 472048954.768046 RUSSIA | 1994 | 470184915.184273 RUSSIA | 1993 | 476580708.735711 RUSSIA | 1992 | 466406647.954062 SAUDI ARABIA | 1998 | 270758185.404528 SAUDI ARABIA | 1997 | 457143876.575855 SAUDI ARABIA | 1996 | 465324480.622563 SAUDI ARABIA | 1995 | 467783104.145599 SAUDI ARABIA | 1994 | 463609647.567646 SAUDI ARABIA | 1993 | 463384867.159954 SAUDI ARABIA | 1992 | 467891483.828544 UNITED KINGDOM | 1998 | 266838341.928674 UNITED KINGDOM | 1997 | 460716583.760408 UNITED KINGDOM | 1996 | 458242297.115165 UNITED KINGDOM | 1995 | 461935297.933128 UNITED KINGDOM | 1994 | 462354886.620611 UNITED KINGDOM | 1993 | 467264774.914719 UNITED KINGDOM | 1992 | 457646417.080574 UNITED STATES | 1998 | 274103001.690704 UNITED STATES | 1997 | 468178099.250322 UNITED STATES | 1996 | 477833392.435309 UNITED STATES | 1995 | 465773722.322775 UNITED STATES | 1994 | 475338488.749855 UNITED STATES | 1993 | 469820638.236165 UNITED STATES | 1992 | 472414511.139493 VIETNAM | 1998 | 271401060.101017 VIETNAM | 1997 | 465528486.179359 VIETNAM | 1996 | 461324321.799875 VIETNAM | 1995 | 459609673.189507 VIETNAM | 1994 | 465560293.211278 VIETNAM | 1993 | 467607349.667155 VIETNAM | 1992 | 461880391.676887 (175 rows) COMMIT; COMMIT