Solved

Want a row that shows the total of the result set

Posted on 2008-10-23
4
136 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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

739 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