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 '%deep%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272436804.42799 ALGERIA | 1997 | 457233600.492998 ALGERIA | 1996 | 463933422.249438 ALGERIA | 1995 | 453518631.159087 ALGERIA | 1994 | 453681861.197724 ALGERIA | 1993 | 453113074.430529 ALGERIA | 1992 | 460360844.298914 ARGENTINA | 1998 | 284227186.172286 ARGENTINA | 1997 | 479479919.906702 ARGENTINA | 1996 | 476727663.06922 ARGENTINA | 1995 | 480440420.396776 ARGENTINA | 1994 | 476287053.844083 ARGENTINA | 1993 | 483562622.219667 ARGENTINA | 1992 | 485284555.767214 BRAZIL | 1998 | 277650833.010771 BRAZIL | 1997 | 455240817.59966 BRAZIL | 1996 | 462921028.326417 BRAZIL | 1995 | 465098586.362964 BRAZIL | 1994 | 460269949.786294 BRAZIL | 1993 | 461472470.817719 BRAZIL | 1992 | 462626714.43408 CANADA | 1998 | 272749551.110625 CANADA | 1997 | 469280973.870669 CANADA | 1996 | 477935760.497944 CANADA | 1995 | 466102218.923839 CANADA | 1994 | 475438252.489712 CANADA | 1993 | 469150283.871187 CANADA | 1992 | 468693208.39006 CHINA | 1998 | 274262522.269452 CHINA | 1997 | 459191669.569008 CHINA | 1996 | 467062448.78565 CHINA | 1995 | 459578001.658081 CHINA | 1994 | 466003435.610827 CHINA | 1993 | 461577114.148329 CHINA | 1992 | 469193407.323344 EGYPT | 1998 | 270455593.28724 EGYPT | 1997 | 460143258.434387 EGYPT | 1996 | 466360033.339998 EGYPT | 1995 | 463529131.627934 EGYPT | 1994 | 464284907.412312 EGYPT | 1993 | 467163423.449893 EGYPT | 1992 | 461063852.084968 ETHIOPIA | 1998 | 260700215.412146 ETHIOPIA | 1997 | 452058234.197834 ETHIOPIA | 1996 | 448897074.908484 ETHIOPIA | 1995 | 449962593.606021 ETHIOPIA | 1994 | 447906752.648897 ETHIOPIA | 1993 | 453749992.271954 ETHIOPIA | 1992 | 462574921.202114 FRANCE | 1998 | 264840283.052422 FRANCE | 1997 | 456073346.231555 FRANCE | 1996 | 455045548.953693 FRANCE | 1995 | 456651094.379459 FRANCE | 1994 | 454716040.150329 FRANCE | 1993 | 461115396.118342 FRANCE | 1992 | 448689026.972423 GERMANY | 1998 | 274315453.520443 GERMANY | 1997 | 486623639.379069 GERMANY | 1996 | 477090879.76548 GERMANY | 1995 | 487622679.900047 GERMANY | 1994 | 476682308.148958 GERMANY | 1993 | 478257096.700572 GERMANY | 1992 | 476118984.246221 INDIA | 1998 | 277364867.721489 INDIA | 1997 | 479770983.102151 INDIA | 1996 | 474599296.600601 INDIA | 1995 | 477491629.968721 INDIA | 1994 | 464806893.059449 INDIA | 1993 | 481675547.508008 INDIA | 1992 | 474321725.745831 INDONESIA | 1998 | 271678515.071982 INDONESIA | 1997 | 462780848.001275 INDONESIA | 1996 | 462987818.904173 INDONESIA | 1995 | 454092287.410355 INDONESIA | 1994 | 458006947.44467 INDONESIA | 1993 | 459566610.897136 INDONESIA | 1992 | 457992760.277017 IRAN | 1998 | 264899293.927272 IRAN | 1997 | 461424371.236934 IRAN | 1996 | 465960653.455733 IRAN | 1995 | 470376409.01163 IRAN | 1994 | 468068154.23169 IRAN | 1993 | 466373822.887136 IRAN | 1992 | 466912260.460885 IRAQ | 1998 | 271712146.473076 IRAQ | 1997 | 463757075.939616 IRAQ | 1996 | 470188125.112553 IRAQ | 1995 | 467424714.343745 IRAQ | 1994 | 468472808.82915 IRAQ | 1993 | 468681364.83852 IRAQ | 1992 | 471936236.111398 JAPAN | 1998 | 270009508.240443 JAPAN | 1997 | 465621855.888103 JAPAN | 1996 | 457958518.17354 JAPAN | 1995 | 458273421.734864 JAPAN | 1994 | 468910878.759098 JAPAN | 1993 | 467797350.589042 JAPAN | 1992 | 463827609.854444 JORDAN | 1998 | 269942934.197879 JORDAN | 1997 | 461121944.344812 JORDAN | 1996 | 454745444.956173 JORDAN | 1995 | 463772624.616583 JORDAN | 1994 | 462922579.219914 JORDAN | 1993 | 460619670.183152 JORDAN | 1992 | 458674831.07089 KENYA | 1998 | 275524328.263487 KENYA | 1997 | 466763314.88093 KENYA | 1996 | 469290127.793282 KENYA | 1995 | 463912220.847401 KENYA | 1994 | 469226782.728813 KENYA | 1993 | 474161914.470143 KENYA | 1992 | 476319812.877883 MOROCCO | 1998 | 275945597.171461 MOROCCO | 1997 | 468912206.712323 MOROCCO | 1996 | 463622257.819923 MOROCCO | 1995 | 464112149.652762 MOROCCO | 1994 | 464226720.949946 MOROCCO | 1993 | 460626576.596572 MOROCCO | 1992 | 463313930.66388 MOZAMBIQUE | 1998 | 275046195.126938 MOZAMBIQUE | 1997 | 463562043.139752 MOZAMBIQUE | 1996 | 469053634.249719 MOZAMBIQUE | 1995 | 469397460.604832 MOZAMBIQUE | 1994 | 472401999.382883 MOZAMBIQUE | 1993 | 458652700.533354 MOZAMBIQUE | 1992 | 467351968.396351 PERU | 1998 | 270821114.856165 PERU | 1997 | 460045650.723847 PERU | 1996 | 467166593.950555 PERU | 1995 | 459433544.997327 PERU | 1994 | 456047335.710078 PERU | 1993 | 456751276.683415 PERU | 1992 | 463120762.668415 ROMANIA | 1998 | 268995383.927616 ROMANIA | 1997 | 464587025.403774 ROMANIA | 1996 | 465025859.355116 ROMANIA | 1995 | 454484662.084161 ROMANIA | 1994 | 462317966.347134 ROMANIA | 1993 | 460592983.118528 ROMANIA | 1992 | 465396229.529071 RUSSIA | 1998 | 276437051.657967 RUSSIA | 1997 | 473946929.011816 RUSSIA | 1996 | 473088110.233682 RUSSIA | 1995 | 473294964.664232 RUSSIA | 1994 | 465666784.425372 RUSSIA | 1993 | 471667558.818713 RUSSIA | 1992 | 474373130.621797 SAUDI ARABIA | 1998 | 275255408.341246 SAUDI ARABIA | 1997 | 473220836.46456 SAUDI ARABIA | 1996 | 476097366.414369 SAUDI ARABIA | 1995 | 471280029.860943 SAUDI ARABIA | 1994 | 468552727.822083 SAUDI ARABIA | 1993 | 469476384.750966 SAUDI ARABIA | 1992 | 467700527.172293 UNITED KINGDOM | 1998 | 272205272.170938 UNITED KINGDOM | 1997 | 463230240.189712 UNITED KINGDOM | 1996 | 462605397.712342 UNITED KINGDOM | 1995 | 468819127.712635 UNITED KINGDOM | 1994 | 464766235.581759 UNITED KINGDOM | 1993 | 467329464.828771 UNITED KINGDOM | 1992 | 463871641.625459 UNITED STATES | 1998 | 276779374.400606 UNITED STATES | 1997 | 472177154.748282 UNITED STATES | 1996 | 475359358.169661 UNITED STATES | 1995 | 467908125.046659 UNITED STATES | 1994 | 469593221.060369 UNITED STATES | 1993 | 465347040.29015 UNITED STATES | 1992 | 464007976.868078 VIETNAM | 1998 | 279135900.882135 VIETNAM | 1997 | 469279764.265643 VIETNAM | 1996 | 473698786.711826 VIETNAM | 1995 | 463927240.080982 VIETNAM | 1994 | 472121155.585724 VIETNAM | 1993 | 468891384.390015 VIETNAM | 1992 | 470886995.567417 (175 rows) COMMIT; COMMIT