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 '%midnight%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 276123742.037504 ALGERIA | 1997 | 461796599.684076 ALGERIA | 1996 | 464615506.039493 ALGERIA | 1995 | 469098745.667515 ALGERIA | 1994 | 468393529.975633 ALGERIA | 1993 | 463767816.845489 ALGERIA | 1992 | 461063179.357186 ARGENTINA | 1998 | 284120789.705439 ARGENTINA | 1997 | 473744505.358961 ARGENTINA | 1996 | 468122278.659128 ARGENTINA | 1995 | 475470451.171228 ARGENTINA | 1994 | 469592273.017807 ARGENTINA | 1993 | 466430910.563731 ARGENTINA | 1992 | 472227194.442645 BRAZIL | 1998 | 274095503.264094 BRAZIL | 1997 | 470666072.419677 BRAZIL | 1996 | 470834103.513881 BRAZIL | 1995 | 468295367.990072 BRAZIL | 1994 | 467846877.899354 BRAZIL | 1993 | 465630465.170326 BRAZIL | 1992 | 476570267.230639 CANADA | 1998 | 275630755.309231 CANADA | 1997 | 476690405.251139 CANADA | 1996 | 479183697.532412 CANADA | 1995 | 475911860.036608 CANADA | 1994 | 489514980.859966 CANADA | 1993 | 479175721.795604 CANADA | 1992 | 480436306.333694 CHINA | 1998 | 277765539.01337 CHINA | 1997 | 466329877.181022 CHINA | 1996 | 464235867.625157 CHINA | 1995 | 459837164.865935 CHINA | 1994 | 468313945.506714 CHINA | 1993 | 465137100.092395 CHINA | 1992 | 471910372.880118 EGYPT | 1998 | 272511722.341767 EGYPT | 1997 | 465118000.632706 EGYPT | 1996 | 460278611.891558 EGYPT | 1995 | 458939403.428891 EGYPT | 1994 | 466792074.130134 EGYPT | 1993 | 453330874.518489 EGYPT | 1992 | 467974570.635083 ETHIOPIA | 1998 | 266553819.494812 ETHIOPIA | 1997 | 465759303.79162 ETHIOPIA | 1996 | 458141154.13339 ETHIOPIA | 1995 | 458672461.169283 ETHIOPIA | 1994 | 463196723.481445 ETHIOPIA | 1993 | 465810265.977518 ETHIOPIA | 1992 | 463294572.642562 FRANCE | 1998 | 274097931.787526 FRANCE | 1997 | 462311636.60016 FRANCE | 1996 | 467030381.461901 FRANCE | 1995 | 464232502.823616 FRANCE | 1994 | 458210912.807712 FRANCE | 1993 | 466900835.30657 FRANCE | 1992 | 460333073.826563 GERMANY | 1998 | 275562326.749991 GERMANY | 1997 | 466137565.991843 GERMANY | 1996 | 466822217.250628 GERMANY | 1995 | 471706982.331863 GERMANY | 1994 | 475061116.312325 GERMANY | 1993 | 466482588.838014 GERMANY | 1992 | 471580314.917779 INDIA | 1998 | 276218819.021987 INDIA | 1997 | 470346839.327755 INDIA | 1996 | 471764961.65704 INDIA | 1995 | 475704966.653118 INDIA | 1994 | 475155267.304237 INDIA | 1993 | 473026366.627668 INDIA | 1992 | 480437771.422729 INDONESIA | 1998 | 268007955.560878 INDONESIA | 1997 | 464934462.182376 INDONESIA | 1996 | 462536151.021931 INDONESIA | 1995 | 468423106.374088 INDONESIA | 1994 | 460336740.209696 INDONESIA | 1993 | 464091950.80114 INDONESIA | 1992 | 459470252.085836 IRAN | 1998 | 277692476.032765 IRAN | 1997 | 461612627.265356 IRAN | 1996 | 471967441.951411 IRAN | 1995 | 463942786.387677 IRAN | 1994 | 468876525.59443 IRAN | 1993 | 468589311.313786 IRAN | 1992 | 471214478.946167 IRAQ | 1998 | 275228636.941543 IRAQ | 1997 | 471805612.082435 IRAQ | 1996 | 476587794.554296 IRAQ | 1995 | 473331999.089929 IRAQ | 1994 | 472666822.865375 IRAQ | 1993 | 481395284.447397 IRAQ | 1992 | 477081828.83286 JAPAN | 1998 | 269229869.063681 JAPAN | 1997 | 466696684.835861 JAPAN | 1996 | 460101974.728904 JAPAN | 1995 | 460232472.884335 JAPAN | 1994 | 455810181.746454 JAPAN | 1993 | 466822692.320535 JAPAN | 1992 | 457532912.895509 JORDAN | 1998 | 275052795.052575 JORDAN | 1997 | 469438802.47644 JORDAN | 1996 | 468407747.4158 JORDAN | 1995 | 462289647.201482 JORDAN | 1994 | 466367954.192576 JORDAN | 1993 | 466282966.638736 JORDAN | 1992 | 472610758.731434 KENYA | 1998 | 278560288.560947 KENYA | 1997 | 474646460.880915 KENYA | 1996 | 477982085.020227 KENYA | 1995 | 474238502.964648 KENYA | 1994 | 467860747.244168 KENYA | 1993 | 480468709.799378 KENYA | 1992 | 475467840.547613 MOROCCO | 1998 | 264254653.116937 MOROCCO | 1997 | 459938214.261131 MOROCCO | 1996 | 456802705.290352 MOROCCO | 1995 | 461698646.823364 MOROCCO | 1994 | 463411249.261002 MOROCCO | 1993 | 454993041.36791 MOROCCO | 1992 | 461194056.353431 MOZAMBIQUE | 1998 | 269968547.282532 MOZAMBIQUE | 1997 | 455322798.579466 MOZAMBIQUE | 1996 | 461945479.559872 MOZAMBIQUE | 1995 | 458627153.646708 MOZAMBIQUE | 1994 | 452776743.018016 MOZAMBIQUE | 1993 | 462278059.968309 MOZAMBIQUE | 1992 | 456176673.813834 PERU | 1998 | 273396691.388471 PERU | 1997 | 474406515.803026 PERU | 1996 | 461365977.193623 PERU | 1995 | 465840140.355649 PERU | 1994 | 463207811.952337 PERU | 1993 | 461871244.482647 PERU | 1992 | 471158520.95845 ROMANIA | 1998 | 280041358.736268 ROMANIA | 1997 | 470511042.03022 ROMANIA | 1996 | 475013008.007663 ROMANIA | 1995 | 476710299.594619 ROMANIA | 1994 | 473501308.398692 ROMANIA | 1993 | 474346077.018051 ROMANIA | 1992 | 469612631.362778 RUSSIA | 1998 | 275803183.50152 RUSSIA | 1997 | 477243694.540466 RUSSIA | 1996 | 480783845.992883 RUSSIA | 1995 | 461845409.243299 RUSSIA | 1994 | 465349034.005321 RUSSIA | 1993 | 474484918.398284 RUSSIA | 1992 | 483241317.755196 SAUDI ARABIA | 1998 | 275014163.248004 SAUDI ARABIA | 1997 | 477618466.167599 SAUDI ARABIA | 1996 | 458214589.317654 SAUDI ARABIA | 1995 | 460826426.771664 SAUDI ARABIA | 1994 | 464396872.480564 SAUDI ARABIA | 1993 | 467461089.804076 SAUDI ARABIA | 1992 | 468280168.861172 UNITED KINGDOM | 1998 | 272826202.240926 UNITED KINGDOM | 1997 | 463066691.645547 UNITED KINGDOM | 1996 | 462599092.893592 UNITED KINGDOM | 1995 | 456374821.64942 UNITED KINGDOM | 1994 | 466947278.866542 UNITED KINGDOM | 1993 | 464683108.435028 UNITED KINGDOM | 1992 | 471973315.520638 UNITED STATES | 1998 | 273119147.166044 UNITED STATES | 1997 | 475121495.278517 UNITED STATES | 1996 | 474871194.501329 UNITED STATES | 1995 | 476339592.228324 UNITED STATES | 1994 | 480220285.111242 UNITED STATES | 1993 | 476234438.804756 UNITED STATES | 1992 | 469770316.931275 VIETNAM | 1998 | 267034931.741791 VIETNAM | 1997 | 458786226.585494 VIETNAM | 1996 | 462562955.233791 VIETNAM | 1995 | 460550439.353499 VIETNAM | 1994 | 463698729.982835 VIETNAM | 1993 | 461614001.960189 VIETNAM | 1992 | 467082264.371186 (175 rows) COMMIT; COMMIT