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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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