Post reply

Count a new field in SQL statement

  • July 31, 2022 at 2:05 pm

    #4070599

    Hello all,

    I am trying to find a query that bring results like on the example.

    Thanks a lot all and best regards,

    Attachments:
    You must be logged in to view attached files.

  • July 31, 2022 at 2:26 pm

    #4070608

    Are you sure you want duplicate rows repeated?

    A combination of GROUP BY and COUNT will get you the results you need.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 31, 2022 at 3:52 pm

    #4070623

    Hi Phil

    Tks for d quick answer.

    The 2 table have more fields, therefore it will not be duplicate row.

    I tried to combine "tbl1","tbl2log" and used COUNT anf GROUP BY but i failed to get the result of combined field with count of id.

    also i tried to use WITH statment but still without success

    so still i would apprecaite help

  • July 31, 2022 at 6:36 pm

    #4070650

    The use of COUNT() OVER will make light work of this.  It basically does what Phil says... provides a combination of COUNT with GROUP BY.  You would use the PARTITION BY of the OVER clause to provide the grouping.

    If you were to proved the CREATE TABLE statement for your two examples along with code to populate the two tables, I'd be happy to show you how with tested code.

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

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

Viewing 4 posts - 1 through 3 (of 3 total)

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