When returning the count from SQL, how to also include the value in grouped item for which the count doesn't exist?

Question

Been working on this SQL dilemma for a while now. The part of the table looks like the following.

It's a many-to-many table relationship where one claim can have many notes. So, one example would be the following:

------------------------------------------
|   ClaimID  |   NoteID  |       Note    |
------------------------------------------
|    2387    |     1     |    Test 1     |
|    2387    |     2     |    Test 2     |
|    2387    |     3     |    Test 3     |
|    2532    |     4     |  Something 1  |
|    2539    |     5     |     abcd      |
|    2539    |     6     |    jklm       |
------------------------------------------

You get the idea.

So, when I run the query I want the result in such a way that it should show me the number of note counts from 1 to 10. If the count exist, then it should show me the count, otherwise 0. An example of what it would look like in the real-world scenario is the following.

    [{
         "numOfNotes":1,
         "count":5916
     },{
        "numOfNotes":2,
        "count":1846
     },{
        "numOfNotes":3,
        "count":639
     },{
       "numOfNotes":4,
       "count":226
     },{
       "numOfNotes":5,
       "count":94
     },{
      "numOfNotes":6,
      "count":50
     },{
      "numOfNotes":7,
      "count":10
     },{
      "numOfNotes":8,
      "count":2
     },{
      "numOfNotes":9,
      "count":2
     },{
      "numOfNotes":11,
      "count":2
   }]

That's the query return from the database that I retrieved using C# and linq. Here's the code for that.

if (type == "e" || type == "p")
{
  //sub query to retrieve notes

  var subquery = from f in db.DBFileInfo
                 join c in db.Claims on f.FileID equals c.FileID into cl
                 from gp1 in cl.DefaultIfEmpty()
                 join n in db.Notes on gp1.ClaimID equals n.ClaimID into nt
                 from gp2 in nt.DefaultIfEmpty()
                 where f.ReportDate.Month == month && f.ReportDate.Year == year
                 group gp2 by gp2.ClaimID into g
                 select new
                 {
                       Key = g.Key,
                       Count = g.Count()
                 };

  //query to grop by notes count. Notes count is consider contact per claim
  var count = (from c in db.Claims
              join s in subquery on c.ClaimID equals s.Key
              where c.RecordType == type && 
              (c.Username != "RxService") 
              && (c.HIC3 != "J3A" && c.HIC3 != "J3C" && c.HIC3 != "H7N")
              group s by s.Count into g
              orderby g.Key
              select new
              {
                  NumOfNotes = g.Key,
                  count = g.Count()
              }).Take(10);
}

If you notice in the result, there are numOfNotes from 1 - 11 but 10 is missing. That's because there aren't any claimID that has 10 notes. So, in this case, I still want SQL to return "numOfNotes": 10, "count": 0. And if you notice, I only asked for 10 results (Take(10)), because there can be more than 10 such notes per claim which we are not interested.

And in some cases, there aren't more than 5 notes per claimID for the given time period. In one instance, the result from SQL only goes up to 6. But I still want the result upto 10 whether it exists or not. Is it possible?

In case if you're interested: Here's my SQL statement

SELECT 
    count(C.ClaimID) as count, N.NotesPerClaim 
FROM
    ClaimsTable C
INNER JOIN
    (SELECT 
         claimid, count(note) as NotesPerClaim 
     FROM
         NotesTable
     GROUP BY 
         ClaimID) as N ON N.ClaimID = C.ClaimID
WHERE 
    RecordType = 'e' AND 
    (Username <> 'RxService') AND 
    (HIC3 <> 'J3A' AND HIC3 <> 'J3C' AND HIC3 <> 'H7N')
GROUP BY 
    N.NotesPerClaim
ORDER BY 
    N.NotesPerClaim;

I just realize your data didnt have the `10` but have `11`, so will you discard that one? — Juan Carlos Oropeza, Oct 30 '15 at 14:06

Gordon Linoff · Accepted Answer · 2015-10-30T17:42:38.487

1

You seem to want a numbers table. Here is one method:

WITH nums as (
      SELECT 1 as n
      UNION ALL
      SELECT n + 1
      FROM nums
      WHERE n < 10
     ),
     t as (
      SELECT count(C.ClaimID) as NumClaims, N.NotesPerClaim
      FROM ClaimsTable C JOIN
           (SELECT claimid, count(note) as NotesPerClaim
            FROM NotesTable
            GROUP BY ClaimID
           ) N
           ON N.ClaimID = C.ClaimID
      WHERE c.RecordType = 'e' AND 
            c.Username <> 'RxService' AND 
            c.HIC3 NOT IN ('J3A', 'J3C', 'H7N')
      GROUP BY N.NotesPerClaim
     )
SELECT nums.n as NotesPerClaim, t.NumClaims
FROM nums LEFT JOIN
     t
     ON nums.n = t.NotesPerClaim
ORDER BY NotesPerClaim;

edited Oct 30 '15 at 17:42

answered Oct 30 '15 at 14:04

Gordon Linoff

1,242,037
58
646
786

