SQL derived value by a set of Row

Question

Hi my table looks like this

Combination of Rows with same S id is termed as a family. Output should have a Calculated Tier code based on below derivation

Output put should be Output

First time i am using Stackoverflow. I need a query in db2 and I can use the output as a report.

While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Feb 04 '21 at 17:17

score 0 · Answer 1 · answered Feb 04 '21 at 17:48

0

You need windows function as follows:

Select t.*,
       Concat('Tier ', count(*) over (partition by s_id) ) as tier
  From your_table t

answered Feb 04 '21 at 17:48

Popeye

Thanks for your Query but its not just Count of number of dependents ,if under a Sid there is a set of Relationship code i.e EE+CH it should give some kind of 'Teir+EE+CH' if its EE+SP the it should give 'Tier+EE+SP' ,if its EE+CH+CH then it should be 'Tier+FAM' like that basically some kind of string for a set of combination of Relationship code. Hope I am clear about my question – Lovely Kolli Feb 05 '21 at 06:32
What? I have written query according to expected output. You should make your logic clear in the question itself. – Popeye Feb 05 '21 at 06:41
Thanks For you time REally Appreciate it.Is it possible to compare rows with in set of rows in table.Also I am writing query in Db2 – Lovely Kolli Feb 05 '21 at 07:28
Yes. It is done using windows function. You should really get your answer if you learn how to use windows function. – Popeye Feb 05 '21 at 08:00

1 Answers1