[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Im trying to add a Computed Column Specification Formula on a table to COUNT number of entries in other table.

Posted on 2009-04-20
11
Medium Priority
?
1,298 Views
Last Modified: 2012-05-06
Im trying to add a Computed Column Specification Formula on a table to COUNT number of entries in other table.  How do i do this?
0
Comment
Question by:henjohn1520
  • 6
  • 5
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24185586
you cannot, unless you are using sql2008, and create function that returns the computed data for the columnn.

prior to that, you have to use a trigger method with a "regular" column instead
0
 

Author Comment

by:henjohn1520
ID: 24185688
If i were to create a new column in the same table to count the number of entries of another column is this possible. Pretty much i am trying to count how many textbooks are in inventory. I have ISBNs and Barcodes Tables. Barcodes table includes (ID, Barcode, Description, and Out). I am trying to keep track of number of entries with same ID as well as number of entries with same ID when Out=0. I have query to update table but this has to be done for each ID one at a time. Apperently i cannot update table with multiple different values. Any help is appreciated.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24185722
a trigger on the "other" table is what you need
do you need help with the trigger code?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:henjohn1520
ID: 24185862
Yes. I have never worked with triggers before.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24186007
something like this (just fill in the correct table and column names)
CREATE TRIGGRE trg_update_counts
   ON related_table_name
 FOR INSERT, DELETE 
  AS
  UPDATE t
     SET row_count_column = (SELECT COUNT(*) 
                               FROM your_related_table r 
                              WHERE r.foreign_key_field = t.primary_key_field 
                             )
    FROM your_main_table t
   WHERE t.primary_key_field IN ( SELECT primary_key_field FROM INSERTED )
      OR t.primary_key_field IN ( SELECT primary_key_field FROM DELETED )

Open in new window

0
 

Author Comment

by:henjohn1520
ID: 24186354
This is what i have come up with and tried. I executed it and I got a "Commad(s) completed succesfully" Result. However the values in the table have not changed. Is there something else i have to do to run this to update the values in the fields? Do you see any problems with my statement? Thanks.
CREATE TRIGGER trg_update_counts 
   ON  Barcodes 
   FOR  INSERT, DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
UPDATE ISNBs SET Total= (SELECT     COUNT(Barcodes.[In]) 
FROM         Barcodes INNER JOIN
                      ISBNs ON Barcodes.ID = ISBNs.ID
WHERE  Barcodes.[ID]= ISBNs.[ID]
GROUP BY Barcodes.ID)
WHERE ISBNs.[ID] IN (SELECT Barcodes.[ID])
 
END
GO

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24186630
now you have a trigger (there are some corrections, see below), any future insert/deleted on the child table should update the relevant rows from the main table:
CREATE TRIGGER trg_update_counts 
   ON  Barcodes 
   FOR  INSERT, DELETE
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
 
    -- Insert statements for trigger here
UPDATE ISNBs SET Total= (SELECT COUNT(Barcodes.[In]) 
                           FROM Barcodes 
                          WHERE Barcodes.ID = ISBNs.ID
                        )
  WHERE ISBNs.[ID] IN (SELECT [ID] FROM INSERTED )
     OR ISBNs.[ID] IN (SELECT [ID] FROM DELETED )
 
END
GO

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24186638
and to update the current rows, one-shot:
UPDATE ISNBs SET Total= (SELECT COUNT(Barcodes.[In]) 
                           FROM Barcodes 
                          WHERE Barcodes.ID = ISBNs.ID
                        )

Open in new window

0
 

Author Comment

by:henjohn1520
ID: 24187189
I really appreciate your help. I executed the trigger with the corrections you made. I am still unclear about how this trigger is supposed to work. I have a front-end application and i have tested it by entering new barcodes in new records but there are no changes made on the field that has the value i want. Is there something that needs to be added to the application (im using visual basic) to execute this trigger?

Regarding the UPDATE statement. I have tried this before and have had no success. If i run that query as stated then i will get all records updated to the total value of ALL different entries. If have tried running another query with a subquery but SQLServer 2005 states that i cannot perform that statement with subquery because  subquery has multiple values. That is why i started looking into the Computed Column alternative.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24187275
>If i run that query as stated then i will get all records updated to the total value of ALL different entries.
I can't believe that, unless you did not run it like that, or the field ID is not the linking field...

>I executed the trigger with the corrections you made.
as from then, it should "work" without any additional code change in your application.
I presume, the problem is the same as for the global update "problem", somehow.
0
 

Author Comment

by:henjohn1520
ID: 24187822
I have tried the UPDATE statement you provided and it worked!! I really appreciate your help with this. The querys i was trying to run previously were similar but not exactly like you stated and yours did work. Again thanks for your help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question