Solved

Want a row that shows the total of the result set

Posted on 2008-10-23
4
132 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:utlonghornjulie
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22788165
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
 
LVL 3

Author Comment

by:utlonghornjulie
ID: 22788261
I get the following error when I try and run the query:

Incorrect syntax near the keyword 'as'.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22788370
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
 
LVL 3

Author Comment

by:utlonghornjulie
ID: 22788418
Thanks it worked!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now