Create table and get data from another table

问题:

I have a table Cost category:

CREATE TABLE [dbo].[CostCategory](
[ID_CostCategory] [int] NOT NULL,
[Name] [varchar](150) NOT NULL,
[Plan] [money] NOT NULL,
[Realization] [money] NULL,
)
go

and I have another table with defined Costs:

CREATE TABLE [dbo].[Cost](
[ID_Cost] [int] NOT NULL,
[Name] [varchar](50) NULL,
[ID_CostCategory] [int] NULL,
[ID_Department] [int] NULL,
[ID_Project] [int] NULL,
[Value] [money] NULL,
)
go 

What I want to do is to sum values from Cost table (according to ID_CostCategory) and put into Cost Category table, Realization column. So each ID_CostCategory presents automaticaly sum of Costs from Cost table (per ID_CostCategory).

How can I modify the script of Cost Category table to achieve it? Probably it's not a rocket science but I am really new to SQL.

---

OK. One more thing I forgot to add... the structure looks in a way:
Cost>Cost Category>Department>Project

Right now I can easily take data from cost table and present it in Cost Category table. But Cost table includes such data as:

ID_Cost Name    ID_CostCategory ID_Department   ID_Project  Value
1   fv 001  1   1   1   100
2   fv 002  2   1   1   500
3   fv 003  2   2   1   300
4   fv 004  3   2   2   150
5   fv 005  3   3   2   30
6   fv 006  4   3   2   15

I have also table Department which includes colums: ID_Department, Name, Plan,

So now, I want to do is to sum values from Cost table (according to ID_CostCategory and ID_Department) and put into Department table as Realization column. So each ID_Department presents automaticaly sum of Costs from Cost table (per ID_CostCategory and ID_Department).

Hope it is clear (later on will have to do it with table Project, but once I got it, it will be easy)

回答1:

Try this

--create table without realization column
CREATE TABLE [dbo].[CostCategory](
[ID_CostCategory] [int] NOT NULL,
[Name] [varchar](150) NOT NULL,
[Plan] [money] NOT NULL
) go

CREATE TABLE [dbo].[Cost](
[ID_Cost] [int] NOT NULL,
[Name] [varchar](50) NULL,
[ID_CostCategory] [int] NULL,
[ID_Department] [int] NULL,
[ID_Project] [int] NULL,
[Value] [money] NULL,

) go 

Create a UDF to calculate sum of the cost column:

CREATE FUNCTION [dbo].[CalculateRealization](@Id INT) 
RETURNS money
AS 
BEGIN
  DECLARE @cost money

  SELECT @cost = SUM(Value)
  FROM [dbo].[Cost]
  WHERE [ID_CostCategory] = @ID

  return @cost
END

Now Alter your CostCategory table to add computed column:

ALTER TABLE [dbo].[CostCategory]
   ADD [Realization] AS dbo.CalculateRealization(ID_CostCategory);

Now you can select Realization from Costcategory

SELECT ID_CostCategory, Realization
FROM [dbo].[CostCategory]

Answer to your comment below:

Create Another UDF

CREATE FUNCTION [dbo].[CheckValue](@Id INT, @value Money) 
RETURNS INT
AS 
BEGIN
  DECLARE @flg INT
  SELECT @flg = CASE WHEN [Plan] >= @value THEN 1 ELSE 0 END
  FROM [dbo].[CostCategory]
  WHERE [ID_CostCategory] = @ID

  return @flg;
END

Now add Constraint on Cost Table:

ALTER TABLE ALTER TABLE [dbo].[Cost]
  ADD CONSTRAINT CHK_VAL_PLAN_COSTCATG
    CHECK(dbo.CheckValue(ID_CostCategory, Value) = 1)

回答2:

You do not need to have a Realization column as part of the CostCategory table. Rather, you will want to use a join.

Select A.ID_CostCategory, A.Name, SUM(B.Value) As Realization from CostCategory A
JOIN Cost B ON A.ID_CostCategory = B.ID_CostCategory
Group By A.ID_CostCategory, A.Name