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 '%red%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272034359.413063 ALGERIA | 1997 | 454036695.515399 ALGERIA | 1996 | 457260741.731656 ALGERIA | 1995 | 454850715.004326 ALGERIA | 1994 | 456929166.731851 ALGERIA | 1993 | 460458238.50949 ALGERIA | 1992 | 454790547.151346 ARGENTINA | 1998 | 277214292.127622 ARGENTINA | 1997 | 465845189.518795 ARGENTINA | 1996 | 477539633.447805 ARGENTINA | 1995 | 468867920.91095 ARGENTINA | 1994 | 468893917.910333 ARGENTINA | 1993 | 464940773.810806 ARGENTINA | 1992 | 469439107.238367 BRAZIL | 1998 | 272828106.58883 BRAZIL | 1997 | 465269814.608519 BRAZIL | 1996 | 470472501.054044 BRAZIL | 1995 | 472520942.90861 BRAZIL | 1994 | 462793278.208341 BRAZIL | 1993 | 467758250.078355 BRAZIL | 1992 | 467599955.845958 CANADA | 1998 | 282513242.521043 CANADA | 1997 | 477358428.105133 CANADA | 1996 | 483413150.462766 CANADA | 1995 | 485625502.932826 CANADA | 1994 | 487751264.307159 CANADA | 1993 | 474887591.204572 CANADA | 1992 | 477841443.945645 CHINA | 1998 | 281351717.482961 CHINA | 1997 | 475252084.103966 CHINA | 1996 | 468321915.571558 CHINA | 1995 | 466194482.441042 CHINA | 1994 | 469613779.214797 CHINA | 1993 | 471847825.519834 CHINA | 1992 | 478678527.657982 EGYPT | 1998 | 275964093.982418 EGYPT | 1997 | 468128528.809162 EGYPT | 1996 | 469533609.181004 EGYPT | 1995 | 468023914.808519 EGYPT | 1994 | 472064785.835587 EGYPT | 1993 | 465930256.285947 EGYPT | 1992 | 473029462.358578 ETHIOPIA | 1998 | 269802188.311546 ETHIOPIA | 1997 | 461162671.212695 ETHIOPIA | 1996 | 464445380.707219 ETHIOPIA | 1995 | 469347291.699669 ETHIOPIA | 1994 | 461977536.163715 ETHIOPIA | 1993 | 464514165.288511 ETHIOPIA | 1992 | 455759879.520619 FRANCE | 1998 | 279065594.63246 FRANCE | 1997 | 474945434.175745 FRANCE | 1996 | 478362967.599616 FRANCE | 1995 | 474114445.686049 FRANCE | 1994 | 474765221.185481 FRANCE | 1993 | 467448951.52244 FRANCE | 1992 | 470010374.50214 GERMANY | 1998 | 280060828.942667 GERMANY | 1997 | 475756948.14949 GERMANY | 1996 | 481183120.777508 GERMANY | 1995 | 480216873.272505 GERMANY | 1994 | 481782490.4238 GERMANY | 1993 | 478964264.385838 GERMANY | 1992 | 478475228.547148 INDIA | 1998 | 280680708.506149 INDIA | 1997 | 473612223.587808 INDIA | 1996 | 473874624.708476 INDIA | 1995 | 475907930.825271 INDIA | 1994 | 476378867.146533 INDIA | 1993 | 467450168.191156 INDIA | 1992 | 481429959.786766 INDONESIA | 1998 | 273604793.261121 INDONESIA | 1997 | 457346315.906794 INDONESIA | 1996 | 463530029.397374 INDONESIA | 1995 | 459186799.832818 INDONESIA | 1994 | 461708197.049383 INDONESIA | 1993 | 459325558.124355 INDONESIA | 1992 | 451169364.321373 IRAN | 1998 | 271119227.865346 IRAN | 1997 | 464983715.278629 IRAN | 1996 | 463700233.776218 IRAN | 1995 | 463783935.761823 IRAN | 1994 | 466662916.868612 IRAN | 1993 | 461490365.699193 IRAN | 1992 | 465567998.253272 IRAQ | 1998 | 285810209.564129 IRAQ | 1997 | 470701619.977337 IRAQ | 1996 | 487328773.184162 IRAQ | 1995 | 476154822.933195 IRAQ | 1994 | 474193811.096821 IRAQ | 1993 | 480662923.960209 IRAQ | 1992 | 475555380.640466 JAPAN | 1998 | 276776879.085751 JAPAN | 1997 | 465779718.937004 JAPAN | 1996 | 459647308.003162 JAPAN | 1995 | 469261170.64697 JAPAN | 1994 | 463729175.619039 JAPAN | 1993 | 469561539.27102 JAPAN | 1992 | 463793727.809705 JORDAN | 1998 | 270683228.886266 JORDAN | 1997 | 457324633.85077 JORDAN | 1996 | 459714036.357417 JORDAN | 1995 | 454871146.226178 JORDAN | 1994 | 459280654.444095 JORDAN | 1993 | 457049401.61344 JORDAN | 1992 | 459517891.484373 KENYA | 1998 | 271766661.858322 KENYA | 1997 | 467792434.129879 KENYA | 1996 | 476432907.915332 KENYA | 1995 | 474297293.371826 KENYA | 1994 | 479368269.060749 KENYA | 1993 | 471070364.994478 KENYA | 1992 | 472121878.096893 MOROCCO | 1998 | 279008977.456853 MOROCCO | 1997 | 465654116.274903 MOROCCO | 1996 | 469422477.000652 MOROCCO | 1995 | 470689235.310423 MOROCCO | 1994 | 472973507.705711 MOROCCO | 1993 | 464384303.979714 MOROCCO | 1992 | 474651262.973111 MOZAMBIQUE | 1998 | 270518556.214689 MOZAMBIQUE | 1997 | 458972438.18837 MOZAMBIQUE | 1996 | 453276070.014607 MOZAMBIQUE | 1995 | 457432139.220475 MOZAMBIQUE | 1994 | 459956591.455698 MOZAMBIQUE | 1993 | 451072803.153377 MOZAMBIQUE | 1992 | 454146685.313608 PERU | 1998 | 280092851.003502 PERU | 1997 | 468315147.022714 PERU | 1996 | 469064178.808563 PERU | 1995 | 466272573.781636 PERU | 1994 | 471411946.962364 PERU | 1993 | 458285491.416557 PERU | 1992 | 466194872.548154 ROMANIA | 1998 | 269753379.080533 ROMANIA | 1997 | 462304871.789831 ROMANIA | 1996 | 466304902.180585 ROMANIA | 1995 | 461353068.689429 ROMANIA | 1994 | 453174591.213128 ROMANIA | 1993 | 461467956.68741 ROMANIA | 1992 | 463173023.426982 RUSSIA | 1998 | 276510516.960715 RUSSIA | 1997 | 471762150.358147 RUSSIA | 1996 | 471002444.987934 RUSSIA | 1995 | 477601647.70429 RUSSIA | 1994 | 462968895.555913 RUSSIA | 1993 | 474586596.04966 RUSSIA | 1992 | 476365663.080422 SAUDI ARABIA | 1998 | 270917162.84897 SAUDI ARABIA | 1997 | 463620609.536516 SAUDI ARABIA | 1996 | 467013410.466768 SAUDI ARABIA | 1995 | 465153910.933186 SAUDI ARABIA | 1994 | 469863216.245274 SAUDI ARABIA | 1993 | 461046065.356215 SAUDI ARABIA | 1992 | 470000956.536432 UNITED KINGDOM | 1998 | 272548013.747051 UNITED KINGDOM | 1997 | 466106615.212178 UNITED KINGDOM | 1996 | 469579060.777836 UNITED KINGDOM | 1995 | 465343498.481337 UNITED KINGDOM | 1994 | 465463069.467462 UNITED KINGDOM | 1993 | 474036264.581694 UNITED KINGDOM | 1992 | 465915775.749986 UNITED STATES | 1998 | 274779867.370581 UNITED STATES | 1997 | 473083307.668409 UNITED STATES | 1996 | 467548792.237549 UNITED STATES | 1995 | 462041569.715318 UNITED STATES | 1994 | 468028307.424501 UNITED STATES | 1993 | 479008323.418424 UNITED STATES | 1992 | 472840759.226261 VIETNAM | 1998 | 276639341.1458 VIETNAM | 1997 | 472902517.305777 VIETNAM | 1996 | 476908496.994812 VIETNAM | 1995 | 468466674.897324 VIETNAM | 1994 | 468537010.993553 VIETNAM | 1993 | 464900762.301205 VIETNAM | 1992 | 476382793.338896 (175 rows) COMMIT; COMMIT