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 '%lace%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 263866900.540402 ALGERIA | 1997 | 450777307.495167 ALGERIA | 1996 | 451077364.546894 ALGERIA | 1995 | 453087550.081416 ALGERIA | 1994 | 457926068.763976 ALGERIA | 1993 | 453656037.958216 ALGERIA | 1992 | 449927227.928905 ARGENTINA | 1998 | 267840567.83201 ARGENTINA | 1997 | 459991727.916593 ARGENTINA | 1996 | 461517422.138507 ARGENTINA | 1995 | 454975079.198681 ARGENTINA | 1994 | 460069180.205253 ARGENTINA | 1993 | 461622329.687683 ARGENTINA | 1992 | 460491041.203587 BRAZIL | 1998 | 276451655.688462 BRAZIL | 1997 | 472019386.976284 BRAZIL | 1996 | 476056613.15212 BRAZIL | 1995 | 481956709.330735 BRAZIL | 1994 | 475386926.669597 BRAZIL | 1993 | 472309916.172171 BRAZIL | 1992 | 471330382.760201 CANADA | 1998 | 279866006.667964 CANADA | 1997 | 476112412.412021 CANADA | 1996 | 481147913.240565 CANADA | 1995 | 468932515.768479 CANADA | 1994 | 471243863.984966 CANADA | 1993 | 475429662.124556 CANADA | 1992 | 475726466.764312 CHINA | 1998 | 272074079.194354 CHINA | 1997 | 462829783.087513 CHINA | 1996 | 461974698.379314 CHINA | 1995 | 457486023.939703 CHINA | 1994 | 463876040.536796 CHINA | 1993 | 462240288.864071 CHINA | 1992 | 458795222.745887 EGYPT | 1998 | 274750147.083927 EGYPT | 1997 | 463556697.596416 EGYPT | 1996 | 461252081.705468 EGYPT | 1995 | 461766520.972389 EGYPT | 1994 | 461862332.604088 EGYPT | 1993 | 468228202.089767 EGYPT | 1992 | 467720232.778201 ETHIOPIA | 1998 | 271299021.91529 ETHIOPIA | 1997 | 465719804.162221 ETHIOPIA | 1996 | 462571362.030868 ETHIOPIA | 1995 | 467345840.490528 ETHIOPIA | 1994 | 471653199.127756 ETHIOPIA | 1993 | 468528941.083282 ETHIOPIA | 1992 | 462232424.497359 FRANCE | 1998 | 261658055.761029 FRANCE | 1997 | 453868021.164235 FRANCE | 1996 | 454211581.61916 FRANCE | 1995 | 462114765.867116 FRANCE | 1994 | 457429442.408618 FRANCE | 1993 | 455848104.548834 FRANCE | 1992 | 452143361.876368 GERMANY | 1998 | 274677380.914993 GERMANY | 1997 | 464823511.117203 GERMANY | 1996 | 466157743.732873 GERMANY | 1995 | 474250410.513296 GERMANY | 1994 | 471709552.710233 GERMANY | 1993 | 473380257.164378 GERMANY | 1992 | 467530153.842934 INDIA | 1998 | 284624008.978208 INDIA | 1997 | 475090525.302233 INDIA | 1996 | 482131412.513078 INDIA | 1995 | 478551031.531461 INDIA | 1994 | 473744744.016415 INDIA | 1993 | 480875911.518263 INDIA | 1992 | 477450346.806751 INDONESIA | 1998 | 270538922.019822 INDONESIA | 1997 | 472158504.795794 INDONESIA | 1996 | 466879088.869796 INDONESIA | 1995 | 463365945.106124 INDONESIA | 1994 | 473824764.004219 INDONESIA | 1993 | 466806298.661949 INDONESIA | 1992 | 466705963.065072 IRAN | 1998 | 269559308.291746 IRAN | 1997 | 465725695.321604 IRAN | 1996 | 459444754.452756 IRAN | 1995 | 465441962.021243 IRAN | 1994 | 457073009.662588 IRAN | 1993 | 461661176.143604 IRAN | 1992 | 466446652.517952 IRAQ | 1998 | 271675653.933388 IRAQ | 1997 | 469630856.20654 IRAQ | 1996 | 466684619.115594 IRAQ | 1995 | 468468377.740604 IRAQ | 1994 | 477636784.602506 IRAQ | 1993 | 462397832.920859 IRAQ | 1992 | 466068378.956565 JAPAN | 1998 | 275311327.841371 JAPAN | 1997 | 465277778.254945 JAPAN | 1996 | 466435550.039741 JAPAN | 1995 | 473114736.225443 JAPAN | 1994 | 458169753.217689 JAPAN | 1993 | 469067029.041213 JAPAN | 1992 | 472378024.393783 JORDAN | 1998 | 269598495.947058 JORDAN | 1997 | 459142194.674483 JORDAN | 1996 | 462930801.681417 JORDAN | 1995 | 461535901.944902 JORDAN | 1994 | 464712333.571705 JORDAN | 1993 | 466095069.85827 JORDAN | 1992 | 463127928.232815 KENYA | 1998 | 278236324.704841 KENYA | 1997 | 473093602.392145 KENYA | 1996 | 469932286.243415 KENYA | 1995 | 468048684.461281 KENYA | 1994 | 473120344.984014 KENYA | 1993 | 473525718.395982 KENYA | 1992 | 484757460.779121 MOROCCO | 1998 | 272387592.684584 MOROCCO | 1997 | 476055048.736997 MOROCCO | 1996 | 466888244.811903 MOROCCO | 1995 | 461498483.124849 MOROCCO | 1994 | 467505927.356198 MOROCCO | 1993 | 468184670.346077 MOROCCO | 1992 | 464343757.985353 MOZAMBIQUE | 1998 | 267353128.911563 MOZAMBIQUE | 1997 | 460173636.773506 MOZAMBIQUE | 1996 | 464214764.16669 MOZAMBIQUE | 1995 | 453259718.912753 MOZAMBIQUE | 1994 | 457188683.191356 MOZAMBIQUE | 1993 | 455364880.085859 MOZAMBIQUE | 1992 | 449592623.415846 PERU | 1998 | 269554812.172736 PERU | 1997 | 458775722.669533 PERU | 1996 | 468799346.219542 PERU | 1995 | 462982339.34435 PERU | 1994 | 458971617.237837 PERU | 1993 | 467620181.749622 PERU | 1992 | 465249493.829801 ROMANIA | 1998 | 271227950.36561 ROMANIA | 1997 | 462965144.579949 ROMANIA | 1996 | 472124790.286815 ROMANIA | 1995 | 467383149.56887 ROMANIA | 1994 | 473969910.746555 ROMANIA | 1993 | 474884412.774149 ROMANIA | 1992 | 469523875.242014 RUSSIA | 1998 | 275591436.517585 RUSSIA | 1997 | 471106225.724759 RUSSIA | 1996 | 472896143.107342 RUSSIA | 1995 | 462113407.292066 RUSSIA | 1994 | 469796310.344627 RUSSIA | 1993 | 463216902.284239 RUSSIA | 1992 | 473721340.313632 SAUDI ARABIA | 1998 | 272641826.874567 SAUDI ARABIA | 1997 | 468377052.243384 SAUDI ARABIA | 1996 | 466352459.015077 SAUDI ARABIA | 1995 | 468169364.471865 SAUDI ARABIA | 1994 | 466510851.410629 SAUDI ARABIA | 1993 | 467926892.287336 SAUDI ARABIA | 1992 | 468571080.858234 UNITED KINGDOM | 1998 | 277519466.982659 UNITED KINGDOM | 1997 | 464857061.930147 UNITED KINGDOM | 1996 | 464443665.17982 UNITED KINGDOM | 1995 | 464108357.648252 UNITED KINGDOM | 1994 | 471372223.747798 UNITED KINGDOM | 1993 | 460535481.064597 UNITED KINGDOM | 1992 | 454289842.925982 UNITED STATES | 1998 | 262621680.636157 UNITED STATES | 1997 | 459928329.671032 UNITED STATES | 1996 | 464936141.93009 UNITED STATES | 1995 | 457967509.646678 UNITED STATES | 1994 | 458016935.645958 UNITED STATES | 1993 | 463290893.390572 UNITED STATES | 1992 | 454609839.211673 VIETNAM | 1998 | 271194123.460667 VIETNAM | 1997 | 464754081.215878 VIETNAM | 1996 | 477415335.924128 VIETNAM | 1995 | 469448952.967808 VIETNAM | 1994 | 463758639.436638 VIETNAM | 1993 | 466977368.323801 VIETNAM | 1992 | 470841353.173193 (175 rows) COMMIT; COMMIT