Solved

How to comine like fields in MS SQL

Posted on 2009-05-18
5
167 Views
Last Modified: 2012-05-07
I have the following query from a stored procedure, and I want to combing the ShortDescrption value in the counts by the names listed in the where clause so that it counts all Approved as one count for example, instead of several. How do I do that?
select 

(select Count(1) LoanCount from  @SummaryReportBasic), 

ShortDescription ,

LoanAmount = sum(loanamount),

CountofLoan from @SummaryReportBasic

where shortdescription in ('Approved', 'Approved and awaiting Esign and/Or Certification', 'Certified and Ready for Disbursement','Pending','On Hold','Approved but Cancelled', 'Cancelled/Withdrawn', 'Denied')

Group by shortdescription, CountofLoan

Order by shortdescription

Open in new window

0
Comment
Question by:crosstf
  • 3
  • 2
5 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 300 total points
ID: 24412155
Hope this helps:

If I misunderstood your requirement, give the sample result set of the query below and the expected result set to help you better
select 

ShortDescription ,

Count(1) LoanCount, 

LoanAmount = sum(loanamount) 

from @SummaryReportBasic

where shortdescription in ('Approved', 'Approved and awaiting Esign and/Or Certification', 'Certified and Ready for Disbursement','Pending','On Hold','Approved but Cancelled', 'Cancelled/Withdrawn', 'Denied')

Group by shortdescription

Order by shortdescription

Open in new window

0
 

Author Comment

by:crosstf
ID: 24412244
That query provides the following dataset, and I need to combing rows were the shortdescription IS the same. Its lists them separately. So all Approved as one row, all Approve and awaiting, etc.
-------------------------------------------------------------------------------------------------------------- ----------- --
21          Approved                                                                                             10000       1
21          Approved                                                                                             34081       4
21          Approved                                                                                             356423      23
21          Approved and awaiting                                                                        10001       1
21          Approved and awaiting                                                                        11555       2
21          Approved and awaiting                                                                        247156      25
21          Approved and awaiting                                                                        351008      31
21          Approved but Cancelled                                                                       10000       1
21          Cancelled/Withdrawn                                                                           124345      9
21          Cancelled/Withdrawn                                                                           1569556     128
21          Certified and Ready                                                                              16104       1
21          Certified and Ready                                                                              47503       4
21          Certified and Ready                                                                               77621       9
21          Denied                                                                                                   687166      51
21          Pending                                                                                                5990        1
21          Pending                                                
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 300 total points
ID: 24412310
What about the result set of the query which I provided
0
 

Accepted Solution

by:
crosstf earned 0 total points
ID: 24412482
This worked. Thank you for your help.
select ShortDescription, sum(CountofLoan) TotalCount, sum(LoanAmount) TotalLoanAmount from @SummaryReportBasic

where ShortDescription in ('Loans Disbursed To Date','Approved and awaiting Esign and/Or Certification','Pending','On Hold','Approved but Cancelled', 'Cancelled/Withdrawn', 'Denied') 

Group by ShortDescription 

Open in new window

0
 

Author Comment

by:crosstf
ID: 24412502
Thank you, was able to obtain the solution.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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 is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 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

8 Experts available now in Live!

Get 1:1 Help Now