-1

I have a table called Donates. I have to find all d_names who donated more than once on a single day. I have no idea how to combine those 2 queries. Any help is appreciated. This is my table. 3 fields. donors receivers giftdate a donor could only give a receiver a gift one time. Donors can donate more than once and receivers can receive more than once. I just have to find who donated a gift more than once on a day. But i need to know when and to who.

codenheim
  • 20,467
  • 1
  • 59
  • 80
user3667148
  • 3
  • 3
  • I need all the details of the table. This is why I cant use having or group by. – user3667148 May 23 '14 at 00:33
  • OK. Add your table structure, or at least let us know what your primary key for Donates is, and also if you have any other keys. Can we assume d_name is per unique person? – codenheim May 23 '14 at 00:40
  • Table has 3 fields D_NAME, R_NAME, G_DATE – user3667148 May 23 '14 at 00:42
  • Primary key is D_NAME and R_Name – user3667148 May 23 '14 at 00:43
  • This obviously has to do with joining two tables and grouping the result, and it is doable, but we'll need additional details on, for start, where and how do you store the data for donations (what you put is the donor table). – vonPetrushev May 23 '14 at 00:44
  • Only one table is involved with just 3 fields. I need to find anyone that donated more than once on a single day. – user3667148 May 23 '14 at 00:47
  • If you had posted your table structure, we could avoid the 20 questions. Is g_date a DATE or a DATETIME ? It is important to know the granularity of your date field for this type of query. – codenheim May 23 '14 at 00:55
  • I deleted by original answer once you said you are using Oracle. You should tag your question appropriately by adding "oracle" tag. – codenheim May 23 '14 at 01:40

4 Answers4

3

You are correct that you would use COUNT, and you would use a HAVING clause to filter:

select d_name
from Donates
group by d_name
having count(1) > 1

You will of course need to add whatever other clauses to meet your requirements, such as limiting to or grouping by day. The simplest being to limit the results to one single day (you can use both WHERE and HAVING in the same query):

select d_name
from Donates
where g_date = @Date
group by d_name
having count(1) > 1

Responding to your comment, you can join on this query as a derived table:

select *
from Donates
inner join (
    select d_name
    from Donates
    where g_date = @Date
    group by d_name
    having count(1) > 1
) x on Donates.d_name = x.d_name

After all the comments in multiple places, I believe you're finally looking for something like:

select d_name, r_name, g_date
from Donates
inner join (
    select d_name, g_date
    from Donates
    group by d_name, g_date
    having count(1) > 1
) x on Donates.d_name = x.d_name and Donates.g_date = x.g_date
lc.
  • 113,939
  • 20
  • 158
  • 187
  • thank you but i forgot to mention that I need all the details of the row not just the names. – user3667148 May 23 '14 at 00:37
  • for some reason I am getting missing expression in the donationdate = @date line – user3667148 May 23 '14 at 00:45
  • This is because that is meant to serve as a parameter, if you were to pass it a specific date. It does not answer the general need to operate on all dates in the table. – user1417835 May 23 '14 at 00:56
  • This solution assumes that g_date is DATE. If you have DATETIME, you will need to change the approach. – codenheim May 23 '14 at 00:57
  • i need to find any days that fits the criteria. I dont want to use any parameters. – user3667148 May 23 '14 at 00:58
  • You should be able to change the query to fit your needs and group/join by the necessary columns. – lc. May 23 '14 at 01:10
  • @lc - The OP now says they are using Oracle (in the comments of the answer I finally deleted). Just an FYI if you want to fix. – codenheim May 23 '14 at 01:42
0
select *
from Donates
where d_name in (
    select d_name
    from Donates 
    where cast(d_date as Date) in (
        select cast(d_date as Date)
        from Donates 
        group by cast(d_date as Date)
        having count(cast(d_date as Date)) > 1
    )
    group by d_name
)
user1417835
  • 1,002
  • 1
  • 8
  • 14
0

OP now says he is using Oracle, can't use GROUP BY, and wants all fields in the table. He wants donors who donated more than once in any given day (regardless of the receivers).

select distinct d1.* 
 from Donates d1
       inner join Donates d2
        on  d1.donors = d2.donors
        and trunc(d1.giftdate) = trunc(d2.giftdate)
        and d1.rowid < d2.rowid
 ;
codenheim
  • 20,467
  • 1
  • 59
  • 80
0

I would suggest simply using analytic functions:

select d.*
from (select d.*, count(*) over (partition by trunc(d.giftdate), d.name) as cnt
      from donates d
     ) d
where cnt > 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786