Our first problem is that you have no idea what a table is. There must be a key which means that all NULL-able columns. Next, identifiers cannot be numeric because you don't do any math on them. This might be what you meant:
CREATE TABLE Holdings -- these are not cusip or any other indusrt standard identifiers
(main_holding_cusip CHAR(9) NOT NULL,
offering_element_cusip CHAR(9) NOT NULL,
foobar_cnt INTEGER DEFAULT 1 NOT NULL CHECK (foobar_cnt > 0), -- needs a real name
PRIMARY KEY (main_holding_cusip, offering_element_cusip));
I am making an assumption that these are securities of some kind so that a CUSIP number would make sense. Unfortunately your data is all screwed up and you have redundancy in it in violation of all the Normal Forms. Also, we don't do insertions one row at a time; that was back in the old Sybase days with punchcards and the syntax on the ANSI/ISO standard insertion statement is different.
INSERT INTO Holdings
('1133535784', '58413', 3),
('1133535745', '58414', 6);
>> I want to be able to output a count of values across the columns so I would get a resultset like the following: <<
No! This is what your table should look like, not something you derive from gathering up the punchcards from your old file system and putting them into a non-table. Normalize the data!
>> I tried do the following but it did not work <<
FROM (SELECT main_holding_cusip,offering_element_cusip
GROUP BY main_holding_cusip,offering_element_cusip;
Of course not!
Please post DDL and follow ANSI/ISO standards when asking for help.