-2

In the following codes, how do you exclude members's spending that's larger than $500 for each year (instead of total spending for all years)?

 select 

Year
,month
,memberkey
,sum(spending) as spending

from table1
group by 
1,2,3
BurtBee
  • 19
  • 1
  • 4

1 Answers1

0

A HAVING clause won't work here since you really want to aggregate at the YEAR level to determine which records should be included. Traditionally you would do this with a correlated subquery, but in Teradata you can make use of the QUALIFY clause:

SELECT "Year"
  ,"Month"
  ,MemberKey
  ,spending
from table1
QUALIFY sum(spending) OVER (PARTITION BY "Year", MemberID) < 500
JNevill
  • 46,980
  • 4
  • 38
  • 63
  • I have updated my answer focusing specifically on the using `QUALIFY` as a solution. – JNevill Apr 16 '19 at 18:20
  • I tried to add this line to my code, but it says selected non aggregate values must be part of the associated group. But my grouping was fine before adding this line. Do you know what the problem might be? – BurtBee Apr 22 '19 at 17:40
  • Perhaps toss your query in a subquery and then perform the QUALIFY outside of that. It's really a step that should act last on the results and that can get tricky with a GROUP BY in the middle. – JNevill Apr 22 '19 at 17:50
  • create volatile table C as( Select year ,age ,memberID , sum( spending) as spending FROM Table1 group by 1,2,3 QUALIFY sum(spending) OVER (PARTITION BY "year", memberID) < 5000 ) with data – BurtBee Apr 22 '19 at 17:54
  • This is what I have now. – BurtBee Apr 22 '19 at 17:54
  • Wrapping that up in a subquery would look like: `create volatile table C as( SELECT * FROM (Select "year" ,age ,memberID , sum( spending) as spending FROM Table1 group by 1,2,3) somealias QUALIFY sum(spending) OVER (PARTITION BY "year", memberID) < 5000 )` – JNevill Apr 22 '19 at 17:59
  • It's working now, thank you. What does somealias mean? can't really find it googling. And is there a way to keep it at 5000, instead of completely throwing that row away? – BurtBee May 21 '19 at 18:09
  • That's just an alias for the derived table/subquery. You can use whatever you like for the alias. – JNevill May 21 '19 at 18:22
  • I'm not following your last question "keep it at 5000 instead of completely throwing it away". Throw what away? Keep what at 5000? It's been a month, so perhaps I'm forgetting the context. – JNevill May 21 '19 at 18:23
  • sorry i meant 500. The very last line of code - that's to completely remove any person who had more than $500 from the data, right? (instead of capping it at $500, e.g. assuming that person only costs $500 when he actually costs $100000) – BurtBee May 22 '19 at 18:13
  • Like allowing those records through but stop bringing through new rows when the total goes over 500? You could use some added functionality to the Window Function in play here in the qualify clause like: `QUALIFY sum(spending) OVER (PARTITION BY "Year", MemberID ORDER BY "month" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) < 500;` which will be a running total of rows for this memberid/year combination month by month. It will stop new months from coming into the data for that combination when the total hits over 500. – JNevill May 22 '19 at 18:32
  • So if we have John 600, Mary 300, Peter 200. After applying that line of code, we would see Mary 300, Peter 200. But I'm wondering how to get John 500, Mary 300, Peter 200. Thanks – BurtBee May 24 '19 at 21:59