Tricky select count() in select

[Stevenn]

I have a little tricky question in Oracle. Right now I have a SQL string which looks like

SELECT my_code FROM TABLE WHERE my_code IS NOT NULL

The codes could look like "40070101". I want both to select my_code, but also the number of 'green numbers' in each 'red number'. So if there exists following codes: "40070101", "400701??", "40070106", then the number of 'green numbers' in each 'red number' is 01+02+03+04+05+06 = 6. I guess I have to make a SELECT-query in my SELECT-query and to find the red and green numbers I can use SUBSTR(my_code, 5, 2) and SUBSTR(my_code, 7, 2). So how will the syntax?

 

[SydneyGeek]

Take a look at the Partition function. It is brilliant for getting the sum or count by group.

Alternatively, create a view with 2 calculated fields (the substrings mentioned above) then group by the first and count the second.

Denis

 

[Stevenn]

Take a look at the Partition function. It is brilliant for getting the sum or count by group.

Alternatively, create a view with 2 calculated fields (the substrings mentioned above) then group by the first and count the second.

Denis

I have been looking at PARTITION BY in my search for finding a solution, but I can't understand how to use it in my situation. Can you give me a hint?

What is a view? Something in Oracle? I do not have access to do anything besides using SELECT :-/

 

[SydneyGeek]

Partition lets you summarise by group. Assuming that you have 3 fields that will form the group (my_code and the two SUBSTR calculations) you would do something like:

COUNT(my_code) over (partition by my_code, first_substr order by my_code, first_substr, second_substr) AS some_count

For the second option, something like

SELECT my_code, substr(my_code,5,2) as red, count(substr(my_code,7,2)) as green
FROM my_table
GROUP BY my_code, substr(my_code,5,2)

Denis

 

[Stevenn]

I can't make your second option to work. It returns just 1 as COUNT() in each row.

Actually, it works with

SELECT
	SUBSTR(my_code, 6, 2) AS green,
	(SELECT COUNT(*) FROM my_table WHERE SUBSTR(my_code, 6, 2) = green) AS number
FROM project

Is there any way I can write it smarter?