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 '%antique%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 266524135.179878 ALGERIA | 1997 | 447844465.20364 ALGERIA | 1996 | 453199685.643472 ALGERIA | 1995 | 456972213.783149 ALGERIA | 1994 | 444556611.954347 ALGERIA | 1993 | 452357398.323634 ALGERIA | 1992 | 451638073.336688 ARGENTINA | 1998 | 279203774.645923 ARGENTINA | 1997 | 473580671.040654 ARGENTINA | 1996 | 469323317.117697 ARGENTINA | 1995 | 474235535.506592 ARGENTINA | 1994 | 471996762.063336 ARGENTINA | 1993 | 468169771.717054 ARGENTINA | 1992 | 472783560.76229 BRAZIL | 1998 | 270475708.265941 BRAZIL | 1997 | 464468545.417823 BRAZIL | 1996 | 459236306.489668 BRAZIL | 1995 | 465068894.523045 BRAZIL | 1994 | 460887319.863156 BRAZIL | 1993 | 459873903.271802 BRAZIL | 1992 | 459416347.314248 CANADA | 1998 | 277396718.794538 CANADA | 1997 | 474567262.32116 CANADA | 1996 | 468912145.960543 CANADA | 1995 | 468664895.772523 CANADA | 1994 | 480981690.96062 CANADA | 1993 | 479459815.654978 CANADA | 1992 | 465240383.254182 CHINA | 1998 | 269267168.44802 CHINA | 1997 | 456814185.633232 CHINA | 1996 | 461234845.825389 CHINA | 1995 | 458780077.720727 CHINA | 1994 | 456103880.161723 CHINA | 1993 | 469744886.873402 CHINA | 1992 | 459411300.591033 EGYPT | 1998 | 275200702.820701 EGYPT | 1997 | 469910810.528557 EGYPT | 1996 | 472026194.525514 EGYPT | 1995 | 470993712.833437 EGYPT | 1994 | 468922463.107768 EGYPT | 1993 | 469060067.999304 EGYPT | 1992 | 460076462.117024 ETHIOPIA | 1998 | 265077230.800864 ETHIOPIA | 1997 | 456127566.537565 ETHIOPIA | 1996 | 460466626.541921 ETHIOPIA | 1995 | 456926907.886662 ETHIOPIA | 1994 | 464811204.47758 ETHIOPIA | 1993 | 461931018.601572 ETHIOPIA | 1992 | 456375108.484511 FRANCE | 1998 | 266963605.050982 FRANCE | 1997 | 456776005.297852 FRANCE | 1996 | 455960631.775809 FRANCE | 1995 | 453793688.683044 FRANCE | 1994 | 454320811.013743 FRANCE | 1993 | 453790913.838895 FRANCE | 1992 | 455954549.472514 GERMANY | 1998 | 285009380.495098 GERMANY | 1997 | 473128407.26389 GERMANY | 1996 | 474438327.779483 GERMANY | 1995 | 482150174.16508 GERMANY | 1994 | 470907935.236348 GERMANY | 1993 | 470034546.107849 GERMANY | 1992 | 469119972.944272 INDIA | 1998 | 272134948.336735 INDIA | 1997 | 467019842.251533 INDIA | 1996 | 481293414.890467 INDIA | 1995 | 472398521.848358 INDIA | 1994 | 472327816.657053 INDIA | 1993 | 470035641.723669 INDIA | 1992 | 472112909.341682 INDONESIA | 1998 | 273123747.59609 INDONESIA | 1997 | 463724225.227601 INDONESIA | 1996 | 463431168.945323 INDONESIA | 1995 | 463087394.246621 INDONESIA | 1994 | 469653586.010466 INDONESIA | 1993 | 464131346.725196 INDONESIA | 1992 | 469335191.918735 IRAN | 1998 | 275933164.225893 IRAN | 1997 | 464860235.664498 IRAN | 1996 | 477236216.746101 IRAN | 1995 | 457191235.215073 IRAN | 1994 | 463840209.974242 IRAN | 1993 | 469809754.946506 IRAN | 1992 | 471280706.688884 IRAQ | 1998 | 267467359.809265 IRAQ | 1997 | 456272300.93423 IRAQ | 1996 | 465154266.564767 IRAQ | 1995 | 451837292.309858 IRAQ | 1994 | 454715475.417142 IRAQ | 1993 | 457584401.720808 IRAQ | 1992 | 455532223.850991 JAPAN | 1998 | 272239378.831656 JAPAN | 1997 | 465548915.45855 JAPAN | 1996 | 461818188.416605 JAPAN | 1995 | 468363536.848851 JAPAN | 1994 | 464966209.993711 JAPAN | 1993 | 465201771.204136 JAPAN | 1992 | 459806382.779109 JORDAN | 1998 | 262821866.457948 JORDAN | 1997 | 451174242.920826 JORDAN | 1996 | 449205811.255938 JORDAN | 1995 | 454059564.79174 JORDAN | 1994 | 448940814.792554 JORDAN | 1993 | 454567428.755875 JORDAN | 1992 | 447520187.688414 KENYA | 1998 | 275563696.853943 KENYA | 1997 | 474737102.579141 KENYA | 1996 | 479478077.172464 KENYA | 1995 | 466161833.10834 KENYA | 1994 | 478219441.04341 KENYA | 1993 | 469989697.589687 KENYA | 1992 | 475623635.392135 MOROCCO | 1998 | 270211143.28984 MOROCCO | 1997 | 472373173.461323 MOROCCO | 1996 | 469251303.354845 MOROCCO | 1995 | 463816286.004116 MOROCCO | 1994 | 464870248.793174 MOROCCO | 1993 | 464242881.163486 MOROCCO | 1992 | 473994245.866902 MOZAMBIQUE | 1998 | 268345324.44363 MOZAMBIQUE | 1997 | 459191556.044065 MOZAMBIQUE | 1996 | 457765941.780668 MOZAMBIQUE | 1995 | 458547799.793177 MOZAMBIQUE | 1994 | 452643923.295158 MOZAMBIQUE | 1993 | 457381948.852575 MOZAMBIQUE | 1992 | 457354460.281418 PERU | 1998 | 269584730.505087 PERU | 1997 | 451299492.40347 PERU | 1996 | 456502938.460792 PERU | 1995 | 453839079.371578 PERU | 1994 | 451567016.662259 PERU | 1993 | 454880942.723309 PERU | 1992 | 459983286.41116 ROMANIA | 1998 | 271868379.283949 ROMANIA | 1997 | 469859890.659834 ROMANIA | 1996 | 467255222.752698 ROMANIA | 1995 | 467814759.614634 ROMANIA | 1994 | 463502100.273642 ROMANIA | 1993 | 463645413.802255 ROMANIA | 1992 | 460338201.121091 RUSSIA | 1998 | 272838620.664123 RUSSIA | 1997 | 468604540.41086 RUSSIA | 1996 | 474659552.051801 RUSSIA | 1995 | 465719200.154189 RUSSIA | 1994 | 462601638.14193 RUSSIA | 1993 | 466620434.127912 RUSSIA | 1992 | 468198591.782691 SAUDI ARABIA | 1998 | 272490158.014278 SAUDI ARABIA | 1997 | 465095962.69548 SAUDI ARABIA | 1996 | 471637819.106953 SAUDI ARABIA | 1995 | 471347473.087216 SAUDI ARABIA | 1994 | 470962441.528327 SAUDI ARABIA | 1993 | 467403444.816772 SAUDI ARABIA | 1992 | 471117250.553437 UNITED KINGDOM | 1998 | 274983172.538444 UNITED KINGDOM | 1997 | 461447807.390037 UNITED KINGDOM | 1996 | 459806640.448452 UNITED KINGDOM | 1995 | 459952954.500558 UNITED KINGDOM | 1994 | 454604992.156776 UNITED KINGDOM | 1993 | 463776119.258903 UNITED KINGDOM | 1992 | 461528587.920431 UNITED STATES | 1998 | 266894563.225693 UNITED STATES | 1997 | 462055832.150178 UNITED STATES | 1996 | 467028041.062811 UNITED STATES | 1995 | 471522585.508628 UNITED STATES | 1994 | 470217135.258898 UNITED STATES | 1993 | 464260923.99696 UNITED STATES | 1992 | 457519072.255805 VIETNAM | 1998 | 271120576.0439 VIETNAM | 1997 | 462881377.512245 VIETNAM | 1996 | 462064623.013433 VIETNAM | 1995 | 460839323.231796 VIETNAM | 1994 | 467694942.813584 VIETNAM | 1993 | 465695875.7502 VIETNAM | 1992 | 464620769.098782 (175 rows) COMMIT; COMMIT