Solved

Want a row that shows the total of the result set

Posted on 2008-10-23
4
137 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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