Count and replace

[vladk]

Hi,

I would like to GROUP BY a table with COUNT for some field. I want if COUNT > 1 write some code, say 99999, in that field, otherwise (if COUNT = 1)I want retain the actual field value.

Can anybody help?


Thank you

vladk

 

[PHV]

Something like this ?
UPDATE aTable
SET SomeField=99999
WHERE SomeField IN (SELECT SomeField FROM aTable GROUP BY SomeField HAVING COUNT(*)>1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vladk]

PHV,

Thank you for your quick reply. I actually having difficulties to apply the advice. I blame myself only. Please let me explain. Below is the example of query I just wrote for demonstration purpose:

SELECT count(Main.ClassCD) as ClassCDCount,
Main.AlphaCD,
Main.Coverage,
Main.RateGroup,
Main.Ratio
INTO TEMP1
FROM Main
WHERE Main.TerrNbr =1
GROUP BY Main.Ratio, Main.TerrNbr, Main.AlphaCD, Main.Coverage, Main.RateGroup
ORDER BY Main.TerrNbr, Main.AlphaCD, Main.Coverage, Main.RateGroup;

And this is part of the output:

ClassCDCount AlphaCD Coverage RateGroup Ratio
2 A 10 1 1
2 A 10 2 1
2 A 10 3 1
2 A 10 4 1
2 A 10 5 1
2 A 10 6 1
2 A 10 7 1
2 A 10 8 1
2 A 10 9 1
2 A 20 1 1
30 B 10 1 1
182 B 10 1 1.02
25 B 10 2 1
101 B 10 2 1.02
13 B 10 3 1
99 B 10 3 1.02
30 B 20 1 1
182 B 20 1 1.02
25 B 20 2 1
101 B 20 2 1.02
13 B 20 3 1
99 B 20 3 1.02
1 B 40 1 1
39 B 40 1 1.02
1 B 40 2 1
71 B 40 2 1.02
3 B 40 3 1
21 B 40 3 1.02
5 B 40 4 1
62 B 40 4 1.02
8 B 40 5 1
60 B 40 5 1.02
13 B 40 6 1
4 B 40 7 1
11 B 40 7 1.02
3 B 40 8 1.02
41 B 40 9 1.02

What I actually need is to review all the Ratios within given AlphaCD, within given Coverage, within given RateGroup in Main. If all the Ratios are the same then I need to replace ClassCD with 99999 and then delete the dupes. If not, leave ClassCD.

This is why I tried to ask question about the Count and replace.

In this example, AlphaCD=A with Coverage=10 and RateGroup =1 would get ClassCD 99999. The same is true for A/10/2,...A/10/9:

99999,A,10,1,1
99999,A,10,2,1
.......
99999,A,10,9,1

since lines for each A,10,?,1 are not repeated for each "?".

However, these lines:

30 B 10 1 1
182 B 10 1 1.02

require 30 lines for each ClassCD in B/10/1 for Ratio=1 and 182 lines for each ClassCD in B/10/1 for Ratio=1.02

This is the actual problem. And this is just the first one, but I don't want to ask more for now.

If it is too much for you then I will try to implement it in Excel or ask somewhere else.

Thank you again

vladk