Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

How to comine like fields in MS SQL

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
crosstf
Asked:
crosstf
  • 3
  • 2
3 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
crosstfAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
What about the result set of the query which I provided
0
 
crosstfAuthor Commented:
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
 
crosstfAuthor Commented:
Thank you, was able to obtain the solution.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now