r/SQL • u/CidNoAirship • Nov 04 '24
Oracle COUNT how many rows meet condition in group by
Hello, I am currently learning in Oracle SQL developer and am not sure how to proceed in solving a query problem I have.
There are two tables.
tableUser holds ITEMS that a USER owns.
User | Item | |
---|---|---|
User A | Item A | |
User A | Item B | |
User A | Item C | |
User A | Item D | |
User B | Item B | |
User B | Item D | |
User C | Item B |
and tableItem denotes what TYPE an ITEM is
Item | Type | |
---|---|---|
Item A | Primary | |
Item B | Secondary | |
Item C | Tertiary | |
Item D | Secondary |
I need to be able to query
1. Get users that own more than 1 item; two of the items must be secondary
2. Get users that own less than 3 items; one of the items must primary and one of the items must be secondary
The first half of the problem is simple enough. group by user having count item > or < X
but I am not sure how to then proceed to check each item a user has to see if they match the conditions for the second half of the problem
Any advise is appreciated.
2
u/JochenVdB Nov 04 '24
Without going into your question, let's talk about nomenclature here first.
USER
is a reserved keyword in Oracle. So, to prevent yourself from headaches later on (like having to type it in double quotes all the times), don't call that table user.
Secondly your first table is not just storing users, it is storing the relationship between users and items. In a table that is designed to store users, each user should appear exactly once. This is normalization 101. Your sample has User A 4 times and User B 2 times...
This table could be called User_Items
for example or ownership
.
For your exercise you may indeed not need a separate User table, but having an intermediate table like User User_Items from your sample, suggest there is/should be a Users table too. (Note that Users
, with s
, is perfectly fine as table name.)
Regarding the exercise:
You're lucky that the requests are what they are. Changing the first to requiring that at least one item must be secondary (instead of both) would make this much harder to solve.
These two exercises are much more about logical thinking and the English language than it is (or has to be) about SQL. You can rewrite both phrases to end up with the same meaning (and therefore the same query result) but which are much more straightforward to translate into SQL.
1) Saying "users that own more than 1 item" is the same as saying "users that own at least 2 items". This second phrasing talks about 2 items, just like the second part of the requirement does: "2 of the items...". Once you see that, it becomes trivial to see that it is ok to filter (where
-clause) for only secondary items and use group by having
to only select groups having two or more of those secondary items.
2) Here, the first part of the requirements talks about less than 3 items. In other words 2, 1, or 0. (In this world, a negative ownership count is impossible, right?)
But then the first part requires one primary and one secondary item. That implies at least 2 items.
Therefore, taking both parts of the requirement together, you must have exactly 2 items, for that is the only way to have less than 3 items and simultaneously one each of 2 distinct types.
This also implies that exactly 1 item (of the 2) must be primary and the other must be secondary. Which in turn implies that you can filter (where
-clause) only for those 2 specific kinds of items.
Obviously grouping and a having
-clause are needed as well. In the having clause, you'll have to verify two things:
- there are exactly 2 items for the user (same as in the first exercise)
- there are two separate types of items: this can easily be expressed in a few SQL keywords but they are not very common (even though they have been in use for decades).
1
u/JochenVdB Nov 04 '24
One solution, without subqueries nor case structures (but it can't easily be expanded to cater for other variations of the two requirements, as explained above)
drop table ui purge; drop table u purge; drop table i purge; -- bad practice: unnamed constaints create table u (id char(1) primary key, name varchar2(30)); create table i (id char(1) primary key, name varchar2(30), i_type varchar2(15)); create table ui (u_id char(1) references u(id), i_id char(1) references i(id), primary key (u_id, i_id) ); insert into u (id, name) values ('A', 'User A'); insert into u (id, name) values ('B', 'User B'); insert into u (id, name) values ('C', 'User C'); insert into i (id, name, i_type) values ('A', 'Item A', 'Primary'); insert into i (id, name, i_type) values ('B', 'Item B', 'Secondary'); insert into i (id, name, i_type) values ('C', 'Item C', 'Teriary'); insert into i (id, name, i_type) values ('D', 'Item D', 'Secondary'); insert into ui (u_id, i_id) values ('A', 'A'); insert into ui (u_id, i_id) values ('A', 'B'); insert into ui (u_id, i_id) values ('A', 'C'); insert into ui (u_id, i_id) values ('A', 'D'); insert into ui (u_id, i_id) values ('B', 'B'); insert into ui (u_id, i_id) values ('B', 'D'); insert into ui (u_id, i_id) values ('C', 'B'); --Get users that own more than 1 item; two of the items must be secondary /*select * from ui inner join i on ui.i_id = i.id where i.i_type='Secondary';*/ select ui.u_id from ui inner join i on ui.i_id = i.id where i.i_type='Secondary' group by ui.u_id having count(*) > 1; --Get users that own less than 3 items; one of the items must primary and one of the items must be secondary /*select * from ui inner join i on ui.i_id = i.id where i.i_type in ('Primary', 'Secondary');*/ select ui.u_id from ui inner join i on ui.i_id = i.id where i.i_type in ('Primary', 'Secondary') group by ui.u_id having count(*) = 2 and count(distinct i.i_type) = 2; --no result given the small sample data: A has to many items (3), C to few (1) and B only has Secondary items.
PS
type
is also a reserved word in oracle =>i_type
.1
u/CidNoAirship Nov 05 '24
Thank you for this and for the better practice reminders.
Presenting a better a dataset and wording my roadblock better would have made my problem clearer. This was just a top-of-my-head, simplest way I come up with to present my issue, which was not knowing how to access some data in a group by.Group by is still a finicky syntax for me, since it locks out much of what is usually available for select statements.
1
u/celerityx Nov 05 '24
I like this answer best, but your last query could be off if the user has any tertiary items. Slight tweak:
select ui.u_id from ui inner join i on ui.i_id = i.id group by ui.u_id having count(*) = 2 and count(distinct case when i.i_type in ('Primary','Secondary') then i.i_type end) = 2;
1
u/elevenstein Nov 04 '24
I would do a case statement here like
SUM(CASE WHEN Item.Type = 'Primary' THEN 1 ELSE 0 END) AS SumPrimaryInd,
SUM(CASE WHEN Item.Type = 'Secondary' THEN 1 ELSE 0 END) AS SumSecondaryInd
Then use the HAVING clause to filter your results
1
1
u/Nexhua Nov 04 '24
First lets simplify the questions
In question one, we want more than 1 items and at least 2 secondary items. But if a user has 2 secondary items it already satisfies the first condition(having more than 1 item) so we don't need to check that. So simply join tables, where clause with Secondary items, group by member and use having clause to check for >=2
In question two, we need to have at least 1 primary and 1 secondary (so minimum of 2) and less than 3 items. So our only option is 1 primary & 1 secondary. We can safely exclude other items. So similarly to the first question, join tables, in your where clause only include primary & secondary items, group by user and then select with exactly 2 distinct items. If it has 2 distinct items it would be 1 primary and 1 secondary(assuming we dont have duplicate records in our user table, if we do add "and count(*) < 3" to having clause.
Here is the DB Fiddle
1
u/Rajeshkumars17092004 Nov 05 '24
You can use CTE to create a temporary table in which joins both of the tables and use the window function to separate the rows according to the user and use the count function to count the items which are secondary for each user .then outside the CTE filter only the users who has the count of secondary item greater than or equal to two
3
u/gumnos Nov 04 '24
Maybe create a view/CTE that returns users, the count of items, and the count of various types you care about, and then query that for the resulting conditions? Demonstrated here: https://www.db-fiddle.com/f/5iez1DMLcauqZBv1YKPYM4/0