0

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.

HiGonz
  • 87
  • 9
  • 2
    Keep it internal to the DB. See http://stackoverflow.com/questions/13488822/create-computed-column-using-data-from-another-table for ways to do it. – DeanOC Mar 30 '15 at 22:47
  • 1
    Tip: It is helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. – HABO Mar 31 '15 at 00:09

1 Answers1

2

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

Xavier Poinas
  • 19,377
  • 14
  • 63
  • 95
  • 1
    Just to add, if you *really* need to you could expose this query as a 'view' in Microsoft Sql Server, assuming that is the DBMS you are using. It behaves similar to a table with limited capabilities depending on how it's configured. – Drew R Mar 30 '15 at 23:07