Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to count by range?

B TFeb 6 2025 — edited Feb 6 2025

Good morning everybody,

I would like to have some view on one of our warehouse and I wonder if this is doable easilly without having to do some complex PLSQL or Java programming.

I have a structure that is like this (simplied as much as possible to keep only the interesting part of my problem because in reality I have many more tables and lots of joins):

CREATE TABLE boxes (
 id number, 
 name varchar2(4000), 
 grp varchar2(4000),
 parent_id number 
);
INSERT INTO boxes(id, name, parent_id, grp) VALUES(1, 'Box 1', NULL, 'cars'); -- this is a root level box.
INSERT INTO boxes(id, name, parent_id, grp) VALUES(2, 'Box 999', 1, null); -- this is a box that belong to parent box having id equal to 1.
INSERT INTO boxes(id, name, parent_id, grp) VALUES(3, 'Box 989', 1, null); -- this is a box that belong to parent box having id equal to 1.

Boxes are a two level structure. Root levels don't have parent_id. And if there is a box inside the "root" level box, then that box has a parent_id. What is also interesting to mention is that root boxes have a group. It's a tag we apply on that box to know to which department it belongs.

Nox, in each box, I can have some content. And this is what I would like to have a view on. I can have content in the "root" box or in any of the boxes inside.

So, if I visually try to represent my current structure, i have this; (and indent means it's inside). My boxes 999 and 989 are inside "Box 1"

"Box 1"
    "Box 999"
    "Box 989"

Now, if I add some content.

CREATE TABLE contents(
 box_id number, 
 content_id number -- the content is a big table with many attributes like dimensions, colors, and many things. for this it doesn't matter. 
);
INSERT INTO contents(box_id, content_id) VALUES(1, 209);
INSERT INTO contents(box_id, content_id) VALUES(1, 210);
INSERT INTO contents(box_id, content_id) VALUES(1, 223);
INSERT INTO contents(box_id, content_id) VALUES(2, 787);
INSERT INTO contents(box_id, content_id) VALUES(3, 786);

I can add content in both levels (roots or not roots). And I can even have all possible mix-and-match. For instance a root box may have only content and not other boxes inside. I can have a root box with no content but content in boxes that belongs to it. If I try to represent this, it may look like this:


INSERT INTO boxes(id, name, parent_id, grp) VALUES(2, 'Box 2', NULL, 'cars'); -- this is a root level box.
INSERT INTO boxes(id, name, parent_id, grp) VALUES(3, 'Box 33', 2, null); -- this is a box that belong to parent box having id equal to 2
INSERT INTO contents(box_id, content_id) VALUES(3, 787);  -- yes one content may belong to multiple boxes.

INSERT INTO boxes(id, name, parent_id, grp) VALUES(7, 'Box 7', NULL, 'trains'); -- this is a root level box.
INSERT INTO boxes(id, name, parent_id, grp) VALUES(8, 'Box 56', 7, null); -- this is a box that belong to parent box having id equal to 7
INSERT INTO boxes(id, name, parent_id, grp) VALUES(9, 'Box 88', 7, null); -- this is a box that belong to parent box having id equal to 7
INSERT INTO contents(box_id, content_id) VALUES(88, 77);
INSERT INTO boxes(id, name, parent_id, grp) VALUES(10, 'Box 10', NULL, 'cars'); -- this is a root level box.

Now, I would like to have a view on root boxes.

I would like to have a result presented like this:
For each group, count the root boxes ONLY and their content. But presented in a table by range.

So, if I have to take this exemple I want to have:

GRP:       0         [1-5]  [6-10]
--------  ------  --------  -------
  CARS      1         2       0
trains      0         1        0

The headers are some ranges I would like to have

Box 10 has no content. So, it goes in category cars with 0 content.
Box 1 has 5 content. Their ids are 209, 210, 223 but ALSO 787 (because box with id equals to 2 belong to 1) and also 786 (that belong to box with id = 3)
Box 2 has 1 content. Its id is 787 because we have it through box with id = 3.

Box 7 has one content.

Is this doable easily in Oracle 19? And could you guide me to get that? I simplified here a lot. I will have more ranges and instead of counts I need to do some calculations on the content itself (like calculate the weight etc). But if I have an idea how to count, I think I can move forward easily.

Any help will be appreciated.
Thank you

This post has been answered by Frank Kulash on Feb 7 2025
Jump to Answer

Comments

chonewell Nov 12 2024

My Oracle Cloud tenant, cloud account, and secure email have no issues. Why haven't I received my password reset email for Oracle Cloud? This is very strange, and our attempts have not been able to solve the problem. May I ask who I should turn to for help?

L. Fernigrini Nov 12 2024

If your account is a paid one, open a Support ticket.

If it is a Free Tier then you will have to rely on help from the community. Most probable cause that you did not receive the password reset email is that your account has been stolen and the email has been changed.

chonewell Nov 13 2024

Thank you for your reply!
But when I chatted with the online customer service, they told me that my Oracle Cloud tenant, account, and email were all fine. So, there shouldn't be a problem of theft.
I have a free account, but who can I contact on the forum? I can only post, but no one on the forum can view my account permissions, right. I am currently trying to reset MFA, I don't know if it works.
It's quite ridiculous that I have a free account and can't enjoy any services, but how can I become a paid user if I can't log in to my account.

1 - 3

Post Details

Added on Feb 6 2025
5 comments
130 views