CREATE TABLE current_funds ( fundid integer PRIMARY KEY, cnum integer NOT NULL, UNIQUE (cnum) ) ; CREATE TABLE retirement_survey ( fundid integer NOT NULL REFERENCES current_funds (fundid), "date" date, dh_simple integer NOT NULL, ha_simple integer DEFAULT 0 ) ; CREATE TABLE research_complex ( cnum integer REFERENCES current_funds (cnum), name varchar(50) ) ; CREATE TABLE research_retirement_simple_ira ( cnum integer REFERENCES current_funds (cnum), "date" date, assets integer ) ; INSERT INTO current_funds (fundid, cnum) VALUES (2, 2), (7, 7), (14, 14), (15, 15), (35, 35), (38, 38), (43, 43), (45, 45), (46, 46), (47, 47), (49, 49), (51, 51), (55, 55), (59, 59), (68, 68), (71, 71), (80, 80), (85, 85), (88, 88), (91, 91), (102, 102), (110, 110), (115, 115), (130, 130), (143, 143), (149, 149), (153, 153), (156, 156), (169, 169), (200, 200), (207, 207), (226, 226), (236, 236), (275, 275), (280, 280), (286, 286), (302, 302), (304, 304), (379, 379), (530, 530) ; INSERT INTO research_complex (cnum, name) VALUES (2, 'Lord, Abbett & Co. LLC'), (7, 'Capital, Research & Management'), (14, 'Principal'), (15, 'Bridges'), (35, 'Delaware Investments'), (38, 'Bank of New York Mellon/Dreyfus Corporation'), (43, 'Neuberger Berman Management LLC'), (45, 'Federated Investors'), (46, 'Fidelity Investments'), (47, 'Foresters Financial'), (49, 'Calvert Investments'), (51, 'Franklin Templeton Investments'), (55, 'William Blair Funds'), (59, 'Columbia Threadneedle Investments'), (68, 'MFS Investment Management'), (71, 'Nationwide Funds'), (80, 'OppenheimerFunds/MassMutual'), (85, 'Pioneer Investment Management USA Inc.'), (88, 'T. Rowe Price'), (91, 'Putnam Funds'), (102, 'State Farm'), (110, 'Waddell & Reed Funds'), (115, 'Vanguard Group'), (130, 'USAA'), (143, 'Invesco'), (149, 'Affiliated Managers Group'), (153, 'Goldman Sachs & Co.'), (156, 'TCW Funds, Inc.'), (169, 'Davis Selected Advisers, L.P.'), (200, 'Deutsche Asset Management'), (207, 'Hartford Funds'), (226, 'Meeder Asset Management, Inc.'), (236, 'Nuveen Investments'), (275, 'SunAmerica Group'), (280, 'Janus'), (286, 'Legg Mason'), (302, 'Transamerica Asset Management'), (304, 'John Hancock Investments'), (379, 'JP Morgan Chase & Co.'), (530, 'Schwab Funds, Laudus Funds & Schwab ETFs') ; INSERT INTO retirement_survey (fundid, "date", dh_simple, ha_simple) VALUES (2, '20160930', 329929, 0), (14, '20160930', 867280, 0), (15, '20160930', 430, 0), (35, '20160930', 147950, 0), (38, '20160930', 1417, 0), (43, '20160930', 14273, 0), (46, '20160930', 7676456, 0), (47, '20160930', 78766, 0), (49, '20160930', 76591, 0), (55, '20160930', 233, 0), (59, '20160930', 138908, 0), (68, '20160930', 1551971, 0), (71, '20160930', 10183, 0), (80, '20160930', 4101977, 0), (88, '20160930', 1905810, 0), (91, '20160930', 169569, 0), (102, '20160930', 999378, 0), (110, '20160930', 700310, 0), (115, '20160930', 6938415, 0), (130, '20160930', 169139, 0), (143, '20160930', 1822865, 0), (149, '20160930', 102, 0), (153, '20160930', 182038, 0), (156, '20160930', 3, 0), (169, '20160930', 16633, 0), (200, '20160930', 236911, 0), (207, '20160930', 1047561, 0), (226, '20160930', 1487, 0), (236, '20160930', 33764, 0), (275, '20160930', 69726, 0), (280, '20160930', 1899, 0), (286, '20160930', 5533, 0), (302, '20160930', 308727, 0) ; INSERT INTO research_retirement_simple_ira (cnum, date, assets) VALUES (2, '20160930', 303026683), (7, '20160930', 20000000), (14, '20160930', 777135509), (45, '20160930', 67583161), (46, '20160930', 20000000), (47, '20160930', 9999), (51, '20160930', 1848513179), (59, '20160930', 138678936), (68, '20160930', 1518052790), (80, '20160930', 20000000), (85, '20160930', 289936684), (88, '20160930', 1921051937), (91, '20160930', 20000000), (102, '20160930', 1026211854), (110, '20160930', 560233847), (115, '20160930', 20000000), (130, '20160930', 168430000), (143, '20160930', 1798640460), (207, '20160930', 586878824), (304, '20160930', 342692928), (379, '20160930', 148135910), (530, '20160930', 20000000) ;

SELECT cf.cnum as cnum, rc.name as name, SUM(rs.dh_simple + rs.ha_simple) as sums FROM retirement_survey as rs INNER JOIN current_funds as cf ON rs.fundid = cf.fundid INNER JOIN research_complex rc ON cf.cnum = rc.cnum WHERE rs.date = '20160930' GROUP BY rc.name, cf.cnum HAVING SUM(rs.dh_simple + rs.ha_simple) > 0 ORDER BY cf.cnum ;

SELECT r.cnum as cnum, c.name as name, r.assets as assets FROM research_complex c JOIN research_retirement_simple_ira r ON r.cnum = c.cnum WHERE r.date = '20160930' ORDER BY cnum ;

SELECT s3.cnum, name, sums, assets FROM (SELECT coalesce(s1.cnum, s2.cnum) AS cnum, s1.sums, s2.assets FROM (SELECT cf.cnum as cnum, SUM(rs.dh_simple + rs.ha_simple) as sums FROM retirement_survey as rs INNER JOIN current_funds as cf ON rs.fundid = cf.fundid WHERE rs.date = '20160930' GROUP BY cf.cnum HAVING SUM(rs.dh_simple + rs.ha_simple) > 0 ) AS s1 FULL OUTER JOIN (SELECT r.cnum as cnum, r.assets as assets FROM research_retirement_simple_ira r WHERE r.date = '20160930' ) s2 ON s1.cnum = s2.cnum ) s3 JOIN research_complex rc ON rc.cnum = s3.cnum ORDER BY cnum ;