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 '%black%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261802329.537058 ALGERIA | 1997 | 449682235.644012 ALGERIA | 1996 | 450576614.950204 ALGERIA | 1995 | 448035563.459436 ALGERIA | 1994 | 456493044.298263 ALGERIA | 1993 | 453807440.285522 ALGERIA | 1992 | 449325950.660213 ARGENTINA | 1998 | 279694435.722824 ARGENTINA | 1997 | 479799558.085194 ARGENTINA | 1996 | 472167653.329352 ARGENTINA | 1995 | 471753598.16701 ARGENTINA | 1994 | 474753441.394785 ARGENTINA | 1993 | 467588029.253612 ARGENTINA | 1992 | 487328784.277535 BRAZIL | 1998 | 279955597.690924 BRAZIL | 1997 | 476162764.132968 BRAZIL | 1996 | 472546228.620101 BRAZIL | 1995 | 471517302.59336 BRAZIL | 1994 | 470704655.099135 BRAZIL | 1993 | 471687461.402573 BRAZIL | 1992 | 477813787.433129 CANADA | 1998 | 282081824.770019 CANADA | 1997 | 486330613.00824 CANADA | 1996 | 484092356.007397 CANADA | 1995 | 480437983.911299 CANADA | 1994 | 490454377.310718 CANADA | 1993 | 486206154.934197 CANADA | 1992 | 492318773.059134 CHINA | 1998 | 274540053.934654 CHINA | 1997 | 461358432.3408 CHINA | 1996 | 465142742.415494 CHINA | 1995 | 470345631.657602 CHINA | 1994 | 467988064.674469 CHINA | 1993 | 467778649.430196 CHINA | 1992 | 466981373.545793 EGYPT | 1998 | 272137212.187052 EGYPT | 1997 | 461034970.190299 EGYPT | 1996 | 471433735.207026 EGYPT | 1995 | 465450029.298823 EGYPT | 1994 | 462825608.123873 EGYPT | 1993 | 466483288.627389 EGYPT | 1992 | 459876360.14706 ETHIOPIA | 1998 | 267612583.585064 ETHIOPIA | 1997 | 452220169.941303 ETHIOPIA | 1996 | 446497928.821689 ETHIOPIA | 1995 | 454028813.102044 ETHIOPIA | 1994 | 456915897.964641 ETHIOPIA | 1993 | 457605347.225955 ETHIOPIA | 1992 | 455788771.570576 FRANCE | 1998 | 264881408.855929 FRANCE | 1997 | 456497089.381815 FRANCE | 1996 | 468018343.011623 FRANCE | 1995 | 467551545.899151 FRANCE | 1994 | 465810588.93909 FRANCE | 1993 | 473291741.140438 FRANCE | 1992 | 464366863.117258 GERMANY | 1998 | 275360504.882787 GERMANY | 1997 | 476762728.919294 GERMANY | 1996 | 476912998.550802 GERMANY | 1995 | 469862266.064633 GERMANY | 1994 | 468090924.035821 GERMANY | 1993 | 468876299.099673 GERMANY | 1992 | 461323055.944978 INDIA | 1998 | 281138948.952654 INDIA | 1997 | 475115719.562816 INDIA | 1996 | 474568529.88209 INDIA | 1995 | 469432519.644679 INDIA | 1994 | 474949319.88717 INDIA | 1993 | 474039497.387671 INDIA | 1992 | 483201056.201513 INDONESIA | 1998 | 270559258.611327 INDONESIA | 1997 | 464122447.140975 INDONESIA | 1996 | 451339992.446289 INDONESIA | 1995 | 461002347.100398 INDONESIA | 1994 | 464307046.992734 INDONESIA | 1993 | 467360934.942854 INDONESIA | 1992 | 462320967.503889 IRAN | 1998 | 272669419.109075 IRAN | 1997 | 462437821.551162 IRAN | 1996 | 463061932.311141 IRAN | 1995 | 468083402.094964 IRAN | 1994 | 460337750.024134 IRAN | 1993 | 472689866.995177 IRAN | 1992 | 464614529.721396 IRAQ | 1998 | 271279242.623551 IRAQ | 1997 | 473824214.038869 IRAQ | 1996 | 470116774.657423 IRAQ | 1995 | 473860197.938502 IRAQ | 1994 | 477950224.128401 IRAQ | 1993 | 471435782.377758 IRAQ | 1992 | 473653782.744884 JAPAN | 1998 | 276554106.366102 JAPAN | 1997 | 469838232.944849 JAPAN | 1996 | 470724161.887594 JAPAN | 1995 | 474551739.344693 JAPAN | 1994 | 465360120.684834 JAPAN | 1993 | 468291320.971949 JAPAN | 1992 | 472521092.402445 JORDAN | 1998 | 272042219.752414 JORDAN | 1997 | 457094450.051718 JORDAN | 1996 | 468276848.188316 JORDAN | 1995 | 461741075.326582 JORDAN | 1994 | 468337932.9925 JORDAN | 1993 | 464687688.597658 JORDAN | 1992 | 466440632.303737 KENYA | 1998 | 281280383.011303 KENYA | 1997 | 469212760.958959 KENYA | 1996 | 473554470.32839 KENYA | 1995 | 473892767.301284 KENYA | 1994 | 473760002.410564 KENYA | 1993 | 471034086.842922 KENYA | 1992 | 470921783.915099 MOROCCO | 1998 | 277078553.884715 MOROCCO | 1997 | 463841730.407596 MOROCCO | 1996 | 470595990.443679 MOROCCO | 1995 | 468660107.342669 MOROCCO | 1994 | 474300782.480948 MOROCCO | 1993 | 471621483.943593 MOROCCO | 1992 | 466648800.511832 MOZAMBIQUE | 1998 | 277142161.600757 MOZAMBIQUE | 1997 | 460255835.428849 MOZAMBIQUE | 1996 | 457117725.981904 MOZAMBIQUE | 1995 | 461643353.275378 MOZAMBIQUE | 1994 | 463048051.224007 MOZAMBIQUE | 1993 | 455603529.408291 MOZAMBIQUE | 1992 | 458467999.7448 PERU | 1998 | 270123089.787263 PERU | 1997 | 471227385.950864 PERU | 1996 | 463567868.043627 PERU | 1995 | 468163460.928976 PERU | 1994 | 463533253.505274 PERU | 1993 | 467024238.257673 PERU | 1992 | 475744213.218379 ROMANIA | 1998 | 281619420.069858 ROMANIA | 1997 | 463152428.197262 ROMANIA | 1996 | 481251937.143379 ROMANIA | 1995 | 474793259.713969 ROMANIA | 1994 | 475629305.576845 ROMANIA | 1993 | 470312112.758057 ROMANIA | 1992 | 473921101.260412 RUSSIA | 1998 | 282987193.326776 RUSSIA | 1997 | 471168371.841074 RUSSIA | 1996 | 473483881.989001 RUSSIA | 1995 | 479897414.50689 RUSSIA | 1994 | 471975122.822705 RUSSIA | 1993 | 472611200.391116 RUSSIA | 1992 | 477536390.908305 SAUDI ARABIA | 1998 | 270622946.980564 SAUDI ARABIA | 1997 | 463499206.478604 SAUDI ARABIA | 1996 | 473406697.238618 SAUDI ARABIA | 1995 | 470744498.102655 SAUDI ARABIA | 1994 | 465442482.322943 SAUDI ARABIA | 1993 | 468638326.499599 SAUDI ARABIA | 1992 | 464613464.504802 UNITED KINGDOM | 1998 | 273330226.160787 UNITED KINGDOM | 1997 | 467084158.333799 UNITED KINGDOM | 1996 | 459862621.674949 UNITED KINGDOM | 1995 | 463908376.221847 UNITED KINGDOM | 1994 | 463573715.913746 UNITED KINGDOM | 1993 | 464711582.968129 UNITED KINGDOM | 1992 | 459622339.462401 UNITED STATES | 1998 | 275574680.314723 UNITED STATES | 1997 | 469058156.833979 UNITED STATES | 1996 | 466763177.470118 UNITED STATES | 1995 | 470218011.370347 UNITED STATES | 1994 | 480488408.297968 UNITED STATES | 1993 | 473739062.507364 UNITED STATES | 1992 | 468390702.392523 VIETNAM | 1998 | 278912005.235107 VIETNAM | 1997 | 462534258.215678 VIETNAM | 1996 | 472525048.743456 VIETNAM | 1995 | 468748364.567308 VIETNAM | 1994 | 468289720.843364 VIETNAM | 1993 | 470624498.887227 VIETNAM | 1992 | 467902231.216606 (175 rows) COMMIT; COMMIT