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 '%chiffon%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272337703.906195 ALGERIA | 1997 | 454122273.634926 ALGERIA | 1996 | 461635964.42134 ALGERIA | 1995 | 460057931.572669 ALGERIA | 1994 | 453094090.67951 ALGERIA | 1993 | 459349007.715406 ALGERIA | 1992 | 453910874.154945 ARGENTINA | 1998 | 268784710.621127 ARGENTINA | 1997 | 472074920.399395 ARGENTINA | 1996 | 466569661.990909 ARGENTINA | 1995 | 462838853.61443 ARGENTINA | 1994 | 473721121.049083 ARGENTINA | 1993 | 467339036.623744 ARGENTINA | 1992 | 461731882.476821 BRAZIL | 1998 | 268600083.709569 BRAZIL | 1997 | 468442447.957497 BRAZIL | 1996 | 461940970.736268 BRAZIL | 1995 | 465545608.514134 BRAZIL | 1994 | 466832067.943571 BRAZIL | 1993 | 455485857.233476 BRAZIL | 1992 | 471977035.448191 CANADA | 1998 | 275851077.931844 CANADA | 1997 | 459794650.974449 CANADA | 1996 | 473575420.368488 CANADA | 1995 | 464472901.980194 CANADA | 1994 | 476031915.055327 CANADA | 1993 | 468382087.190949 CANADA | 1992 | 475305764.77049 CHINA | 1998 | 277464259.664254 CHINA | 1997 | 475951281.956518 CHINA | 1996 | 477717213.668757 CHINA | 1995 | 471741566.656838 CHINA | 1994 | 470845894.472624 CHINA | 1993 | 476592196.503909 CHINA | 1992 | 471930452.784777 EGYPT | 1998 | 272282939.738059 EGYPT | 1997 | 463715578.42458 EGYPT | 1996 | 472648040.597904 EGYPT | 1995 | 466360842.940058 EGYPT | 1994 | 460173352.803072 EGYPT | 1993 | 464679138.560731 EGYPT | 1992 | 461662231.592903 ETHIOPIA | 1998 | 268169716.921452 ETHIOPIA | 1997 | 458254978.472053 ETHIOPIA | 1996 | 455053825.143342 ETHIOPIA | 1995 | 458240520.036156 ETHIOPIA | 1994 | 454921265.239225 ETHIOPIA | 1993 | 459572343.979406 ETHIOPIA | 1992 | 457646624.430512 FRANCE | 1998 | 271844981.452928 FRANCE | 1997 | 468426339.371872 FRANCE | 1996 | 463942460.410011 FRANCE | 1995 | 470805973.743048 FRANCE | 1994 | 464715004.042308 FRANCE | 1993 | 464934769.782104 FRANCE | 1992 | 458237336.134423 GERMANY | 1998 | 278400919.455577 GERMANY | 1997 | 468419585.023535 GERMANY | 1996 | 472542079.342742 GERMANY | 1995 | 474736347.274091 GERMANY | 1994 | 474168016.621185 GERMANY | 1993 | 469551944.654633 GERMANY | 1992 | 471633381.087589 INDIA | 1998 | 277744844.168164 INDIA | 1997 | 471642507.174091 INDIA | 1996 | 477531621.112306 INDIA | 1995 | 470791925.530653 INDIA | 1994 | 474791645.792873 INDIA | 1993 | 471193724.710086 INDIA | 1992 | 478589206.182024 INDONESIA | 1998 | 276983771.873721 INDONESIA | 1997 | 462858558.314026 INDONESIA | 1996 | 468911615.105854 INDONESIA | 1995 | 455708258.033639 INDONESIA | 1994 | 465361927.295677 INDONESIA | 1993 | 466839936.741951 INDONESIA | 1992 | 464006768.521058 IRAN | 1998 | 271663309.317476 IRAN | 1997 | 463207812.190053 IRAN | 1996 | 459014893.282514 IRAN | 1995 | 463631408.240425 IRAN | 1994 | 459724484.061278 IRAN | 1993 | 459891309.528042 IRAN | 1992 | 467374258.496806 IRAQ | 1998 | 277129603.809613 IRAQ | 1997 | 464088698.325652 IRAQ | 1996 | 471097507.086426 IRAQ | 1995 | 471762113.162956 IRAQ | 1994 | 476074674.646607 IRAQ | 1993 | 476694175.666187 IRAQ | 1992 | 472836676.757727 JAPAN | 1998 | 278045660.966815 JAPAN | 1997 | 474213749.723899 JAPAN | 1996 | 469712611.001403 JAPAN | 1995 | 475577002.801465 JAPAN | 1994 | 473744416.541188 JAPAN | 1993 | 472341640.472526 JAPAN | 1992 | 474448140.53188 JORDAN | 1998 | 277317989.054735 JORDAN | 1997 | 461865768.463353 JORDAN | 1996 | 465564621.46834 JORDAN | 1995 | 464820304.526373 JORDAN | 1994 | 463471213.684327 JORDAN | 1993 | 466804525.884704 JORDAN | 1992 | 462750009.254098 KENYA | 1998 | 273306021.748725 KENYA | 1997 | 466853163.966088 KENYA | 1996 | 476398063.299995 KENYA | 1995 | 471525860.361014 KENYA | 1994 | 473227054.419947 KENYA | 1993 | 473967114.465818 KENYA | 1992 | 476044760.197562 MOROCCO | 1998 | 274463769.382593 MOROCCO | 1997 | 466915674.951482 MOROCCO | 1996 | 479539243.707205 MOROCCO | 1995 | 466967996.093777 MOROCCO | 1994 | 476610240.002677 MOROCCO | 1993 | 465805938.148144 MOROCCO | 1992 | 482569572.040938 MOZAMBIQUE | 1998 | 269370935.893244 MOZAMBIQUE | 1997 | 455249179.827209 MOZAMBIQUE | 1996 | 461976325.071307 MOZAMBIQUE | 1995 | 457331993.195693 MOZAMBIQUE | 1994 | 459591739.091953 MOZAMBIQUE | 1993 | 454098206.222306 MOZAMBIQUE | 1992 | 456451955.236618 PERU | 1998 | 275532626.546694 PERU | 1997 | 461953860.397935 PERU | 1996 | 464498615.091747 PERU | 1995 | 472306404.897295 PERU | 1994 | 461190376.696976 PERU | 1993 | 470299682.874869 PERU | 1992 | 461296158.737534 ROMANIA | 1998 | 278001117.80296 ROMANIA | 1997 | 462446719.298531 ROMANIA | 1996 | 464966485.59193 ROMANIA | 1995 | 463627353.580411 ROMANIA | 1994 | 470293411.797093 ROMANIA | 1993 | 466532881.945856 ROMANIA | 1992 | 469242736.179618 RUSSIA | 1998 | 274581216.691899 RUSSIA | 1997 | 467344086.989215 RUSSIA | 1996 | 466648074.131178 RUSSIA | 1995 | 470400868.193685 RUSSIA | 1994 | 471277935.77798 RUSSIA | 1993 | 470416148.961531 RUSSIA | 1992 | 471965924.910018 SAUDI ARABIA | 1998 | 272862728.938941 SAUDI ARABIA | 1997 | 461249179.121844 SAUDI ARABIA | 1996 | 465334047.553173 SAUDI ARABIA | 1995 | 458740639.529756 SAUDI ARABIA | 1994 | 455819111.26892 SAUDI ARABIA | 1993 | 461299679.756275 SAUDI ARABIA | 1992 | 463267605.195763 UNITED KINGDOM | 1998 | 271291595.849141 UNITED KINGDOM | 1997 | 459435758.18437 UNITED KINGDOM | 1996 | 470733049.407044 UNITED KINGDOM | 1995 | 461819296.462321 UNITED KINGDOM | 1994 | 458961121.565326 UNITED KINGDOM | 1993 | 463869797.539949 UNITED KINGDOM | 1992 | 463578923.757774 UNITED STATES | 1998 | 269653779.998985 UNITED STATES | 1997 | 464728112.301031 UNITED STATES | 1996 | 465185062.063192 UNITED STATES | 1995 | 462960173.896826 UNITED STATES | 1994 | 469299851.819042 UNITED STATES | 1993 | 463362741.103265 UNITED STATES | 1992 | 468724834.954648 VIETNAM | 1998 | 275597711.470461 VIETNAM | 1997 | 465832346.488604 VIETNAM | 1996 | 467396192.567933 VIETNAM | 1995 | 460938304.494879 VIETNAM | 1994 | 462601764.683488 VIETNAM | 1993 | 470058213.513338 VIETNAM | 1992 | 472494341.596193 (175 rows) COMMIT; COMMIT