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 '%thistle%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267202686.806888 ALGERIA | 1997 | 463695028.962465 ALGERIA | 1996 | 455821947.019027 ALGERIA | 1995 | 457356727.687853 ALGERIA | 1994 | 455000299.841335 ALGERIA | 1993 | 452862908.440991 ALGERIA | 1992 | 456965390.938493 ARGENTINA | 1998 | 274017135.556354 ARGENTINA | 1997 | 475605161.841144 ARGENTINA | 1996 | 467535419.592921 ARGENTINA | 1995 | 467571165.512228 ARGENTINA | 1994 | 470044114.694276 ARGENTINA | 1993 | 474955894.676687 ARGENTINA | 1992 | 469163807.696578 BRAZIL | 1998 | 269946473.173394 BRAZIL | 1997 | 462088908.125522 BRAZIL | 1996 | 459190529.836948 BRAZIL | 1995 | 458021868.594873 BRAZIL | 1994 | 457290050.728967 BRAZIL | 1993 | 460762353.733108 BRAZIL | 1992 | 466728580.960344 CANADA | 1998 | 276691623.408305 CANADA | 1997 | 477094556.841894 CANADA | 1996 | 478981547.572871 CANADA | 1995 | 472857779.653567 CANADA | 1994 | 483624089.3552 CANADA | 1993 | 480136389.22022 CANADA | 1992 | 481595663.652542 CHINA | 1998 | 270573876.103742 CHINA | 1997 | 457784505.606893 CHINA | 1996 | 459595350.424707 CHINA | 1995 | 457382614.759555 CHINA | 1994 | 459464201.172645 CHINA | 1993 | 462653269.384826 CHINA | 1992 | 464322926.129779 EGYPT | 1998 | 268799664.829736 EGYPT | 1997 | 464399830.934034 EGYPT | 1996 | 458841588.665089 EGYPT | 1995 | 468100692.020307 EGYPT | 1994 | 459418648.443462 EGYPT | 1993 | 460653066.79018 EGYPT | 1992 | 463715310.310912 ETHIOPIA | 1998 | 266508257.614784 ETHIOPIA | 1997 | 458769022.178827 ETHIOPIA | 1996 | 455726231.636282 ETHIOPIA | 1995 | 458822049.588819 ETHIOPIA | 1994 | 460333588.080636 ETHIOPIA | 1993 | 453635369.870863 ETHIOPIA | 1992 | 456888017.989441 FRANCE | 1998 | 271858896.171311 FRANCE | 1997 | 457819740.627758 FRANCE | 1996 | 461255258.792777 FRANCE | 1995 | 454509060.329594 FRANCE | 1994 | 453559017.319928 FRANCE | 1993 | 459325627.435192 FRANCE | 1992 | 462746660.215099 GERMANY | 1998 | 270860911.500423 GERMANY | 1997 | 465307392.235986 GERMANY | 1996 | 464457863.882844 GERMANY | 1995 | 465569456.107642 GERMANY | 1994 | 467902646.975489 GERMANY | 1993 | 470388646.104204 GERMANY | 1992 | 467213346.747367 INDIA | 1998 | 274646834.938228 INDIA | 1997 | 475293252.378646 INDIA | 1996 | 474498659.489215 INDIA | 1995 | 477926470.756631 INDIA | 1994 | 474817613.732317 INDIA | 1993 | 475010656.940884 INDIA | 1992 | 470474041.482754 INDONESIA | 1998 | 272213241.801514 INDONESIA | 1997 | 449664549.109429 INDONESIA | 1996 | 455253995.999594 INDONESIA | 1995 | 455235296.580822 INDONESIA | 1994 | 455377016.574211 INDONESIA | 1993 | 461749407.948267 INDONESIA | 1992 | 456945585.413239 IRAN | 1998 | 264788518.712278 IRAN | 1997 | 469835630.715554 IRAN | 1996 | 461835934.992288 IRAN | 1995 | 461860741.525327 IRAN | 1994 | 459246591.321653 IRAN | 1993 | 459646378.020181 IRAN | 1992 | 465003196.521143 IRAQ | 1998 | 272234188.197313 IRAQ | 1997 | 466421183.511045 IRAQ | 1996 | 471480130.417455 IRAQ | 1995 | 464352388.912361 IRAQ | 1994 | 466785695.278663 IRAQ | 1993 | 462072428.238237 IRAQ | 1992 | 465609988.744645 JAPAN | 1998 | 270181540.003237 JAPAN | 1997 | 451450270.496037 JAPAN | 1996 | 455789673.776238 JAPAN | 1995 | 454853538.361933 JAPAN | 1994 | 452325562.006407 JAPAN | 1993 | 464273276.9564 JAPAN | 1992 | 451541637.442903 JORDAN | 1998 | 268161839.166221 JORDAN | 1997 | 457209682.607585 JORDAN | 1996 | 458783066.969429 JORDAN | 1995 | 447179372.968153 JORDAN | 1994 | 455983688.130622 JORDAN | 1993 | 453718332.821385 JORDAN | 1992 | 457453335.492144 KENYA | 1998 | 270736150.297687 KENYA | 1997 | 464743058.041333 KENYA | 1996 | 473386286.922925 KENYA | 1995 | 466729913.37295 KENYA | 1994 | 466111517.186791 KENYA | 1993 | 474919229.591219 KENYA | 1992 | 471871196.20736 MOROCCO | 1998 | 266885696.113849 MOROCCO | 1997 | 462887608.015079 MOROCCO | 1996 | 462988594.343696 MOROCCO | 1995 | 458544209.553558 MOROCCO | 1994 | 458766675.165111 MOROCCO | 1993 | 465536475.57487 MOROCCO | 1992 | 465913607.19525 MOZAMBIQUE | 1998 | 269215084.325189 MOZAMBIQUE | 1997 | 449770041.649204 MOZAMBIQUE | 1996 | 455019341.725944 MOZAMBIQUE | 1995 | 452139768.062162 MOZAMBIQUE | 1994 | 457800735.522128 MOZAMBIQUE | 1993 | 455523447.340024 MOZAMBIQUE | 1992 | 448110810.730023 PERU | 1998 | 269913974.799752 PERU | 1997 | 464341278.126491 PERU | 1996 | 467402219.018368 PERU | 1995 | 456229645.394601 PERU | 1994 | 466919640.271477 PERU | 1993 | 465165186.701436 PERU | 1992 | 460736401.756872 ROMANIA | 1998 | 268367877.554865 ROMANIA | 1997 | 458738773.934743 ROMANIA | 1996 | 461301962.8229 ROMANIA | 1995 | 456541575.31685 ROMANIA | 1994 | 464391831.165417 ROMANIA | 1993 | 460282226.412564 ROMANIA | 1992 | 465297024.027434 RUSSIA | 1998 | 280383580.7377 RUSSIA | 1997 | 467265591.690707 RUSSIA | 1996 | 467131538.261166 RUSSIA | 1995 | 472389734.436482 RUSSIA | 1994 | 470401638.01935 RUSSIA | 1993 | 476248292.491472 RUSSIA | 1992 | 466617513.621777 SAUDI ARABIA | 1998 | 270595516.125132 SAUDI ARABIA | 1997 | 457215541.505693 SAUDI ARABIA | 1996 | 465506482.684941 SAUDI ARABIA | 1995 | 467980751.027958 SAUDI ARABIA | 1994 | 464031956.71081 SAUDI ARABIA | 1993 | 463162385.833206 SAUDI ARABIA | 1992 | 467693641.427991 UNITED KINGDOM | 1998 | 267002100.663279 UNITED KINGDOM | 1997 | 460436615.956458 UNITED KINGDOM | 1996 | 458497321.411569 UNITED KINGDOM | 1995 | 462308121.357981 UNITED KINGDOM | 1994 | 462639788.6545 UNITED KINGDOM | 1993 | 467162944.947741 UNITED KINGDOM | 1992 | 457512053.207942 UNITED STATES | 1998 | 274053587.035567 UNITED STATES | 1997 | 468209423.908856 UNITED STATES | 1996 | 477397292.792608 UNITED STATES | 1995 | 465751416.390523 UNITED STATES | 1994 | 474934432.127013 UNITED STATES | 1993 | 469272504.982792 UNITED STATES | 1992 | 472628189.34509 VIETNAM | 1998 | 271466712.919547 VIETNAM | 1997 | 465585557.435729 VIETNAM | 1996 | 461212920.066651 VIETNAM | 1995 | 460070186.770617 VIETNAM | 1994 | 465265029.218893 VIETNAM | 1993 | 467447191.485796 VIETNAM | 1992 | 462258246.122828 (175 rows) COMMIT; COMMIT