Solved

Want a row that shows the total of the result set

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Review MS SQL cluster diagram 9 81
Why does this keep coming up NULL? 2 45
SQL Where not exists in same table 3 53
SQL Encryption question 2 50
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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

864 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

20 Experts available now in Live!

Get 1:1 Help Now