CODEWITHSUNDEEP
c#sqlasp.net
HiGonz
HiGonz

Reputation: 87

Add a counter to a sql table

I'd like to add a column to a table whose value counts the number of times it is repeated on another table. Something like this:

Name    NameID  Quantity  |    NameID   Franchisee   Business  BusinessID

John    12345      2      |    12345    CA           Laundry   45678
Smith   45684      1      |    12345    CA           Bakery    45679
                          |    45684    NY           Shampoo   45680

The column Quantity is the one I want to add, I want it to count all the BusinessID that belong to his NameID: John has a NameId of 12345 and that NameID has 2 BusinessIDs assosiated to it.

I don't know how to do this, I want to add this value to an aspx project, maybe it'd be easier to make a function in c# to keep the DB as is and just show the values on the client.

Upvotes: 0

Views: 432

Answers (1)

Xavier Poinas
Xavier Poinas

Reputation: 19733

In general you should not store that value. If you do, you'll have to update it every time you change the 2nd table, and then you'll have problems like what do I do if the update of the 2nd table succeeds, but that of the 1st table fails? (answer: use a transaction).

It is way simpler to calculate that value on the fly. You can do this in SQL:

SELECT
    t1.Name, t1.NameID, COUNT(*) AS Quantity
FROM
    Table1 t1
        INNER JOIN Table2 t2 ON t1.NameID = t2.NameID
GROUP BY
    t1.Name, t1.NameID

The only reason to store it would be if that value was an expensive calculation (in this case, it is not).

Upvotes: 2

Related Questions