Home » SQL & PL/SQL » SQL & PL/SQL » How to get count of records that do not have more than 1 specific value in 1 column (PL-SQL)
How to get count of records that do not have more than 1 specific value in 1 column [message #687665] |
Mon, 01 May 2023 14:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c3955cd201f26ab9c4cdcfe73d5b8edf?s=64&d=mm&r=g) |
Linhardt
Messages: 2 Registered: May 2023
|
Junior Member |
|
|
Say I have a table as below
ID, NAME, ORDER_TYPE, ORDER_DATE
1 TOM 1 5/1/2023
2 JIM 1 4/23/2023
3 BOB 1 4/20/2023
4 ART 2 3/18/2023
5 JIM 1 3/4/2023
6 TOM 1 2/1/2023
I am wanting to get the name of the person who only has 1 record with order type = 1, But I will also specifying a date range so I need it to evaluate the date range and of those records how many names only have 1 record with order_type 1.
SELECT COUNT(ID) FROM TABLE1
WHERE ORDER_DATE BETWEEN '01-MAR-2023' AND '01-MAY-2023'
AND ORDER_TYPE = 1
GROUP BY NAME
HAVING COUNT (*) < 2;
I want it to ONLY return BOB. So the COUNT would be 1.
JIM has > 1 record within the date range (has more than 1 record)
TOM has 1 record in the date range and 1 outside the date range (has more than 1 record)
ART is ORDER_TYPE 2 (has one record but it is not and order_type 1)
This has me stumped.
I appreciate any assistance I can get.
|
|
|
Re: How to get count of records that do not have more than 1 specific value in 1 column [message #687666 is a reply to message #687665] |
Mon, 01 May 2023 15:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/5db0aa55eb576006ce1bb00eee2e35d9?s=64&d=mm&r=g) |
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
Do you need count < 2, or count = 1? Meaning - "customer who has 1 record with order type = 1" or "customer who has at most 1 record with order type = 1"? Either way is easy to code, but they are different.
A few other notes: In relational database we talk about "rows" rather than records; and dates - as you have in the WHERE clause - should be given as date data type, not as strings. For example, TO_DATE('01-MAR-2023', 'dd-MON-yyyy'), not just simply the string '01-MAR-2023'. Also, GROUP BY NAME is a bad idea. There's a very good reason people are assigned ID's, and everything is done by ID, not by name; different people may have the same name.
Finally, the question isn't entirely clear. Let me rephrase, please confirm if this is correct. You want to count how many customers (NOT "name of the person" - you must make up your mind on exactly what you need to return!) ... so: how many customers have exactly one order in the entire table, regardless of order type and of date. MOREOVER, this unique order must have order type = 1, AND the date must fall within the range you specify. Is that it?
[Updated on: Mon, 01 May 2023 15:38] Report message to a moderator
|
|
|
Re: How to get count of records that do not have more than 1 specific value in 1 column [message #687667 is a reply to message #687666] |
Mon, 01 May 2023 15:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/5db0aa55eb576006ce1bb00eee2e35d9?s=64&d=mm&r=g) |
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
If my guess is correct, you could do something like this. Note that I am returning the individual names rather than a count of them, since I am rather inclined to think that's what you need. Note also the "date literals" used in the WHERE clause. They are hard-coded here; in real life you may have bind variables, or who knows what else; anyway, that isn't what you were asking about.
with
sample_inputs (id, name, order_type, order_date) as (
select 1, 'TOM', 1, to_date('5/1/2023' , 'mm/dd/yyyy') from dual union all
select 2, 'JIM', 1, to_date('4/23/2023', 'mm/dd/yyyy') from dual union all
select 3, 'BOB', 1, to_date('4/20/2023', 'mm/dd/yyyy') from dual union all
select 4, 'ART', 2, to_date('3/18/2023', 'mm/dd/yyyy') from dual union all
select 5, 'JIM', 1, to_date('3/4/2023' , 'mm/dd/yyyy') from dual union all
select 6, 'TOM', 1, to_date('2/1/2023' , 'mm/dd/yyyy') from dual
)
select name
from sample_inputs
group by name
having count(*) = 1
and min(order_type) = 1
and min(order_date) between date '2023-03-01' and date '2023-05-01'
;
NAME
---------
BOB
Since you are already limiting rows per group to 1, you can use MIN(ORDER_TYPE) and MIN(ORDER_DATE) in the HAVING clause, to filter for your conditions; there is only one row in the group, so MIN doesn't really do anything, but in HAVING you must use aggregate expressions. MIN works (so would MAX, with the same result, and for the same reason).
|
|
|
Re: How to get count of records that do not have more than 1 specific value in 1 column [message #687668 is a reply to message #687665] |
Mon, 01 May 2023 15:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Note that '01-MAR-2023' is a string and not a DATE, always use TO_DATE with format mask or DATE literal when you want to use dates.
There are several ways to achieve what you ask.
Old one with subquery (not tested as I have not your table):
select *
from table1
where name in (select name
from table1
where order_type = 1
group by name
having count(*) = 1
and count(case when order_date between date '2023-03-01' and date '2023-05-01' then id end) = 1)
/
Using analytic function (not tested as I have not your table):
with
data as (
select t1.*,
count(*) over (partition by name) total,
count(case when order_date between date '2023-03-01' and date '2023-05-01' then id end)
over (partition by name) in_range
from table1 t1
where order_type = 1
)
select id, name, order_type, order_date
from data
where total = 1 and in_range = 1
/
...
[Updated on: Mon, 01 May 2023 16:03] Report message to a moderator
|
|
|
Re: How to get count of records that do not have more than 1 specific value in 1 column [message #687669 is a reply to message #687668] |
Mon, 01 May 2023 15:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/5db0aa55eb576006ce1bb00eee2e35d9?s=64&d=mm&r=g) |
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
Michel - your first query will return a person who has two orders, one of type 1 and one of type 2, within the given range. I don't think that's what the OP wanted. Note that to address this problem, you can't simply filter for ORDER_TYPE = 1 in the WHERE clause.
In the analytic function solution, there is no mention of ORDER_TYPE anywhere in the query (except the top-level SELECT), so that can't be right either. Moreover, partitioning by ID makes no sense - note that all ID's are different, even for the same NAME. This suggests that perhaps the ID is an ORDER_ID, not a customer ID. And, even though you are simply doing what the OP already did, using strings for dates is just not right.
[Updated on: Mon, 01 May 2023 16:00] Report message to a moderator
|
|
|
Re: How to get count of records that do not have more than 1 specific value in 1 column [message #687670 is a reply to message #687669] |
Mon, 01 May 2023 16:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I replied without testing as I had no test case, so the error.
Using you test case I modified my queries.
Quote:note that all ID's are different, even for the same NAME. This suggests that perhaps the ID is an ORDER_ID, not a customer ID.
I agree, I saw I misunderstood this id when I was able to test my queries (thank again for your test case).
Quote:even though you are simply doing what the OP already did, using strings for dates is just not right.
I did not use strings for dates, I used date literals: "DATE 'YYYY-MM-DD'" which is the ANSI way to specify a date value.
[Updated on: Mon, 01 May 2023 16:12] Report message to a moderator
|
|
|
|
Re: How to get count of records that do not have more than 1 specific value in 1 column [message #687672 is a reply to message #687671] |
Tue, 02 May 2023 08:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c3955cd201f26ab9c4cdcfe73d5b8edf?s=64&d=mm&r=g) |
Linhardt
Messages: 2 Registered: May 2023
|
Junior Member |
|
|
Thanks to you both (Mathguy and Michel Cadot) for the fast responses and suggestions.
New to the forum and a beginner in pl-sql, so sorry for the bad post. But I have been put into a position where I have to learn pl-sql and work with pre-existing tables that are quite complicated and old. So the table structure, datatypes, data, etc. are not the best of quality. It is what it is at this point as I am not allowed to change or optimize them at this time. I am just tasked to use what is there and to try to produce the queries that are tasked to me.
Because of all that I tried to simplify by showing a basic table to try to get my point across as best I could. Sorry if this was confusing to you both.
But you all seemed to get what I was needing to do.
Michel Cadot your subquery suggestion might be the ticket I needed in this case. I will test it out and see and let you know the results.
I really appreciated the assistance from you both.
[Updated on: Tue, 02 May 2023 10:02] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:18:46 CDT 2024
|