Count rows with multiple aggreagate columns

Question

I have a test table to demonstrate the issue:

Id  NetworkId   CountryCode
1       1           de
2       2           de
3       2           de
4       2           de
5       1           us
6       1           us
7       1           us
8       2           us

I need to output something like this:

NetworkId   CountryCode    DistCount
    1           de              1
    2           de              3
    1           us              3
    2           us              1

Attempted Queries

I looked for several answers on SO and I wasn't able to find exactly what I need. Here is the first related question and the queries I tried: Counting the rows of multiple distinct columns

Query:

SELECT NetworkId, CountryCode, COUNT(*) as DistCount
FROM (SELECT DISTINCT NetworkId, CountryCode FROM TestTable) AS FOO
GROUP BY NetworkId, CountryCode

Results in:

NetworkId   CountryCode    DistCount
    1           de              1
    1           us              1
    2           de              1
    2           us              1

Query:

SELECT COUNT(DISTINCT(STR(NetworkId) + ',' + STR(CountryCode)))
FROM TestTable

Results in:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

I also tried the answers in this question: How can I count distinct multiple fields without repeating the query?

Query:

SELECT 
   NetworkId, 
   CountryCode,
   COUNT(*) OVER(PARTITION BY NetworkId, CountryCode) as DistCount
FROM TestTable
GROUP BY NetworkId, CountryCode

Result:

NetworkId   CountryCode    DistCount
    1           de              1
    1           us              1
    2           de              1
    2           us              1

As you can tell, I'm having a hard time figuring out how to do this... I would think it should be relatively simple, but I'm missing something.

score 3 · Answer 1 · answered Dec 11 '12 at 00:04

3

Unless I'm mistaken, this would work:

SELECT NetworkId, CountryCode, COUNT(Id) as DistCount
FROM TestTable
GROUP BY NetworkId, CountryCode

answered Dec 11 '12 at 00:04

Blorgbeard

101,031
48
228
272

OK, that works... let me see if it work in my actual query, because things are a bit more complicated there. – Kiril Dec 11 '12 at 00:05
Here's a backing [SQLFiddle](http://www.sqlfiddle.com/#!3/ce89b/3) too. (argh, just beaten) – Clockwork-Muse Dec 11 '12 at 00:06
@Lirik - If your query is considerably more complicated, you may need to show us the rest of it, so we can help you better. – Clockwork-Muse Dec 11 '12 at 00:08
@Clockwork-Muse It's a much bigger query, but it involves too much other stuff that's not relevant. This was the key part I was missing and it works like a charm now! – Kiril Dec 11 '12 at 00:10

score 3 · Accepted Answer · answered Dec 11 '12 at 00:05

If Id is unique and not null within TestTable (which it will be if it is the primary key), then this query will return the result set you specified:

SELECT NetworkId, CountryCode, Count(1) AS DistCount
  FROM TestTable 
 GROUP BY NetworkId, CountryCode
 ORDER BY NetworkId, CountryCode

However, if the Id column is not unique, and what you want is a count of distinct non-null Id values within each group, you can add the DISTINCT keyword:

SELECT NetworkId, CountryCode, Count(DISTINCT Id) AS DistCount
  FROM TestTable 
 GROUP BY NetworkId, CountryCode
 ORDER BY NetworkId, CountryCode

Given your sample data, both queries will return the same result. There will be a difference only if you have duplicate Id values within a group.

@Lirik: I typically use `COUNT(1)` when what I want to return is a count of rows. Note that this returns a result equivalent to `SUM(1)`, and that makes sense conceptually, because for each row that is included in the group, we're essentially incrementing a row counter by 1. When we use some other expression, e.g. `COUNT(Id)`, it's only going to increment the counter for non-NULL values. Which is equivalent to `SUM(IF Id IS NULL THEN 0 ELSE 1)`. When we add the `DISTINCT` keyword e.g. `COUNT(DISTINCT Id)`, now we're only incrementing the row counter when a non-NULL value is not already include — spencer7593, Dec 11 '12 at 00:15

Count rows with multiple aggreagate columns

Attempted Queries

2 Answers2