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 '%dim%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270759459.830733 ALGERIA | 1997 | 461208288.321465 ALGERIA | 1996 | 466819048.984491 ALGERIA | 1995 | 455346997.83362 ALGERIA | 1994 | 454437075.299901 ALGERIA | 1993 | 460075752.645776 ALGERIA | 1992 | 455810711.228528 ARGENTINA | 1998 | 274674840.20155 ARGENTINA | 1997 | 477153011.866 ARGENTINA | 1996 | 469763965.537199 ARGENTINA | 1995 | 474540827.670883 ARGENTINA | 1994 | 470547935.016419 ARGENTINA | 1993 | 466867636.919801 ARGENTINA | 1992 | 469744332.17354 BRAZIL | 1998 | 270188328.329263 BRAZIL | 1997 | 461686894.412274 BRAZIL | 1996 | 463678772.856504 BRAZIL | 1995 | 461543507.747202 BRAZIL | 1994 | 459000578.51019 BRAZIL | 1993 | 465500942.487403 BRAZIL | 1992 | 463759384.444791 CANADA | 1998 | 276099087.401331 CANADA | 1997 | 467217311.18036 CANADA | 1996 | 475807635.265787 CANADA | 1995 | 472023927.934045 CANADA | 1994 | 475253941.26954 CANADA | 1993 | 469104020.865055 CANADA | 1992 | 476043729.98606 CHINA | 1998 | 275573504.096967 CHINA | 1997 | 469082063.001283 CHINA | 1996 | 467232122.396854 CHINA | 1995 | 470619288.451501 CHINA | 1994 | 463408493.534357 CHINA | 1993 | 467052353.865222 CHINA | 1992 | 471021887.25952 EGYPT | 1998 | 277320858.260514 EGYPT | 1997 | 472403316.676343 EGYPT | 1996 | 473759529.775615 EGYPT | 1995 | 462299966.108102 EGYPT | 1994 | 466541467.664954 EGYPT | 1993 | 467511180.934459 EGYPT | 1992 | 467995338.196046 ETHIOPIA | 1998 | 265948538.231907 ETHIOPIA | 1997 | 454011582.919872 ETHIOPIA | 1996 | 454909629.522216 ETHIOPIA | 1995 | 455020079.682786 ETHIOPIA | 1994 | 453056161.494591 ETHIOPIA | 1993 | 458528166.753648 ETHIOPIA | 1992 | 462341904.696531 FRANCE | 1998 | 264627123.531489 FRANCE | 1997 | 458928925.107005 FRANCE | 1996 | 453558346.501191 FRANCE | 1995 | 454729105.244157 FRANCE | 1994 | 456587297.094758 FRANCE | 1993 | 456974356.162863 FRANCE | 1992 | 450725381.02317 GERMANY | 1998 | 280369049.257898 GERMANY | 1997 | 474067147.087158 GERMANY | 1996 | 474260894.747736 GERMANY | 1995 | 475360623.665649 GERMANY | 1994 | 477679589.53759 GERMANY | 1993 | 479884836.840866 GERMANY | 1992 | 476628010.61497 INDIA | 1998 | 271512486.70826 INDIA | 1997 | 481134634.504332 INDIA | 1996 | 479017787.385959 INDIA | 1995 | 477550945.180887 INDIA | 1994 | 471863751.629223 INDIA | 1993 | 471287137.553181 INDIA | 1992 | 477544902.211125 INDONESIA | 1998 | 271478479.311206 INDONESIA | 1997 | 463853646.95087 INDONESIA | 1996 | 471913933.043662 INDONESIA | 1995 | 468489405.698146 INDONESIA | 1994 | 460010271.968136 INDONESIA | 1993 | 472618588.176427 INDONESIA | 1992 | 468772428.821888 IRAN | 1998 | 273343266.717997 IRAN | 1997 | 464910663.63386 IRAN | 1996 | 453343763.950547 IRAN | 1995 | 456946095.398579 IRAN | 1994 | 457148086.092644 IRAN | 1993 | 454033800.133091 IRAN | 1992 | 457153267.383686 IRAQ | 1998 | 273875123.497367 IRAQ | 1997 | 473856591.494112 IRAQ | 1996 | 471767789.259476 IRAQ | 1995 | 473755550.419097 IRAQ | 1994 | 471571387.032424 IRAQ | 1993 | 471824299.109406 IRAQ | 1992 | 476017253.371215 JAPAN | 1998 | 269260916.814672 JAPAN | 1997 | 465424557.295869 JAPAN | 1996 | 464937991.999762 JAPAN | 1995 | 468573132.075352 JAPAN | 1994 | 455824903.963954 JAPAN | 1993 | 463620607.571295 JAPAN | 1992 | 459711717.62737 JORDAN | 1998 | 275139741.84642 JORDAN | 1997 | 459337212.628413 JORDAN | 1996 | 457601333.312396 JORDAN | 1995 | 456075913.087379 JORDAN | 1994 | 455663449.512449 JORDAN | 1993 | 455688521.804348 JORDAN | 1992 | 461949161.409276 KENYA | 1998 | 273491505.227333 KENYA | 1997 | 466884030.672443 KENYA | 1996 | 472911366.914863 KENYA | 1995 | 465236546.33537 KENYA | 1994 | 466866304.009207 KENYA | 1993 | 473083555.524117 KENYA | 1992 | 472737492.086954 MOROCCO | 1998 | 275385249.443453 MOROCCO | 1997 | 469875979.478748 MOROCCO | 1996 | 463719778.860171 MOROCCO | 1995 | 468321740.244549 MOROCCO | 1994 | 470372806.146481 MOROCCO | 1993 | 463222451.322392 MOROCCO | 1992 | 468626565.671717 MOZAMBIQUE | 1998 | 275164187.725271 MOZAMBIQUE | 1997 | 469116188.413687 MOZAMBIQUE | 1996 | 466644534.725432 MOZAMBIQUE | 1995 | 470735340.387859 MOZAMBIQUE | 1994 | 458968604.672688 MOZAMBIQUE | 1993 | 463374151.804757 MOZAMBIQUE | 1992 | 460483908.891676 PERU | 1998 | 275647415.268196 PERU | 1997 | 468104122.607401 PERU | 1996 | 460977112.889848 PERU | 1995 | 458849985.956868 PERU | 1994 | 462441307.387287 PERU | 1993 | 464184444.116428 PERU | 1992 | 468250101.790643 ROMANIA | 1998 | 276297226.341761 ROMANIA | 1997 | 466671735.846688 ROMANIA | 1996 | 472746583.093122 ROMANIA | 1995 | 466119352.239028 ROMANIA | 1994 | 471505250.475381 ROMANIA | 1993 | 470896784.183921 ROMANIA | 1992 | 471409375.253564 RUSSIA | 1998 | 277767917.509385 RUSSIA | 1997 | 472581212.156848 RUSSIA | 1996 | 474620454.104746 RUSSIA | 1995 | 477383986.934878 RUSSIA | 1994 | 472596313.529614 RUSSIA | 1993 | 471960800.965066 RUSSIA | 1992 | 480900493.675825 SAUDI ARABIA | 1998 | 270601382.016859 SAUDI ARABIA | 1997 | 457955812.822398 SAUDI ARABIA | 1996 | 461134590.72585 SAUDI ARABIA | 1995 | 461876487.440353 SAUDI ARABIA | 1994 | 462020933.030349 SAUDI ARABIA | 1993 | 466295438.130273 SAUDI ARABIA | 1992 | 474374042.639942 UNITED KINGDOM | 1998 | 273034168.97452 UNITED KINGDOM | 1997 | 463361903.025839 UNITED KINGDOM | 1996 | 462837291.854249 UNITED KINGDOM | 1995 | 462752380.688152 UNITED KINGDOM | 1994 | 466242476.607898 UNITED KINGDOM | 1993 | 460920891.405073 UNITED KINGDOM | 1992 | 462961703.576208 UNITED STATES | 1998 | 283542104.489903 UNITED STATES | 1997 | 474137334.461476 UNITED STATES | 1996 | 476739864.810478 UNITED STATES | 1995 | 474478488.466365 UNITED STATES | 1994 | 475691823.742579 UNITED STATES | 1993 | 480583768.37234 UNITED STATES | 1992 | 485302837.623986 VIETNAM | 1998 | 274712556.61919 VIETNAM | 1997 | 464668500.459 VIETNAM | 1996 | 469780369.234625 VIETNAM | 1995 | 465973545.593907 VIETNAM | 1994 | 465554372.802302 VIETNAM | 1993 | 475122218.3203 VIETNAM | 1992 | 465138178.769522 (175 rows) COMMIT; COMMIT