July 15, 2019 at 10:50 am
I have the folllowing data in a table
If OBJECT_ID(N'tempdb..#OutputTable', N'U') IS NOT NULL DROP TABLE #OutputTable
CREATE TABLE [#OutputTable](
[MainHoldingID] [int] NULL,
[OfferingElementID] [int] NULL
) ON [PRIMARY]
GO
INSERT [#OutputTable] ([MainHoldingID], [OfferingElementID]) VALUES (1133535784, 58413)
GO
INSERT [#OutputTable] ([MainHoldingID], [OfferingElementID]) VALUES (1133535784, 58413)
GO
INSERT [#OutputTable] ([MainHoldingID], [OfferingElementID]) VALUES (1133535784, 58413)
GO
INSERT [#OutputTable] ([MainHoldingID], [OfferingElementID]) VALUES (1133535745, 58414)
GO
INSERT [#OutputTable] ([MainHoldingID], [OfferingElementID]) VALUES (1133535745, 58414)
GO
INSERT [#OutputTable] ([MainHoldingID], [OfferingElementID]) VALUES (1133535745, 58414)
GO
INSERT [#OutputTable] ([MainHoldingID], [OfferingElementID]) VALUES (1133535745, 58414)
GO
INSERT [#OutputTable] ([MainHoldingID], [OfferingElementID]) VALUES (1133535745, 58414)
GO
INSERT [#OutputTable] ([MainHoldingID], [OfferingElementID]) VALUES (1133535745, 58414)
GO?
I want to be able to output a count of values across the columns so i would get a resultset like the following:
MainHoldingID OfferingElementID Count
1133535784 58413 3
1133535745 58414 6
I tried do the following but it did not work
SELECT COUNT(*)
FROM (
select [MainHoldingID],OfferingElementID
from [#OutputTable]
)
group by [MainHoldingID],OfferingElementID?
How i get a resultset as indicated with a count across multiple columns please?
July 15, 2019 at 12:39 pm
You are counting the total number of unique records.
You need to get a count per unique set of values
SELECT MainHoldingID, OfferingElementID, [Count] = COUNT(*)
FROM #OutputTable
GROUP BY MainHoldingID, OfferingElementID;
July 15, 2019 at 3:44 pm
thanks Des
July 16, 2019 at 7:41 pm
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
VALUES
('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 <<
SELECT COUNT(*)
FROM (SELECT main_holding_cusip,offering_element_cusip
FROM Holdings)
GROUP BY main_holding_cusip,offering_element_cusip;
Of course not!
Please post DDL and follow ANSI/ISO standards when asking for help.
July 16, 2019 at 9:29 pm
Next, identifiers cannot be numeric because you don't do any math on them.
This continues to be the most horribly stupid thing you've ever said, Joe.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply