CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
SikhWarrior
SikhWarrior

Reputation: 1087

Stored Procedure for each unique value, do calculation and store result

I'm a bit stuck for a stored procedure I'm beginning to develop.

Basically, there is a table called TicketRenewals that contains the following relevant columns:

Sales Rep | TicketPriceTotal | RenewalGroup
---------------------------------------------
John Doe  | $6,000           | Group 1
John Doe  | $4,000           | Group 1
Liz Smith | $1,000           | Group 1
Liz Smith | $4,000           | Group 1
...
John Doe  | $3,000           | Group 2
John Doe  | $4,000           | Group 2
John Doe  | $5,000           | Group 2
Liz Smith | $9,000           | Group 2

Each ticketrenewal has an attached SalesRepId, ticketprice and is associated with a renewal group.

I would like to first off, divide the data based on the RenewalGroupId. And then group all the sales reps in that renewal group together and sum the ticketprice. The result would be similiar to.

Sales Rep | SUM(TicketPriceTotal) | RenewalGroup
---------------------------------------------
John Doe  | $10,000          | Group 1
Liz Smith | $5,000           | Group 1
...
John Doe  | $12,000          | Group 2
Liz Smith | $9,000           | Group 2

I'm unsure how to group the sales reps without grouping the renewalgroups together.

Any help would be greatly appreciated.

Thanks!

Upvotes: 0

Views: 98

Answers (1)

mr.Reband
mr.Reband

Reputation: 2430

Judging from your desired output, it looks like you do want to group by Renewal Group.

This will yield every unique combination of SalesRepId and RenewalGroup with its corresponding sum of TicketPriceTotal: 

 SELECT SalesRepId, SUM(TicketPriceTotal) as 'SUM(TicketPriceTotal)', RenewalGroup
 from TicketRenewals 
 group by SalesRepId, RenewalGroup

Upvotes: 1

Related Questions