SUM IF from column in different table

1

I'm sorry if this question is inappropriate for this forum. Please forgive me in advance.

I'm trying to create a Computed Column in SSMS in Table_1 called [GroupsABC]. This column will first look up the [Date] column from Table_1 and find it in Table_2; then SUM the column Table_2.[Hours] if Table_2.[Area] contains "GroupA", "GroupB" or "GroupC"

I'm very new to SQL and I have a poor understanding of syntax. Thank you, and sorry again if this is not the right place to ask this.

2 

select t1.date, /*...*/ 
    (SELECT SUM(t2.[Hours]) FROM table_2 t2 WHERE t2.Date = t1.Date AND t2.[Area] IN ('GroupA', 'GroupB', 'GroupC'))
from table_1 t1
3

Thank you for your quick response. Unfortunately, I think I've still got the syntax wrong. I notice in some instances you place the columns in square brackets and others you don't; could this be an issue?

Thank you again for your help

Screenshot 2023-02-07 110608
Screenshot 2023-02-07 1106081443×752 33.8 KB

4

I don't see "53" in my query, so I don't know why you're getting an error related to that in your query.

5

Fair enough. Thanks again for your help

6

Computed columns can only access data in the same row unless you use a user defined function (though that is typically frowned upon because that column could not be persisted).

The best way to do this is in a view (probably an indexed view).

7

Thank you for your response and suggestion - I'll begin doing research into Indexed Views

1 Like