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 '%chartreuse%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 2718927824.78427 ALGERIA | 1997 | 4665256703.20765 ALGERIA | 1996 | 4667281740.99881 ALGERIA | 1995 | 4673304831.72378 ALGERIA | 1994 | 4646330941.16604 ALGERIA | 1993 | 4645782269.33259 ALGERIA | 1992 | 4672947044.77248 ARGENTINA | 1998 | 2725687118.34139 ARGENTINA | 1997 | 4644551001.85234 ARGENTINA | 1996 | 4650886833.27374 ARGENTINA | 1995 | 4636573409.66195 ARGENTINA | 1994 | 4635812966.24048 ARGENTINA | 1993 | 4639778333.28218 ARGENTINA | 1992 | 4641497883.55125 BRAZIL | 1998 | 2744908325.06152 BRAZIL | 1997 | 4696467417.47462 BRAZIL | 1996 | 4685220944.48762 BRAZIL | 1995 | 4665932439.55356 BRAZIL | 1994 | 4658790289.80817 BRAZIL | 1993 | 4676975753.0654 BRAZIL | 1992 | 4691089310.1191 CANADA | 1998 | 2742239595.41813 CANADA | 1997 | 4686253993.8335 CANADA | 1996 | 4690841879.8962 CANADA | 1995 | 4663521650.22067 CANADA | 1994 | 4668777949.78842 CANADA | 1993 | 4660072352.56752 CANADA | 1992 | 4667201587.86101 CHINA | 1998 | 2735608438.73061 CHINA | 1997 | 4658681727.9697 CHINA | 1996 | 4669604797.86275 CHINA | 1995 | 4677893579.01695 CHINA | 1994 | 4675608979.67775 CHINA | 1993 | 4668130635.89506 CHINA | 1992 | 4677935599.93615 EGYPT | 1998 | 2731376733.09776 EGYPT | 1997 | 4662419971.80952 EGYPT | 1996 | 4683100297.47976 EGYPT | 1995 | 4679879056.43374 EGYPT | 1994 | 4671451041.95468 EGYPT | 1993 | 4677523007.75654 EGYPT | 1992 | 4669843942.07907 ETHIOPIA | 1998 | 2766896793.598 ETHIOPIA | 1997 | 4708478519.93592 ETHIOPIA | 1996 | 4719214160.23396 ETHIOPIA | 1995 | 4695955973.3406 ETHIOPIA | 1994 | 4706017812.64073 ETHIOPIA | 1993 | 4689461077.56917 ETHIOPIA | 1992 | 4685049821.01683 FRANCE | 1998 | 2757674421.84344 FRANCE | 1997 | 4718121635.84586 FRANCE | 1996 | 4701215181.32415 FRANCE | 1995 | 4682361251.32791 FRANCE | 1994 | 4702269266.09673 FRANCE | 1993 | 4714306019.25655 FRANCE | 1992 | 4721378109.26184 GERMANY | 1998 | 2755446004.62152 GERMANY | 1997 | 4659352908.59009 GERMANY | 1996 | 4700781395.69339 GERMANY | 1995 | 4675819118.51038 GERMANY | 1994 | 4652776741.9317 GERMANY | 1993 | 4683914831.77561 GERMANY | 1992 | 4700007410.37812 INDIA | 1998 | 2751028366.36109 INDIA | 1997 | 4676363520.798 INDIA | 1996 | 4671135429.36089 INDIA | 1995 | 4675050901.34323 INDIA | 1994 | 4689805745.00163 INDIA | 1993 | 4666554524.02656 INDIA | 1992 | 4680796987.35424 INDONESIA | 1998 | 2713929785.85395 INDONESIA | 1997 | 4604497656.10283 INDONESIA | 1996 | 4631038839.80939 INDONESIA | 1995 | 4630834727.53114 INDONESIA | 1994 | 4613221759.08281 INDONESIA | 1993 | 4630895553.54378 INDONESIA | 1992 | 4638928603.10421 IRAN | 1998 | 2716277655.27674 IRAN | 1997 | 4601140310.94038 IRAN | 1996 | 4631793194.1278 IRAN | 1995 | 4620757675.49065 IRAN | 1994 | 4612372768.28215 IRAN | 1993 | 4602539226.22019 IRAN | 1992 | 4635202988.91764 IRAQ | 1998 | 2702123370.61591 IRAQ | 1997 | 4625926928.34383 IRAQ | 1996 | 4628760683.75697 IRAQ | 1995 | 4627590266.12165 IRAQ | 1994 | 4631371508.99375 IRAQ | 1993 | 4643936813.37259 IRAQ | 1992 | 4627023429.73275 JAPAN | 1998 | 2719177925.73035 JAPAN | 1997 | 4628786060.0042 JAPAN | 1996 | 4657960393.371 JAPAN | 1995 | 4627317470.35328 JAPAN | 1994 | 4648498603.50467 JAPAN | 1993 | 4629261538.22373 JAPAN | 1992 | 4625497100.69544 JORDAN | 1998 | 2729261424.69399 JORDAN | 1997 | 4644933555.92688 JORDAN | 1996 | 4673563839.5118 JORDAN | 1995 | 4618198598.13755 JORDAN | 1994 | 4628646944.26616 JORDAN | 1993 | 4636406977.58228 JORDAN | 1992 | 4659995633.89841 KENYA | 1998 | 2719054309.96311 KENYA | 1997 | 4605635189.74521 KENYA | 1996 | 4621467489.67947 KENYA | 1995 | 4605620049.71424 KENYA | 1994 | 4600255711.14447 KENYA | 1993 | 4609252180.27225 KENYA | 1992 | 4617004117.94032 MOROCCO | 1998 | 2719662702.96545 MOROCCO | 1997 | 4674010249.00637 MOROCCO | 1996 | 4691479055.25445 MOROCCO | 1995 | 4638607245.34436 MOROCCO | 1994 | 4666859099.44654 MOROCCO | 1993 | 4686583612.93397 MOROCCO | 1992 | 4673672661.47879 MOZAMBIQUE | 1998 | 2714941483.89848 MOZAMBIQUE | 1997 | 4635092451.48502 MOZAMBIQUE | 1996 | 4684954523.59669 MOZAMBIQUE | 1995 | 4672832389.63704 MOZAMBIQUE | 1994 | 4647700678.02022 MOZAMBIQUE | 1993 | 4669871452.46603 MOZAMBIQUE | 1992 | 4650766581.23583 PERU | 1998 | 2714611476.71106 PERU | 1997 | 4585223526.46537 PERU | 1996 | 4621136477.78376 PERU | 1995 | 4595814051.20637 PERU | 1994 | 4603856173.35242 PERU | 1993 | 4620850735.44486 PERU | 1992 | 4635864675.02519 ROMANIA | 1998 | 2725762126.52493 ROMANIA | 1997 | 4651536041.5599 ROMANIA | 1996 | 4685101505.74151 ROMANIA | 1995 | 4656727456.64 ROMANIA | 1994 | 4680121058.11799 ROMANIA | 1993 | 4674203097.22994 ROMANIA | 1992 | 4672305752.23389 RUSSIA | 1998 | 2755527060.79044 RUSSIA | 1997 | 4686927356.86053 RUSSIA | 1996 | 4706707222.74384 RUSSIA | 1995 | 4691656937.94006 RUSSIA | 1994 | 4669897337.70688 RUSSIA | 1993 | 4663058924.23512 RUSSIA | 1992 | 4702739087.56012 SAUDI ARABIA | 1998 | 2749379300.27358 SAUDI ARABIA | 1997 | 4680813185.19059 SAUDI ARABIA | 1996 | 4697249093.15698 SAUDI ARABIA | 1995 | 4676590287.16481 SAUDI ARABIA | 1994 | 4689249743.01834 SAUDI ARABIA | 1993 | 4668559095.97097 SAUDI ARABIA | 1992 | 4675394908.39093 UNITED KINGDOM | 1998 | 2734153147.31393 UNITED KINGDOM | 1997 | 4665260273.15774 UNITED KINGDOM | 1996 | 4707601666.92764 UNITED KINGDOM | 1995 | 4685870059.7565 UNITED KINGDOM | 1994 | 4653219126.67433 UNITED KINGDOM | 1993 | 4648630130.38828 UNITED KINGDOM | 1992 | 4678427931.14169 UNITED STATES | 1998 | 2732640636.67379 UNITED STATES | 1997 | 4661471846.2775 UNITED STATES | 1996 | 4715019111.48954 UNITED STATES | 1995 | 4647916827.17211 UNITED STATES | 1994 | 4670731987.23503 UNITED STATES | 1993 | 4654630829.16194 UNITED STATES | 1992 | 4655347118.99991 VIETNAM | 1998 | 2739930070.10669 VIETNAM | 1997 | 4661128900.62465 VIETNAM | 1996 | 4699804878.47369 VIETNAM | 1995 | 4663960991.00798 VIETNAM | 1994 | 4666531697.18933 VIETNAM | 1993 | 4669149927.07625 VIETNAM | 1992 | 4704632348.71758 (175 rows) COMMIT; COMMIT