GROUP BY+distinct record count

Question

Please consider these data:

Id        StateCode           PersonCode           Feature1
-----------------------------------------------------------
1             1                 2000                 10          
2             1                 2000                 13      
3             1                 3000                 20      
4             2                 2000                 1      
5             2                 2000                 13      
6             2                 4000                 10      
7             2                 4000                 11      
8             2                 5000                 10      
9             2                 5000                 1      
10            3                 2000                 10      
11            3                 3000                 9      
12            3                 3000                 1      
13            3                 3000                 4

I want to get this result that come from distinct StateCode & PersonCode

Count       StateCode
  2            1
  3            2
  2            3

and I wrote this query:

SELECT COUNT(*) AS Count,aa.StateCode
FROM   
     (select distinct StateCode, PersonCode
      from @tbl) aa
GROUP BY aa.StateCode

How I can write this query with one query and group by?

and if possible how I can write this query with one LINQ query?

Thanks

Possible duplicate of [SQL Server query - Selecting COUNT(\*) with DISTINCT](https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct) — Mani Deep, Jul 17 '18 at 06:04
Why is there C# and Linq tag? What is this question about ? It a combinaison of 2 duplicate https://stackoverflow.com/questions/448203/linq-to-sql-using-group-by-and-countdistinct — Drag and Drop, Jul 17 '18 at 08:14
The first one found using your title the second one adding Linq to your title. — Drag and Drop, Jul 17 '18 at 08:14

Serghei Motpan · Answer 1 · 2018-07-17T09:05:42.473

1

If your DB server supports window functions, then here is a solution:

http://sqlfiddle.com/#!4/33986d/3

SELECT DISTINCT StateCode, COUNT(DISTINCT PersonCode) OVER (PARTITION BY StateCode) AS "Count" from @tbl ORDER BY StateCode;

edited Jul 17 '18 at 09:05

answered Jul 17 '18 at 09:00

Serghei Motpan

51
4

score 0 · Accepted Answer · edited Jul 17 '18 at 06:32

0

You can write the query as :-

select count(distinct PersonCode) , StateCode from @tbl group by StateCode ;

Hope this will help you !!

edited Jul 17 '18 at 06:32

Arian

12,793
66
176
300

answered Jul 17 '18 at 05:57

rachit17

24
3

2

use `count(distinct PersonCode)` ? – Mani Deep Jul 17 '18 at 06:01

score 0 · Answer 3 · edited Jul 17 '18 at 08:37

Try GroupBy

class StateCodeCount
{
    public int Count {get; set;}
    public int StateCode {get; set;}
}

var result = datatableStateCode
    .GroupBy(c => c.StateCode )
    .Select(c => new StateCodeCount
    {
        StateCode = c.Key,
        Count= x.Where(g => g.StateCode== c.Key)
                .Select(t => t.PersonCode)
                .Distinct()
                .Count()
    })
    .ToList();

score 0 · Answer 4 · answered Jul 17 '18 at 06:19

select count(DISTINCT PersonCode) , StateCode from @tbl group by StateCode ;

DISTINCT COUNT(*) will return a row for each unique count. What you want is COUNT(DISTINCT expression): evaluates expression for each row in a group and returns the number of unique, nonnull values.

ref: https://stackoverflow.com/a/1521656/2750968

GROUP BY+distinct record count

4 Answers4