CODEWITHSUNDEEP
sqlaggregateaggregate-functionshiveql
Pankaj Kumar
Pankaj Kumar

Reputation: 133

Grouping by same column but aggregating in two different ways

I have a table with schema . I want an output table which contains count of all transactions (T) per account, as well as count of those transactions (per account) which have been done beyond a certain date (say today-30).
The grouping column is common, i.e., Account, but the counting strategy is different. Its pretty easy to do this using two different queries and join the results, but is it possible to do it in a single sql query?

Input: 

  Account |  T_id |  T_date 
 ---------|-------|--------- 
  A1      |  t1   |     205 
  A1      |  t2   |     420 
  A1      |  t3   |     180 
  A1      |  t5   |     290 
  A2      |  t6   |     100

Expected Output (c=200):

  Account |  T_count |  T_count_greater_than_c 
 ---------|----------|------------------------- 
  A1      |        3 |                       2 
  A2      |        2 |                       1

To achieve the count, we can do

SELECT Account, COUNT(T_id) 
FROM T 
GROUP BY Account

To achieve the count>c, we can do

SELECT Account, COUNT(T_id) 
FROM T 
GROUP BY Account 
HAVING T_date > c

How do I combine both in a single query and prevent join operation?

Upvotes: 1

Views: 1084

Answers (1)

leftjoin
leftjoin

Reputation: 38335

Apply conditional aggregation using case or IF statement inside sum():

with mydata as(--Replace this with your table
select stack(6,
             1, '2019-08-01', 100,
             1, '2019-08-01', 100,
             1, '2019-07-01', 200,
             2, '2019-08-01', 100,
             2, '2019-08-01', 100,
             2, '2019-07-01', 200
             ) as (account, transaction_date, amount)
)
select account, sum(amount) amount, 
       sum(case when transaction_date < date_sub(current_date,30) then amount else 0 end) amount_beyond_30
  from mydata
 group by account;

Result:

account   amount  amount_beyond_30
    1       400     200
    2       400     200
    Time taken: 40.716 seconds, Fetched: 2 row(s)

Sorry, my example is for Hive SQL, some functions may be different in your database, but hope you got the idea now how to do conditional aggregation in SQL.

UPDATE after you added example and SQL:

SELECT Account, COUNT(T_id) as cnt,
       count(case when T_date > 200 then 1 else null end) as T_count_greater_than_c
  FROM T 
 GROUP BY Account

Upvotes: 3

Related Questions