Not exactly sure what you did or what I'm doing wrong, but I get `Invalid object name 'n'` – user1828605 Oct 30 '15 at 14:10
Can you please update your answer. I first thought it wasn't what I wanted. And since it didn't make any sense to me the way you used n. I then started to look in to the WITH clause, and then it made sense to me what you're trying to do. This is exactly what I need. – user1828605 Oct 30 '15 at 16:37
`FROM n` should be `FROM nums`. – user1828605 Oct 30 '15 at 16:39
btw, would you happen to know how to do this in linq in C#? – user1828605 Oct 30 '15 at 16:40
If you can avoid querying it in code, you honestly should. linq although useful in some cases adds some overhead (and depending on what exactly you're doing, it can end up being a lot of overhead) compared to directly querying the database. – user2366842 Oct 30 '15 at 17:47
@user1828605 . . . Correct. That was the problem. I usually just call the CTE `n`, but you had already used that for another purpose. – Gordon Linoff Oct 30 '15 at 17:49
http://stackoverflow.com/questions/584841/common-table-expression-cte-in-linq-to-sql this might be useful if you're still insistent on using linq however...it looks like you'll likely end up executing arbitrary sql within linq to get the results you're after. – user2366842 Oct 30 '15 at 18:04

score 0 · Answer 2 · answered Oct 30 '15 at 14:03

Use LEFT JOIN instead of INNER JOIN

Also is better use HIC3 NOT IN ('J3A', 'J3C', 'H7N')

SELECT count(C.ClaimID) AS count
     , N.NotesPerClaim
FROM ClaimsTable C
LEFT JOIN (
    SELECT  claimid
          , count(note) AS NotesPerClaim
    FROM NotesTable
    GROUP BY ClaimID
    ) AS N ON N.ClaimID = C.ClaimID
WHERE RecordType = 'e'
    AND (Username <> 'RxService')
    AND HIC3 NOT IN ('J3A', 'J3C', 'H7N')

GROUP BY N.NotesPerClaim
ORDER BY N.NotesPerClaim;

devlin carnate · Answer 3 · 2015-10-30T14:57:39.307

0

LEFT JOIN on the notes table, and check for ISNULL condition. In the case of NULL from notes, return 0.

Here is a SQL Fiddle demo of the concept.

Your code should be:

`SELECT count(C.ClaimID) as count, ISNULL(N.NotesPerClaim,0) as NotesPerClaim
 FROM ClaimsTable C
 LEFT OUTER JOIN
 (SELECT claimid, count(note) as NotesPerClaim from NotesTable
 GROUP BY ClaimID) as N
 ON N.ClaimID = C.ClaimID
 WHERE RecordType = 'e' AND 
 (Username <> 'RxService') AND (HIC3 NOT IN ('J3A','J3C','H7N'))
 GROUP BY ISNULL(N.NotesPerClaim,0)
 ORDER BY N.NotesPerClaim;`

edited Oct 30 '15 at 14:57

answered Oct 30 '15 at 14:16

devlin carnate

8,309
7
48
82

I'm not sure what I'm doing wrong, but I still get the same result as the one shown above in my question. I don't see the result 0 for number of notes 10. – user1828605 Oct 30 '15 at 14:32
do you see results for 0 count if you run the SQL directly against your database (using SSMS) – devlin carnate Oct 30 '15 at 14:34
No. It still shows me the same result that I have in the question above. – user1828605 Oct 30 '15 at 14:36
Then we need to see your table schema. If you look at the SQL Fiddle I linked in my answer, you'll see that the concept is sound. – devlin carnate Oct 30 '15 at 14:37
Try removing the WHERE conditions for testing. Do the records with 0 notes show up with 0 count when you don't qualify your query with the WHERE conditions? – devlin carnate Oct 30 '15 at 14:39
But your SQL Fiddle show `1` three times for 0, 1, 1 and `2` once for 0. I'm lost. Should it not show count of `1` 2? I'm super confused. – user1828605 Oct 30 '15 at 14:50
I changed the fiddle to display more info so you can see where the results are coming from. Here's the updated link: http://sqlfiddle.com/#!3/f99f8a/5 @user1828605 – devlin carnate Oct 30 '15 at 14:56
I think you misunderstood my question. In my question, it is not the claim I'm counting as what you're doing. It's the number of notes I'm counting. For you SQL fiddle, I changed the insertion http://sqlfiddle.com/#!3/7658a/1. I added a few more items in TB. You'll notice that there are 4 `1`, 1 `2`, and ` 1`4`. So the result should be for 1 (for 1, 4 cause they have only 1 item) count = 2 and , for 2 (number of grouped items) the count should be 0 because there aren't any TB with 2 counts (for any TA id's), similarly 3 should be 0, and 4 for 1 – user1828605 Oct 30 '15 at 16:14
and for this, the result should be like this `1 - 2, 2 - 0, 3 - 0, 4 - 1` it's counting the ID. I don't know.. this is way too confusing. – user1828605 Oct 30 '15 at 16:16

When returning the count from SQL, how to also include the value in grouped item for which the count doesn't exist?

3 Answers3