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 '%snow%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267706355.815665 ALGERIA | 1997 | 446936694.786904 ALGERIA | 1996 | 468988316.410005 ALGERIA | 1995 | 463807438.903784 ALGERIA | 1994 | 461603870.163647 ALGERIA | 1993 | 462513462.450891 ALGERIA | 1992 | 462595184.875125 ARGENTINA | 1998 | 271166821.82985 ARGENTINA | 1997 | 467072876.570121 ARGENTINA | 1996 | 464077862.704317 ARGENTINA | 1995 | 458674362.182 ARGENTINA | 1994 | 475766922.617265 ARGENTINA | 1993 | 458224983.174109 ARGENTINA | 1992 | 465388120.683283 BRAZIL | 1998 | 272435577.422895 BRAZIL | 1997 | 460971103.98152 BRAZIL | 1996 | 463337567.676654 BRAZIL | 1995 | 461795672.340633 BRAZIL | 1994 | 468617406.019043 BRAZIL | 1993 | 462978890.533074 BRAZIL | 1992 | 462418660.137946 CANADA | 1998 | 282217580.265246 CANADA | 1997 | 484578050.970154 CANADA | 1996 | 489420944.143305 CANADA | 1995 | 486380531.887916 CANADA | 1994 | 484965310.668885 CANADA | 1993 | 480662261.977778 CANADA | 1992 | 485972975.456274 CHINA | 1998 | 274630468.399666 CHINA | 1997 | 468861435.091644 CHINA | 1996 | 462882349.68792 CHINA | 1995 | 460827550.079978 CHINA | 1994 | 465953324.207261 CHINA | 1993 | 463361966.581952 CHINA | 1992 | 465615952.332579 EGYPT | 1998 | 271241528.969532 EGYPT | 1997 | 461368015.032807 EGYPT | 1996 | 460497276.742485 EGYPT | 1995 | 466025439.990346 EGYPT | 1994 | 467843866.882323 EGYPT | 1993 | 466132520.035073 EGYPT | 1992 | 460766395.011607 ETHIOPIA | 1998 | 270961134.607583 ETHIOPIA | 1997 | 474060521.264888 ETHIOPIA | 1996 | 455920337.199562 ETHIOPIA | 1995 | 466874351.080203 ETHIOPIA | 1994 | 472340595.243269 ETHIOPIA | 1993 | 457712734.20644 ETHIOPIA | 1992 | 457437645.621916 FRANCE | 1998 | 270742369.030975 FRANCE | 1997 | 461227636.17222 FRANCE | 1996 | 460444426.289171 FRANCE | 1995 | 463606378.917529 FRANCE | 1994 | 463485417.67824 FRANCE | 1993 | 465143080.200286 FRANCE | 1992 | 461820785.184802 GERMANY | 1998 | 278935320.110544 GERMANY | 1997 | 478488328.571463 GERMANY | 1996 | 464216027.145926 GERMANY | 1995 | 477210307.31857 GERMANY | 1994 | 473450570.088071 GERMANY | 1993 | 475642074.11323 GERMANY | 1992 | 475818147.341216 INDIA | 1998 | 271881224.198031 INDIA | 1997 | 475467577.659106 INDIA | 1996 | 476607685.027027 INDIA | 1995 | 462489824.320327 INDIA | 1994 | 474584752.502576 INDIA | 1993 | 469940015.618872 INDIA | 1992 | 469492315.601941 INDONESIA | 1998 | 267661934.72473 INDONESIA | 1997 | 458131042.25592 INDONESIA | 1996 | 467256988.63525 INDONESIA | 1995 | 452390009.367479 INDONESIA | 1994 | 457081310.560678 INDONESIA | 1993 | 465883444.753431 INDONESIA | 1992 | 458120458.115168 IRAN | 1998 | 275935875.322804 IRAN | 1997 | 463486010.371758 IRAN | 1996 | 475251995.394301 IRAN | 1995 | 455680249.61933 IRAN | 1994 | 463515856.958302 IRAN | 1993 | 464144814.653344 IRAN | 1992 | 467760166.940171 IRAQ | 1998 | 288741287.01881 IRAQ | 1997 | 473447454.463232 IRAQ | 1996 | 485843635.597508 IRAQ | 1995 | 476246561.553638 IRAQ | 1994 | 481779826.486934 IRAQ | 1993 | 472211217.014463 IRAQ | 1992 | 480564955.937942 JAPAN | 1998 | 272152174.550933 JAPAN | 1997 | 466063020.175885 JAPAN | 1996 | 466110552.750374 JAPAN | 1995 | 471307729.71637 JAPAN | 1994 | 464409734.388372 JAPAN | 1993 | 459498799.441551 JAPAN | 1992 | 467159722.954251 JORDAN | 1998 | 268859788.53446 JORDAN | 1997 | 457830080.85637 JORDAN | 1996 | 460963453.845013 JORDAN | 1995 | 452240942.339712 JORDAN | 1994 | 459386187.946133 JORDAN | 1993 | 454285950.974405 JORDAN | 1992 | 460518538.243439 KENYA | 1998 | 280288197.475508 KENYA | 1997 | 468488822.846084 KENYA | 1996 | 479819640.440756 KENYA | 1995 | 471739094.760084 KENYA | 1994 | 477616640.261161 KENYA | 1993 | 468848126.980388 KENYA | 1992 | 474937561.296334 MOROCCO | 1998 | 269150119.298227 MOROCCO | 1997 | 466504535.31554 MOROCCO | 1996 | 465064029.887431 MOROCCO | 1995 | 462454871.195668 MOROCCO | 1994 | 470940200.866771 MOROCCO | 1993 | 464242538.887669 MOROCCO | 1992 | 459968803.465505 MOZAMBIQUE | 1998 | 266843797.190013 MOZAMBIQUE | 1997 | 458319657.40062 MOZAMBIQUE | 1996 | 457852290.222427 MOZAMBIQUE | 1995 | 456463555.115986 MOZAMBIQUE | 1994 | 459723463.097504 MOZAMBIQUE | 1993 | 464744886.783252 MOZAMBIQUE | 1992 | 455082151.328845 PERU | 1998 | 273996353.325738 PERU | 1997 | 467701133.048141 PERU | 1996 | 466974585.247365 PERU | 1995 | 479917901.939767 PERU | 1994 | 469669722.906475 PERU | 1993 | 465728979.323667 PERU | 1992 | 470354556.279436 ROMANIA | 1998 | 270997550.721076 ROMANIA | 1997 | 465340867.478331 ROMANIA | 1996 | 475170167.70109 ROMANIA | 1995 | 473827440.239171 ROMANIA | 1994 | 466801517.461428 ROMANIA | 1993 | 469653601.637352 ROMANIA | 1992 | 464646081.186968 RUSSIA | 1998 | 283755927.756782 RUSSIA | 1997 | 478651887.003681 RUSSIA | 1996 | 479175577.855358 RUSSIA | 1995 | 464726998.463508 RUSSIA | 1994 | 476360912.985543 RUSSIA | 1993 | 470062510.868968 RUSSIA | 1992 | 470691070.417938 SAUDI ARABIA | 1998 | 274559062.106391 SAUDI ARABIA | 1997 | 465805600.497149 SAUDI ARABIA | 1996 | 468472595.441395 SAUDI ARABIA | 1995 | 467017366.558109 SAUDI ARABIA | 1994 | 467767333.536146 SAUDI ARABIA | 1993 | 459606337.688815 SAUDI ARABIA | 1992 | 470074149.542766 UNITED KINGDOM | 1998 | 267492128.283989 UNITED KINGDOM | 1997 | 463165666.119936 UNITED KINGDOM | 1996 | 458329250.954027 UNITED KINGDOM | 1995 | 456289493.278738 UNITED KINGDOM | 1994 | 457462826.879342 UNITED KINGDOM | 1993 | 465523443.455252 UNITED KINGDOM | 1992 | 452967218.290828 UNITED STATES | 1998 | 271404244.090182 UNITED STATES | 1997 | 471330478.168985 UNITED STATES | 1996 | 462033796.405828 UNITED STATES | 1995 | 465930557.143379 UNITED STATES | 1994 | 461672763.007322 UNITED STATES | 1993 | 471017207.867329 UNITED STATES | 1992 | 468369027.679347 VIETNAM | 1998 | 275001566.005611 VIETNAM | 1997 | 463901412.941443 VIETNAM | 1996 | 471599551.981955 VIETNAM | 1995 | 466064865.168001 VIETNAM | 1994 | 469241572.497276 VIETNAM | 1993 | 458064153.194621 VIETNAM | 1992 | 461009028.366196 (175 rows) COMMIT; COMMIT