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 '%frosted%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267874089.456336 ALGERIA | 1997 | 453905582.532843 ALGERIA | 1996 | 449138436.628763 ALGERIA | 1995 | 450664350.286597 ALGERIA | 1994 | 453669071.858962 ALGERIA | 1993 | 446408847.72812 ALGERIA | 1992 | 449938423.996122 ARGENTINA | 1998 | 279944488.180192 ARGENTINA | 1997 | 470555044.340728 ARGENTINA | 1996 | 472906366.481261 ARGENTINA | 1995 | 472865731.911924 ARGENTINA | 1994 | 472940467.367854 ARGENTINA | 1993 | 466375737.610434 ARGENTINA | 1992 | 464585959.327383 BRAZIL | 1998 | 271295801.480752 BRAZIL | 1997 | 461987652.434434 BRAZIL | 1996 | 465166300.493839 BRAZIL | 1995 | 458646054.283767 BRAZIL | 1994 | 460215196.948959 BRAZIL | 1993 | 461392801.672914 BRAZIL | 1992 | 467149270.921688 CANADA | 1998 | 285746608.403939 CANADA | 1997 | 485175202.784225 CANADA | 1996 | 486206561.758778 CANADA | 1995 | 489968054.728541 CANADA | 1994 | 493355914.614122 CANADA | 1993 | 485015150.880004 CANADA | 1992 | 489524010.191886 CHINA | 1998 | 266237671.636045 CHINA | 1997 | 465539118.324859 CHINA | 1996 | 472301669.603375 CHINA | 1995 | 464922976.762733 CHINA | 1994 | 464641667.350132 CHINA | 1993 | 466167335.399227 CHINA | 1992 | 466591563.109797 EGYPT | 1998 | 278373033.603501 EGYPT | 1997 | 473791356.43526 EGYPT | 1996 | 489355021.397654 EGYPT | 1995 | 483745370.277675 EGYPT | 1994 | 481081751.547307 EGYPT | 1993 | 481326111.072259 EGYPT | 1992 | 473931304.00332 ETHIOPIA | 1998 | 265938276.993839 ETHIOPIA | 1997 | 456759150.684436 ETHIOPIA | 1996 | 461986203.472946 ETHIOPIA | 1995 | 467555838.145522 ETHIOPIA | 1994 | 457929923.540541 ETHIOPIA | 1993 | 467047747.582292 ETHIOPIA | 1992 | 468258915.374092 FRANCE | 1998 | 269076085.315764 FRANCE | 1997 | 448288093.766741 FRANCE | 1996 | 458704785.940685 FRANCE | 1995 | 456783941.826049 FRANCE | 1994 | 455237941.525305 FRANCE | 1993 | 460995174.916235 FRANCE | 1992 | 458815074.939115 GERMANY | 1998 | 278464818.779665 GERMANY | 1997 | 477924554.652997 GERMANY | 1996 | 473976204.902643 GERMANY | 1995 | 476567899.19729 GERMANY | 1994 | 472365963.552418 GERMANY | 1993 | 469181526.685183 GERMANY | 1992 | 471687352.219546 INDIA | 1998 | 280068198.98898 INDIA | 1997 | 476404110.194302 INDIA | 1996 | 484359439.885701 INDIA | 1995 | 470864304.02367 INDIA | 1994 | 472256898.324302 INDIA | 1993 | 476033027.066712 INDIA | 1992 | 477886695.249084 INDONESIA | 1998 | 268407486.701617 INDONESIA | 1997 | 461242011.952585 INDONESIA | 1996 | 469688215.414386 INDONESIA | 1995 | 460409719.42696 INDONESIA | 1994 | 465672047.93047 INDONESIA | 1993 | 467014572.599641 INDONESIA | 1992 | 458449218.625803 IRAN | 1998 | 271209109.724417 IRAN | 1997 | 461841426.105393 IRAN | 1996 | 469742237.082261 IRAN | 1995 | 471406081.617357 IRAN | 1994 | 462054535.434878 IRAN | 1993 | 456417924.208933 IRAN | 1992 | 464423038.260286 IRAQ | 1998 | 280802281.062927 IRAQ | 1997 | 474918089.800878 IRAQ | 1996 | 477453990.082435 IRAQ | 1995 | 483565821.22767 IRAQ | 1994 | 480119539.19324 IRAQ | 1993 | 478872459.459471 IRAQ | 1992 | 482363686.571243 JAPAN | 1998 | 277851766.059214 JAPAN | 1997 | 468317008.20974 JAPAN | 1996 | 465812469.036374 JAPAN | 1995 | 467016198.201831 JAPAN | 1994 | 465431239.168993 JAPAN | 1993 | 462489285.574554 JAPAN | 1992 | 468525070.645531 JORDAN | 1998 | 265684692.467272 JORDAN | 1997 | 459592989.002133 JORDAN | 1996 | 457488485.896085 JORDAN | 1995 | 458208100.241335 JORDAN | 1994 | 458726122.083297 JORDAN | 1993 | 456983564.478143 JORDAN | 1992 | 454565311.549638 KENYA | 1998 | 276934097.002305 KENYA | 1997 | 465301144.400347 KENYA | 1996 | 467423178.700265 KENYA | 1995 | 463648905.28821 KENYA | 1994 | 462834350.403684 KENYA | 1993 | 464427793.10492 KENYA | 1992 | 465559477.136643 MOROCCO | 1998 | 274815928.015225 MOROCCO | 1997 | 464286761.383057 MOROCCO | 1996 | 463016025.479733 MOROCCO | 1995 | 463941927.036498 MOROCCO | 1994 | 463362721.6519 MOROCCO | 1993 | 468383199.699349 MOROCCO | 1992 | 467493151.630795 MOZAMBIQUE | 1998 | 274334424.590329 MOZAMBIQUE | 1997 | 455198051.256745 MOZAMBIQUE | 1996 | 459948678.380841 MOZAMBIQUE | 1995 | 459511029.641794 MOZAMBIQUE | 1994 | 454126987.783693 MOZAMBIQUE | 1993 | 450875158.475443 MOZAMBIQUE | 1992 | 457789274.186781 PERU | 1998 | 273580987.70374 PERU | 1997 | 456883701.015687 PERU | 1996 | 463489100.166478 PERU | 1995 | 461851915.456469 PERU | 1994 | 457172221.575844 PERU | 1993 | 459546865.723982 PERU | 1992 | 462713298.647051 ROMANIA | 1998 | 280333916.534435 ROMANIA | 1997 | 469889927.568161 ROMANIA | 1996 | 475630014.451504 ROMANIA | 1995 | 472854985.29241 ROMANIA | 1994 | 474240920.782845 ROMANIA | 1993 | 477587244.062978 ROMANIA | 1992 | 472749119.889979 RUSSIA | 1998 | 276023905.537262 RUSSIA | 1997 | 475070439.95912 RUSSIA | 1996 | 476035188.315865 RUSSIA | 1995 | 473695625.88142 RUSSIA | 1994 | 469187619.164972 RUSSIA | 1993 | 470131683.38911 RUSSIA | 1992 | 478247923.692411 SAUDI ARABIA | 1998 | 279357682.788244 SAUDI ARABIA | 1997 | 469315468.191287 SAUDI ARABIA | 1996 | 471731874.637047 SAUDI ARABIA | 1995 | 475959214.168193 SAUDI ARABIA | 1994 | 473788058.25253 SAUDI ARABIA | 1993 | 475628470.370643 SAUDI ARABIA | 1992 | 472372789.051817 UNITED KINGDOM | 1998 | 271826679.493605 UNITED KINGDOM | 1997 | 456832709.213302 UNITED KINGDOM | 1996 | 463800208.071746 UNITED KINGDOM | 1995 | 461539747.796717 UNITED KINGDOM | 1994 | 464971528.96968 UNITED KINGDOM | 1993 | 459826110.371338 UNITED KINGDOM | 1992 | 463399251.417318 UNITED STATES | 1998 | 274835068.674107 UNITED STATES | 1997 | 468533465.584352 UNITED STATES | 1996 | 473149462.055077 UNITED STATES | 1995 | 472437512.532775 UNITED STATES | 1994 | 474836714.636463 UNITED STATES | 1993 | 466543859.099456 UNITED STATES | 1992 | 476459585.72165 VIETNAM | 1998 | 272719434.817032 VIETNAM | 1997 | 462243007.164507 VIETNAM | 1996 | 469148551.117691 VIETNAM | 1995 | 464858236.181466 VIETNAM | 1994 | 465234483.906187 VIETNAM | 1993 | 472562959.471374 VIETNAM | 1992 | 466046202.373508 (175 rows) COMMIT; COMMIT