Postgresql - Add a column whose value depends on another row's value

whatdahil
October 21, 2023
214 views
1 vote
2 Answers

I’m trying to implement an "auto-fail" kind of column in a table that includes scores for evaluations like below:

Employee	Category	Score
John	Foo	5
John	Bar	4
Randy	Foo	3
Randy	Bar	0
Daniel	Foo	2
Daniel	Bar	1
Joe	Foo	1
Joe	Bar	0

I wanna create another column (‘calculated score’) such that, if their score in the Bar category is 0, they get a 0 on their overall score (basically, their score on Foo also becomes 0). Otherwise, it will just reflect their current score

So in the example above, the desired outcome will be:

Employee	Category	Score	Calculated Score
John	Foo	5	5
John	Bar	4	4
Randy	Foo	3	0
Randy	Bar	0	0
Daniel	Foo	2	2
Daniel	Bar	1	1
Joe	Foo	1	0
Joe	Bar	0	0

Where Randy and Joe will now have 0 overall scores because they got 0 in Bar

I was able to achieve this in a spreadsheet app (though I’m not sure if my method is the most elegant way) by making a helper column that works as an "indicator" if an employee has an auto-fail score or not by concatenating their employee ID(for the sake of this sample table we’ll use Employee field) to the category and score:

Employee	Category	Score	Indicator
John	Foo	5	JohnFoo5
John	Bar	4	JohnBar4
Randy	Foo	3	RandyFoo3
Randy	Bar	0	RandyBar0
Daniel	Foo	2	DanielFoo2
Daniel	Bar	1	DanielBar1
Joe	Foo	1	JoeFoo1
Joe	Bar	0	JoeBar0

And finally, in the calculated score column, I used COUNT to check if there is an instance of CONCATENATE(Employee, ‘Bar0’), and if there is, then set it to 0, otherwise, set it to their score:

=IF(COUNTIF(D:D, CONCATENATE(A2, "Bar0") ) > 0, 0, C2)

Employee	Category	Score	Indicator	Calculated Score
John	Foo	5	JohnFoo5	5
John	Bar	4	JohnBar4	4
Randy	Foo	3	RandyFoo3	0
Randy	Bar	0	RandyBar0	0
Daniel	Foo	2	DanielFoo2	2
Daniel	Bar	1	DanielBar1	1
Joe	Foo	1	JoeFoo1	0
Joe	Bar	0	JoeBar0	0

Is there any way to implement something like this in SQL?

Answers

- Marth
- October 21, 2023 at 11:22 am
- 0 votes
0
You can use a window function (to only look at the rows for the current employee) along with a filter (to restrict to the ‘Bar’ rows for this employee) and a CASE WHEN to match on this value. If it’s 0, then return 0 (regardless of the category of the current row, but still only matching those of the current employee), otherwise return that row’s score.

You’ll need to create a view though, a generated column won’t work here (can’t refer to other rows, though there might be other problems too).

Schema (PostgreSQL v15)
```
CREATE TABLE employees (
  Employee VARCHAR(6),
  Category VARCHAR(3),
  Score INTEGER
);

INSERT INTO employees
  (Employee, Category, Score)
VALUES
  ('John', 'Foo', '5'),
  ('John', 'Bar', '4'),
  ('Randy', 'Foo', '3'),
  ('Randy', 'Bar', '0'),
  ('Daniel', 'Foo', '2'),
  ('Daniel', 'Bar', '1'),
  ('Joe', 'Foo', '1'),
  ('Joe', 'Bar', '0');
```
Query #1
```
SELECT *
     , CASE
         WHEN MIN(score) FILTER (WHERE category = 'Bar') OVER (PARTITION BY employee) = 0 THEN 0
         ELSE score
       END AS calculated_score
FROM employees
ORDER BY employee, category;
```
employee category score calculated_score

Daniel Bar 1 1

Daniel Foo 2 2

Joe Bar 0 0

Joe Foo 1 0

John Bar 4 4

John Foo 5 5

Randy Bar 0 0

Randy Foo 3 0

View on DB Fiddle
Login or Signup to reply.

- Zegarek
- October 21, 2023 at 1:33 pm
- 0 votes
0
You can just check if their 'Bar' score of 0 exists. Quick, conceptually and syntactically simple: demo
```
select *, score*(not exists(select from employees 
                            where employee=e.employee 
                            and score=0 and 'Bar'=category))::int
          as calculated_score
from employees e
order by 1,2;
```
If they do have a 0, you end up multiplying their scores by 0. If they don’t, by 1, so no change.
Login or Signup to reply.