CODEWITHSUNDEEP
sql
illuminato
illuminato

Reputation: 1257

SQL query with aggregation by multiple groups

I am totally stuck and cannot find an answer online.

I have a table with two columns. The first column is users (text column) and the second corresponds to his/ her transaction amount (int).

For example:

user  amount 
1     200
2     500
1     900
1     100
4     1200
1     100

My task is to define all users who have at most 3 transactions and the total amount each user is more than 1000. So for the example above it will be user №2, №4.

I tried to implement code using multiple SELECT FROM statements:

SELECT 
    recipient
FROM
    (SELECT 
         recipient, SUM(amount) AS sum_am
     FROM 
         (SELECT 
              recipient, TOP(3) amount
          FROM 
              (SELECT recipient, amount
               FROM transactions
               ORDER BY recipient ASC, amount DESC;) AS ord;)
     WHERE 
         recipient = ord.recipient;)
WHERE 
    sum_am >= 1000;

But it doesn't work.

Upvotes: 2

Views: 535

Answers (5)

Syed123
Syed123

Reputation: 1

select user, SUM(amount) as res, COUNT(*) as cnt from transactions group by user having cnt <= 3 AND res > 1000;

Upvotes: 0

Ashwik
Ashwik

Reputation: 61

Try this method:

create table transaction(recipient number,amount number);

insert into transaction values(1,200);
insert into transaction values(2,500);
insert into transaction values(1,900);
insert into transaction values(4,1200);
insert into transaction values(1,100);
commit;


select count(*),sum(amount),recipient
from transaction
group by recipient
having count(*)<=3
AND sum(amount)>=1000;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

My task is to define all users who have at least 3 transactions and the total amount each user is more than 1000.

I think you just want aggregation and having:

select user
from t
group by user
having count(*) <= 3 and
       sum(amount) >= 1000;

Upvotes: 2

Steven G
Steven G

Reputation: 1

Gordon's answer is simple, works both in SQL Server and Oracle and gets the job done well. To get more info, you can slightly amend the first line in Gordon's answer as:

select 
    user AS recipient, 
    COUNT(*) as trans_count, 
    sum(amount) as sum_am
from transactions
group by user
having count(*) >= 3 and
       sum(amount) >= 1000;

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query. you can use having count() and sum() function

select * from (
  select recipient, sum(amount) amt
  from transactions
  group by recipient
  having count(1) >= 3) t1
where amt >= 1000

see dbfiddle

Upvotes: 0

Related Questions