Hi all, fairly new to Teradata but sure this query will be simple for you guys.
I have a case statement to change the role number to a role name. I then have the volume of work completed by those agents in a SalesVolume column:
WHEN _Role IN (1) THEN 'Agent'
WHEN _Role IN (2) THEN 'Manager'
WHEN _Role IN (3) Then 'Assistant Manager '
ELSE 'Other' END AS Agent_Type,
How do I expand this code to return "All staff" in the Agent_Type column? So would essentially add Agents, Managers, Assistant Managers and Other together and returns "All Staff" in the Agent_Type, and therefore return the volume of work completed by all staff in my SalesVolume Column. If I exported the data and did a pivot table the pivot would add this "Total" for me, but I want to try to get Teradata to do this.
Can you show your current query, it's an aggregation?
"Total" as a new column or a new row?It's probably something like this
SUM(SalesVolume) OVER (???) -- new column
GROUP BY GROUPING SETS (your current grouping columns, ()) -- new row
it is important that I return the "Total Staff" in the same colum, so I want to return as follows:
Assistant Manager 8
All Staff 23