Select count problem

[alison]

Hi there,

I was wondering if anyone could help.

I am using the following data to extract information ( this is just a sample there are many more columns)

BD_SANUMBER DF_LOCATION_KEY
----------- ---------------
1 5
2 8
2 8
2 5
3 8
2 1
2 8

I want query this as

SELECT COUNT (*), BD_SANUMBER, DF_LOCATION_KEY
FROM T.RPTDEFECTS
GROUP BY BD.SANUMBER DF_LOCATION_KEY

Which give me these results

COUNT(*) BD_SANUMBER DF_LOCATION_KEY
-------- ----------- ---------------
1 1 5
1 2 1
1 2 5
3 2 8
1 3 8


However if the BD_SANUMBER and the DF_LOCATION_KEY are the same I want the count to be 1 therefore
there should be two occurrences of DF_LOCATION_KEY 8 each showing a count of 1.

This is needed for reporting purposes

Any help would be great

[kmesser]

select bd_sanumber, df_location_key, decode(count(*),0,0,1) counter from t.rptdefects group by bd_sanumber, df_location_key;

[alison]

Thanks
That works well,

Could you please just explain the numbers after the decode bit, I use decode all the time to turn error numbers into words but I'm not sure what this is doing.

Many thanks

Alison

[kmesser]

Decode is essentially an if-then-else statement. The first parameter, count(*) in this case, is the expression to be tested. After that, parameters will be in value/return pairs. Thus, if the count(*) = 0, 0 is returned. After all explicit value pairs are listed, a default value can be provided. In this case, 1. Thus if count(*) is anything except 0, 1 will be returned.

Decode(expression, value1, return1, . . . valueX, returnX, default_return)

[alison]

Thanks for your kind explanation, however I have now been asked to extend the query even more, the user would like all of the numbers totaled up dependent on the result of the first query,

for example if the results were
BD_SANUMBER DF_LOCATION_KEY COUNT
----------- --------------- -----
1 5 1
2 1 1
2 5 1
2 8 1
3 8 1

then what is required is a count of 2 for DF_LOCATION 8, a count of 2 for DF_LOCATION 5, and a count of 1 for DF_LOCATION 1.

I have tried to use the query below with no success.

select bd_sanumber, df_location_key, COUNT(decode(count(*),0,0,1)) count
from t_rptdefects , t_defects
GROUP BY bd_sanumber, df_location_key

Many thanks for your continued help

Alison

[kmesser]

select df_location_key, count(distinct bd_sanumber) from t group by df_location_key;