How to do Count of values on mutiple columns

  • 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?

     

  • 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;
  • thanks Des

  • 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. 

  • jcelko212 32090 wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply