-
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
-
select bd_sanumber, df_location_key, decode(count(*),0,0,1) counter from t.rptdefects group by bd_sanumber, df_location_key;
-
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
-
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)
-
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
-
select df_location_key, count(distinct bd_sanumber) from t group by df_location_key;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|