Stored procedure count number of rows with id numbers in the count

Question

I want to get count total numbers of claims with the ID.

SELECT   
    InsuranceId,Name,
    COUNT(claimid) AS TotalPendingClaims
FROM 
    Claim 
GROUP BY
    InsuranceId

This is my query, but I want to get claimid also which is counted in the above query. I need column claimIds=(1,2,4,5)

Expected result:

InsuranceId  Name TotalPendingClaims  ClaimIds
-----------------------------------------------
1            Med     2                  23,24
2            TX      1                   55
3            TED     3                  44,45,46

ClaimIds here is string column with samples value like 23,24 or 55 ? — Bùi Đức Khánh, Dec 13 '17 at 04:48
I guess this answer is similar to what you want to achieve: https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Oleksii Aza, Dec 13 '17 at 04:51
no claimIds is new column which get the claimId which are Group by InsuranceId. — Ali Imran, Dec 13 '17 at 04:51
Your query is wrong or incomplete. When you group by InsuranceId, you cannot select name, PayerID837. One more thing, can you post table structure of Claim table ? — Bùi Đức Khánh, Dec 13 '17 at 04:55
Kindly share sample data from which you want to obtain the desired result mentioned in question.. — zarruq, Dec 13 '17 at 05:19

Bùi Đức Khánh · Answer 1 · 2017-12-13T05:22:00.847

1

You may try this

    SELECT   
        InsuranceId
        , count(claimid) as TotalPendingClaims
        , claimids = STUFF((SELECT ',' + cast(claimid as varchar(255)) FROM Claim B WHERE B.InsuranceId = A.InsuranceId FOR XML PATH('')) , 1 , 1 , '' )
            From Claim A
            Group by InsuranceId

edited Dec 13 '17 at 05:22

answered Dec 13 '17 at 04:43

Bùi Đức Khánh

3,975
6
27
43

score 0 · Answer 2 · answered Dec 13 '17 at 05:25

You can make use of the following query in which stuff concatenates the claim ID's and count can be calculated seperately

SELECT DISTINCT t2.InsuranceId ,
    STUFF((Select ','+ CONVERT(VARCHAR(5), claimid) FROM #TEMP_INSURANCE T1 
WHERE T1.InsuranceId=T2.InsuranceId FOR XML PATH('')),1,1,'')  AS ClaimID,
    Cnt
FROM #TEMP_INSURANCE t2 INNER JOIN
    (SELECT InsuranceId,COUNT(claimid) Cnt FROM #TEMP_INSURANCE GROUP BY InsuranceId)T ON t.InsuranceId = t2.InsuranceId

score 0 · Answer 3 · answered Dec 13 '17 at 05:42

Answer of my Problem is :

--changes addded by Ali Imran 12/13/2017 get also the claim number

claimid=stuff
(
 ( select distinct','+cast(c1.claimid as varchar(max))
  from #Claim c1 where
  c1.insuranceid=ins.insuranceid

  for xml path('')
  ),1,1,''
),

--Changes ended by Ali Imran

score 0 · Accepted Answer · answered Dec 13 '17 at 06:33

Try this query

SELECT InsuranceId, name, COUNT(*) TotalPendingClaims, 
ClaimIds = STUFF((SELECT ', ' + CONVERT(varchar, claimid)
           FROM  dbo.Claim c2
           WHERE c1.InsuranceId = c2.InsuranceId AND c1.name = c2.name
           FOR XML PATH('')), 1, 2, '')
FROM claim c1
GROUP BY InsuranceId, name
ORDER BY InsuranceId, name

Stored procedure count number of rows with id numbers in the count

4 Answers4