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 | 2743929360.88361 ALGERIA | 1997 | 4701821972.70905 ALGERIA | 1996 | 4695700443.32277 ALGERIA | 1995 | 4696733478.51599 ALGERIA | 1994 | 4684414813.86914 ALGERIA | 1993 | 4673260216.5258 ALGERIA | 1992 | 4690654548.41505 ARGENTINA | 1998 | 2719508662.95335 ARGENTINA | 1997 | 4659189786.84564 ARGENTINA | 1996 | 4675236807.18215 ARGENTINA | 1995 | 4654514745.98029 ARGENTINA | 1994 | 4677230504.65261 ARGENTINA | 1993 | 4663629535.74941 ARGENTINA | 1992 | 4680645013.46434 BRAZIL | 1998 | 2753172369.06623 BRAZIL | 1997 | 4704346551.55925 BRAZIL | 1996 | 4693151945.9428 BRAZIL | 1995 | 4681451828.99172 BRAZIL | 1994 | 4697607295.89968 BRAZIL | 1993 | 4680683928.0639 BRAZIL | 1992 | 4685723201.96437 CANADA | 1998 | 2737652886.76456 CANADA | 1997 | 4671508104.32593 CANADA | 1996 | 4685966934.72396 CANADA | 1995 | 4670859536.6985 CANADA | 1994 | 4669540015.0648 CANADA | 1993 | 4684084134.95951 CANADA | 1992 | 4688808543.6762 CHINA | 1998 | 2731804542.15959 CHINA | 1997 | 4681837199.93346 CHINA | 1996 | 4683183436.40516 CHINA | 1995 | 4690003406.16446 CHINA | 1994 | 4686910056.05037 CHINA | 1993 | 4662262483.74404 CHINA | 1992 | 4703193554.69393 EGYPT | 1998 | 2739157645.0304 EGYPT | 1997 | 4680830801.39478 EGYPT | 1996 | 4704874616.83694 EGYPT | 1995 | 4701969174.52399 EGYPT | 1994 | 4680897712.47965 EGYPT | 1993 | 4696739635.60771 EGYPT | 1992 | 4726713656.60496 ETHIOPIA | 1998 | 2742296970.26721 ETHIOPIA | 1997 | 4712151391.9028 ETHIOPIA | 1996 | 4746484959.57011 ETHIOPIA | 1995 | 4714945063.10888 ETHIOPIA | 1994 | 4719790728.849 ETHIOPIA | 1993 | 4698087185.22447 ETHIOPIA | 1992 | 4718611048.4323 FRANCE | 1998 | 2749028559.28446 FRANCE | 1997 | 4703162152.96748 FRANCE | 1996 | 4708642307.43957 FRANCE | 1995 | 4670789976.23203 FRANCE | 1994 | 4700721167.12002 FRANCE | 1993 | 4682081211.38131 FRANCE | 1992 | 4699704874.21294 GERMANY | 1998 | 2731427859.8007 GERMANY | 1997 | 4673154964.9459 GERMANY | 1996 | 4688091210.90904 GERMANY | 1995 | 4682027118.45635 GERMANY | 1994 | 4651416727.31126 GERMANY | 1993 | 4669612429.20604 GERMANY | 1992 | 4702882748.01622 INDIA | 1998 | 2740938371.94739 INDIA | 1997 | 4696770982.49762 INDIA | 1996 | 4699503402.93361 INDIA | 1995 | 4665258492.64501 INDIA | 1994 | 4662818528.79294 INDIA | 1993 | 4668852778.65374 INDIA | 1992 | 4672266396.14013 INDONESIA | 1998 | 2736816892.64642 INDONESIA | 1997 | 4647788131.48832 INDONESIA | 1996 | 4686465586.347 INDONESIA | 1995 | 4664863897.77077 INDONESIA | 1994 | 4644409448.03954 INDONESIA | 1993 | 4685334890.72613 INDONESIA | 1992 | 4655789557.24652 IRAN | 1998 | 2734884924.8544 IRAN | 1997 | 4662648141.60394 IRAN | 1996 | 4663635027.51254 IRAN | 1995 | 4675465780.47862 IRAN | 1994 | 4662057856.65863 IRAN | 1993 | 4656215277.40885 IRAN | 1992 | 4645598125.91914 IRAQ | 1998 | 2703859747.30137 IRAQ | 1997 | 4652311989.47911 IRAQ | 1996 | 4671985882.03097 IRAQ | 1995 | 4661670450.4347 IRAQ | 1994 | 4639720905.40044 IRAQ | 1993 | 4607319665.44984 IRAQ | 1992 | 4655913288.34724 JAPAN | 1998 | 2712975722.42687 JAPAN | 1997 | 4615407022.7995 JAPAN | 1996 | 4641458731.63532 JAPAN | 1995 | 4625057427.29206 JAPAN | 1994 | 4650058757.16077 JAPAN | 1993 | 4603400602.4434 JAPAN | 1992 | 4649968416.91492 JORDAN | 1998 | 2717002620.66626 JORDAN | 1997 | 4648364553.57184 JORDAN | 1996 | 4665436870.10542 JORDAN | 1995 | 4635348041.96342 JORDAN | 1994 | 4635692408.59803 JORDAN | 1993 | 4633528138.64399 JORDAN | 1992 | 4670478939.54321 KENYA | 1998 | 2717923710.58525 KENYA | 1997 | 4635303340.15129 KENYA | 1996 | 4657491145.29816 KENYA | 1995 | 4664195613.07102 KENYA | 1994 | 4664763857.05407 KENYA | 1993 | 4642103774.15038 KENYA | 1992 | 4650887490.34543 MOROCCO | 1998 | 2745973381.07339 MOROCCO | 1997 | 4672455514.79746 MOROCCO | 1996 | 4680300579.20725 MOROCCO | 1995 | 4678158121.93573 MOROCCO | 1994 | 4693034430.44558 MOROCCO | 1993 | 4639125379.8249 MOROCCO | 1992 | 4696910282.80039 MOZAMBIQUE | 1998 | 2749015423.20086 MOZAMBIQUE | 1997 | 4675970650.36637 MOZAMBIQUE | 1996 | 4700480219.00786 MOZAMBIQUE | 1995 | 4659195460.96985 MOZAMBIQUE | 1994 | 4684104765.4114 MOZAMBIQUE | 1993 | 4681813472.89944 MOZAMBIQUE | 1992 | 4684915853.51505 PERU | 1998 | 2717741304.18873 PERU | 1997 | 4657160620.90433 PERU | 1996 | 4645894819.66434 PERU | 1995 | 4645606606.08464 PERU | 1994 | 4622362128.87843 PERU | 1993 | 4619916919.25335 PERU | 1992 | 4652128522.21436 ROMANIA | 1998 | 2761301976.93872 ROMANIA | 1997 | 4673048984.88074 ROMANIA | 1996 | 4680336493.14957 ROMANIA | 1995 | 4679457526.16655 ROMANIA | 1994 | 4671549739.46931 ROMANIA | 1993 | 4661784656.56232 ROMANIA | 1992 | 4713878595.63169 RUSSIA | 1998 | 2715682185.05543 RUSSIA | 1997 | 4651159466.18339 RUSSIA | 1996 | 4696248045.28492 RUSSIA | 1995 | 4690570021.40891 RUSSIA | 1994 | 4651560420.78919 RUSSIA | 1993 | 4652191640.14214 RUSSIA | 1992 | 4658827628.73703 SAUDI ARABIA | 1998 | 2738953427.70588 SAUDI ARABIA | 1997 | 4699332289.31567 SAUDI ARABIA | 1996 | 4672067881.88492 SAUDI ARABIA | 1995 | 4658634729.15767 SAUDI ARABIA | 1994 | 4664590615.83253 SAUDI ARABIA | 1993 | 4680528960.65883 SAUDI ARABIA | 1992 | 4677666045.336 UNITED KINGDOM | 1998 | 2742812736.63163 UNITED KINGDOM | 1997 | 4720854800.50447 UNITED KINGDOM | 1996 | 4719508118.87504 UNITED KINGDOM | 1995 | 4687034374.46929 UNITED KINGDOM | 1994 | 4697589548.63655 UNITED KINGDOM | 1993 | 4686850637.18306 UNITED KINGDOM | 1992 | 4688189800.89938 UNITED STATES | 1998 | 2733816771.55513 UNITED STATES | 1997 | 4679923229.93139 UNITED STATES | 1996 | 4669108111.48428 UNITED STATES | 1995 | 4651032235.82298 UNITED STATES | 1994 | 4678619692.96888 UNITED STATES | 1993 | 4677978201.42917 UNITED STATES | 1992 | 4685811153.12276 VIETNAM | 1998 | 2738936093.87695 VIETNAM | 1997 | 4708176674.38676 VIETNAM | 1996 | 4704123333.58764 VIETNAM | 1995 | 4686731256.54934 VIETNAM | 1994 | 4711690540.67498 VIETNAM | 1993 | 4699748450.54138 VIETNAM | 1992 | 4700740558.93364 (175 rows) COMMIT; COMMIT