Post reply

Populate column with a count based on 'grouped' rows within a table

  • March 24, 2009 at 7:02 am

    #130037

    Hi,

    I need to establish a count based on 'grouped' rows within a table.

    If we look at 6 columns within the Product table (where PK = Primary Key):

    ItemId (PK)

    ItemType

    Location (PK)

    Archive

    Source (PK)

    Blend_Count

    Example

    If the values of 5 rows are (in relative order) :

    5 ItemIds are '1234A', '1234A', '1234A', '1234A', '1234A'

    5 ItemTypes are '1', '1', '1', '1', '1'

    5 Locations are '', 'RT', 'SA', 'SN', AN,

    5 Archives are 'A', '', '', '', ''

    5 Sources are '1', '1', '1', '1', '1'

    I need to populate the Blend_Count field in last 4 records with the value of '4' as there are 4 instances where ItemIds and Sources are the same AND Location <> '' AND Archive <> 'A'. The 1st row should be set to ''.

    Results

    5 Blend_Counts should be '', '4', '4', '4', '4'

    Therefore, the count (of Blend_Count) is ONLY applied where Location <> '' AND Archive <> 'A'

    (If there are NO matching ItemIds, then the Value is set to '1')

    Can anyone help please?

    Thanks in advance,

    Neal

  • March 24, 2009 at 8:12 am

    #965223

    To do this should be fairly easy... just write the query with corresponding GROUP BY and WHERE clause, join it back to the table and run the update (I'll help you later if you decide that you really must have it).

    Question is, why you need to do this and how often do you plan to run this updating code. As soon as you enter some new data (or delete any rows, or mark them as archive), all your sums will be incorrect and you'll have to update them. Furthermore, you plan to store the same value in many rows (if there are 1000 rows of the same grouping, you will store "1000" in every single one of them). Not a good idea.

    What is the original requirement? I doubt that it is "you simply must store this number", I rather suspect that someone was trying to solve some fully different requirement and decided that this will help him/her. If you don't know what is the reason for this requirement, try to find out - maybe it can be done better.

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

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