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 | 276418074.922546 ALGERIA | 1997 | 461492291.912723 ALGERIA | 1996 | 464958099.575153 ALGERIA | 1995 | 469412260.058914 ALGERIA | 1994 | 468680136.554012 ALGERIA | 1993 | 463733666.746862 ALGERIA | 1992 | 461024257.014066 ARGENTINA | 1998 | 284171076.699541 ARGENTINA | 1997 | 473697445.349031 ARGENTINA | 1996 | 468116519.045839 ARGENTINA | 1995 | 474951690.600858 ARGENTINA | 1994 | 469069382.651734 ARGENTINA | 1993 | 465873510.8015 ARGENTINA | 1992 | 472384698.481024 BRAZIL | 1998 | 274321083.516071 BRAZIL | 1997 | 470583644.033976 BRAZIL | 1996 | 471487058.17652 BRAZIL | 1995 | 468642767.801987 BRAZIL | 1994 | 467757866.363109 BRAZIL | 1993 | 465499572.55202 BRAZIL | 1992 | 477447071.93297 CANADA | 1998 | 275207217.183694 CANADA | 1997 | 475986560.172786 CANADA | 1996 | 478830013.754885 CANADA | 1995 | 475623725.515445 CANADA | 1994 | 488934489.745503 CANADA | 1993 | 478653810.242263 CANADA | 1992 | 480812438.84747 CHINA | 1998 | 277423917.242383 CHINA | 1997 | 465950674.209593 CHINA | 1996 | 464487181.528053 CHINA | 1995 | 460319146.039384 CHINA | 1994 | 469293932.981847 CHINA | 1993 | 465568757.320296 CHINA | 1992 | 471896306.803353 EGYPT | 1998 | 272397717.449647 EGYPT | 1997 | 464732779.51509 EGYPT | 1996 | 461512180.064181 EGYPT | 1995 | 458737414.824007 EGYPT | 1994 | 466275376.921356 EGYPT | 1993 | 453705208.892455 EGYPT | 1992 | 468577455.926883 ETHIOPIA | 1998 | 266408862.381009 ETHIOPIA | 1997 | 465762727.671497 ETHIOPIA | 1996 | 459000790.997522 ETHIOPIA | 1995 | 459516926.729432 ETHIOPIA | 1994 | 463158672.3818 ETHIOPIA | 1993 | 465082513.034915 ETHIOPIA | 1992 | 462455550.210596 FRANCE | 1998 | 273928223.129625 FRANCE | 1997 | 462022682.400698 FRANCE | 1996 | 467267550.695984 FRANCE | 1995 | 464043105.555948 FRANCE | 1994 | 458523503.817162 FRANCE | 1993 | 466830402.960725 FRANCE | 1992 | 459108645.129977 GERMANY | 1998 | 275296870.122786 GERMANY | 1997 | 465810066.829954 GERMANY | 1996 | 466696560.571404 GERMANY | 1995 | 471387018.794465 GERMANY | 1994 | 474488332.903762 GERMANY | 1993 | 466416495.48996 GERMANY | 1992 | 471216166.490476 INDIA | 1998 | 276130170.542887 INDIA | 1997 | 469814621.756776 INDIA | 1996 | 472386035.971193 INDIA | 1995 | 476243420.208679 INDIA | 1994 | 474819480.527359 INDIA | 1993 | 472386852.193577 INDIA | 1992 | 480207548.194229 INDONESIA | 1998 | 268137970.355609 INDONESIA | 1997 | 465340774.874951 INDONESIA | 1996 | 463350957.802375 INDONESIA | 1995 | 467513049.01812 INDONESIA | 1994 | 460075799.625445 INDONESIA | 1993 | 465122293.17665 INDONESIA | 1992 | 458719072.378782 IRAN | 1998 | 277292392.478376 IRAN | 1997 | 461684401.021251 IRAN | 1996 | 471956485.659621 IRAN | 1995 | 462871239.014111 IRAN | 1994 | 468714241.600937 IRAN | 1993 | 467972138.366295 IRAN | 1992 | 470820637.999634 IRAQ | 1998 | 275028245.178227 IRAQ | 1997 | 472022635.259645 IRAQ | 1996 | 476686579.123371 IRAQ | 1995 | 473299062.950776 IRAQ | 1994 | 472720924.024081 IRAQ | 1993 | 481614435.070329 IRAQ | 1992 | 477970722.841927 JAPAN | 1998 | 268577203.499253 JAPAN | 1997 | 467360417.44286 JAPAN | 1996 | 459308240.796015 JAPAN | 1995 | 460259053.655533 JAPAN | 1994 | 456790040.389597 JAPAN | 1993 | 467390482.304833 JAPAN | 1992 | 457363574.089444 JORDAN | 1998 | 274322889.228306 JORDAN | 1997 | 469258494.608383 JORDAN | 1996 | 468506596.506848 JORDAN | 1995 | 462413131.765363 JORDAN | 1994 | 467331515.069932 JORDAN | 1993 | 465768212.547786 JORDAN | 1992 | 471808227.59585 KENYA | 1998 | 277728259.650876 KENYA | 1997 | 475730580.13199 KENYA | 1996 | 477106944.912841 KENYA | 1995 | 474622354.976221 KENYA | 1994 | 467321243.886544 KENYA | 1993 | 480448321.676554 KENYA | 1992 | 475229779.716694 MOROCCO | 1998 | 264454478.922534 MOROCCO | 1997 | 460085034.562069 MOROCCO | 1996 | 457012074.081511 MOROCCO | 1995 | 461432566.306439 MOROCCO | 1994 | 463107497.55275 MOROCCO | 1993 | 455737193.596868 MOROCCO | 1992 | 460833877.250984 MOZAMBIQUE | 1998 | 269314851.600032 MOZAMBIQUE | 1997 | 456035614.430853 MOZAMBIQUE | 1996 | 461695037.634465 MOZAMBIQUE | 1995 | 458331607.308838 MOZAMBIQUE | 1994 | 452965330.026044 MOZAMBIQUE | 1993 | 462452419.871861 MOZAMBIQUE | 1992 | 456124757.749504 PERU | 1998 | 273155105.267787 PERU | 1997 | 474321103.678799 PERU | 1996 | 461698739.003834 PERU | 1995 | 466038261.368701 PERU | 1994 | 462617887.245148 PERU | 1993 | 462141692.783614 PERU | 1992 | 470909763.183721 ROMANIA | 1998 | 280571463.159908 ROMANIA | 1997 | 469554961.420666 ROMANIA | 1996 | 474838250.538986 ROMANIA | 1995 | 476620001.772572 ROMANIA | 1994 | 473947205.447051 ROMANIA | 1993 | 474738488.485011 ROMANIA | 1992 | 469684069.753395 RUSSIA | 1998 | 275335737.807396 RUSSIA | 1997 | 476401375.170404 RUSSIA | 1996 | 480360004.982927 RUSSIA | 1995 | 462056643.153466 RUSSIA | 1994 | 465085523.300883 RUSSIA | 1993 | 474459933.368525 RUSSIA | 1992 | 484000117.719364 SAUDI ARABIA | 1998 | 274936985.032068 SAUDI ARABIA | 1997 | 476786789.948571 SAUDI ARABIA | 1996 | 457839035.929276 SAUDI ARABIA | 1995 | 461258186.484367 SAUDI ARABIA | 1994 | 463806307.370785 SAUDI ARABIA | 1993 | 467540080.103969 SAUDI ARABIA | 1992 | 468753588.082301 UNITED KINGDOM | 1998 | 273373888.754167 UNITED KINGDOM | 1997 | 462354763.533779 UNITED KINGDOM | 1996 | 463448163.88117 UNITED KINGDOM | 1995 | 456581560.784641 UNITED KINGDOM | 1994 | 467447065.901581 UNITED KINGDOM | 1993 | 465606815.481153 UNITED KINGDOM | 1992 | 471599327.883132 UNITED STATES | 1998 | 273242525.168582 UNITED STATES | 1997 | 475196317.604932 UNITED STATES | 1996 | 475723125.418663 UNITED STATES | 1995 | 475969582.51941 UNITED STATES | 1994 | 479561452.114601 UNITED STATES | 1993 | 476526482.858402 UNITED STATES | 1992 | 469030288.212131 VIETNAM | 1998 | 266944236.879777 VIETNAM | 1997 | 458264003.76156 VIETNAM | 1996 | 462237927.127922 VIETNAM | 1995 | 459886709.147509 VIETNAM | 1994 | 463004742.738473 VIETNAM | 1993 | 460655606.325208 VIETNAM | 1992 | 466869367.144036 (175 rows) COMMIT; COMMIT