Summing fields in a column

Teradata Applications

Summing fields in a column

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:

 

CASE
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.

 

Kind regards

 

 

3 REPLIES
Junior Contributor

Re: Summing fields in a column

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

 

Re: Summing fields in a column

hi dnoeth

 

it is important that I return the "Total Staff" in the same colum, so I want to return as follows:

 

_Role                         Sales

Agent                          5

Manager                     10

Assistant Manager      8

All Staff                        23

Junior Contributor

Re: Summing fields in a column

Then it's based on GROUPING SETS and sorted using ORDER BY GROUPING(your_group_by_column), any_other_column