Want a row that shows the total of the result set

I have a query that shows a user, and the number of rows where status=3, and the number of rows where status = 999. At the bottom of the result set I want a row that shows the total for the two columns. How would I modify the query below so that it shows the total at the bottom of the result set?

Example:

User             NeedApproval       NotApproved
Casey      6              6
Jess      2              1
Stephen      3              2
Total              11                         9
Select analyst_brokerrep, sum(NeedApproval) as NeedApproval, sum(NotApproved) as NotApproved
From (
      select analyst_brokerrep, Case When [status] = 3 Then count(status) END as NeedApproval,
      Case When [status] = 999 Then count(status) END as NotApproved
      FROM         tbl_Offer_1_Price INNER JOIN
                                      EnrollCustomer ON tbl_Offer_1_Price.DealCapID = EnrollCustomer.DealCapID
      where (status IN (3, 999)) AND (analyst_brokerrep is not null)
      Group By analyst_brokerrep, [status]
) as Z
Group By analyst_brokerrep

Open in new window

LVL 3
utlonghornjulieAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
oops...

started to do something, realized didn't need it, forgot to rollback the whole set of changes.
;with SumInfo as
(Select analyst_brokerrep, sum(NeedApproval) as NeedApproval, sum(NotApproved) as NotApproved
From (
      select analyst_brokerrep, Case When [status] = 3 Then count(status) END as NeedApproval,
      Case When [status] = 999 Then count(status) END as NotApproved
      FROM         tbl_Offer_1_Price INNER JOIN
                                      EnrollCustomer ON tbl_Offer_1_Price.DealCapID = EnrollCustomer.DealCapID
      where (status IN (3, 999)) AND (analyst_brokerrep is not null)
      Group By analyst_brokerrep, [status]
) as Z
Group By analyst_brokerrep)
 
select * from SumInfo
union all
select 'Total',sum(NeedApproval),(NotApproved)
from SumInfo

Open in new window

0
 
BrandonGalderisiCommented:
Here you go.
;with SumInfo as
(Select analyst_brokerrep, sum(NeedApproval) as NeedApproval, sum(NotApproved) as NotApproved
From (
      select analyst_brokerrep, Case When [status] = 3 Then count(status) END as NeedApproval,
      Case When [status] = 999 Then count(status) END as NotApproved
      FROM         tbl_Offer_1_Price INNER JOIN
                                      EnrollCustomer ON tbl_Offer_1_Price.DealCapID = EnrollCustomer.DealCapID
      where (status IN (3, 999)) AND (analyst_brokerrep is not null)
      Group By analyst_brokerrep, [status]
) as Z
Group By analyst_brokerrep)
 
select * as Sort from SumInfo
union all
select 'Total',sum(NeedApproval),(NotApproved)
from SumInfo

Open in new window

0
 
utlonghornjulieAuthor Commented:
I get the following error when I try and run the query:

Incorrect syntax near the keyword 'as'.
0
 
utlonghornjulieAuthor Commented:
Thanks it worked!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.