CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Denko Mancheski
Denko Mancheski

Reputation: 2719

MS SQL Computed column

I want to create a column based on COUNT(*) on another table, and when a record is deleted from that table it should decrease the value in this new column and vice versa. So, here is the query:

SELECT COUNT (*) FROM dbo.Korisnik1_FakturaStavka GROUP BY dbo.Korisnik1_FakturaStavka.FakturaID

And it returns this: enter image description here

And when I try to create a computated column like this:

CREATE TABLE test( NumberOF as (SELECT COUNT (*) FROM dbo.Korisnik1_FakturaStavka GROUP BY dbo.Korisnik1_FakturaStavka.FakturaID) )

I get the following error: 

Subqueries are not allowed in this context. Only scalar expressions are allowed.

Here is the main table that I want to compute from:

enter image description here

How can I resolve this ?

Upvotes: 0

Views: 158

Answers (2)

Karl Kieninger
Karl Kieninger

Reputation: 9129

You can define a UDF:

create function dbo.NumberOfFakturaID(@id int) returns int as begin
  return (select count(1) from Korisnik1_FakturaStavka where id=@id)
end

and then use it as the computed column:

CREATE TABLE test(FakturaID int, NumberOF as dbo.NumberOfFakturaID(FakturaID))

But putting that sort of calc as a computed column should be used with care.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269523

This is too long for a comment.

You can do this by defining a function to calculate the count and using that function in the computed column definition. However, I don't think this is a good idea for frequently used columns, because you will be doing a lot of counting "behind the scenes".

Alternatives:

  • Set up a view or materialized view with the additional count column.
  • Do the count explicitly when you need it.
  • Set up a trigger to store the count in the first table, whenever rows are inserted/updated/deleted from the second table.

Upvotes: 0

Related Questions