clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798904 fiddles created (41831 in the last week).

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) ;
135 rows affected
 hidden batch(es)


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 ;
cnum name sums
2 Lord, Abbett & Co. LLC 329929
14 Principal 867280
15 Bridges 430
35 Delaware Investments 147950
38 Bank of New York Mellon/Dreyfus Corporation 1417
43 Neuberger Berman Management LLC 14273
46 Fidelity Investments 7676456
47 Foresters Financial 78766
49 Calvert Investments 76591
55 William Blair Funds 233
59 Columbia Threadneedle Investments 138908
68 MFS Investment Management 1551971
71 Nationwide Funds 10183
80 OppenheimerFunds/MassMutual 4101977
88 T. Rowe Price 1905810
91 Putnam Funds 169569
102 State Farm 999378
110 Waddell & Reed Funds 700310
115 Vanguard Group 6938415
130 USAA 169139
143 Invesco 1822865
149 Affiliated Managers Group 102
153 Goldman Sachs & Co. 182038
156 TCW Funds, Inc. 3
169 Davis Selected Advisers, L.P. 16633
200 Deutsche Asset Management 236911
207 Hartford Funds 1047561
226 Meeder Asset Management, Inc. 1487
236 Nuveen Investments 33764
275 SunAmerica Group 69726
280 Janus 1899
286 Legg Mason 5533
302 Transamerica Asset Management 308727
 hidden batch(es)


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 ;
cnum name assets
2 Lord, Abbett & Co. LLC 303026683
7 Capital, Research & Management 20000000
14 Principal 777135509
45 Federated Investors 67583161
46 Fidelity Investments 20000000
47 Foresters Financial 9999
51 Franklin Templeton Investments 1848513179
59 Columbia Threadneedle Investments 138678936
68 MFS Investment Management 1518052790
80 OppenheimerFunds/MassMutual 20000000
85 Pioneer Investment Management USA Inc. 289936684
88 T. Rowe Price 1921051937
91 Putnam Funds 20000000
102 State Farm 1026211854
110 Waddell & Reed Funds 560233847
115 Vanguard Group 20000000
130 USAA 168430000
143 Invesco 1798640460
207 Hartford Funds 586878824
304 John Hancock Investments 342692928
379 JP Morgan Chase & Co. 148135910
530 Schwab Funds, Laudus Funds & Schwab ETFs 20000000
 hidden batch(es)


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