understanding SQL counting and grouping

[miconian]

So I have a table called [2012 raw], and it has many fields, two of which are [Capture ID] and [Internet Site].

I want to count how many internet sites there are per capture ID.

So I created this query:

SELECT [2012 raw].[Capture ID], Count([2012 raw].[Internet Site]) AS [CountOfInternet Site]
FROM [2012 raw]
GROUP BY [2012 raw].[Capture ID];

But the counting numbers are way too high. Clearly, there is some kind of double counting going on.

Now, there are other fields besides the ones listed above. Lots of them. And there are some pairs of records that have the same data for Internet Site and Capture ID, but different data for the other fields.

But those other fields are not included in the query. I'm confused about how to prevent the double counting.

So what I end up doing in cases like this, is that first I'll do a query where I just group both fields, like this:

SELECT [2012 raw].[Capture ID], [2012 raw].[Internet Site]
FROM [2012 raw]
GROUP BY [2012 raw].[Capture ID], [2012 raw].[Internet Site];

...and then I'll run that as a make-table, and then I'll run the count query against that new table, and then I'll get the results I want.

Is that how it's typically done? Obviously I'm not very good at SQL and need help understanding the order of operations.

thanks...

 

[xenou]

Hi,
You are on the right track with your solution described above - the idea to first group the items, then count the grouped items...

Another solution:

SELECT t.[Capture ID], Count(t.[Internet Site]) As [CountOfInternet Site]
FROM (
    SELECT DISTINCT [2012 raw].[Capture ID], [2012 raw].[Internet Site]
    FROM [2012 raw]
    ) AS t
GROUP BY t.[Capture ID];

Using Distinct in the subquery creates the subset of unique captureID-InternetSite items, which you then can count.

Note that in Access I quite often use two physical queries rather than a query with a subquery - so I'd say your original solution is good on that score too. Access doesn't always perform well with subqueries.

HTH,
ξ

 

[Joe4]

What do you see if you run this query?

SELECT [2012 raw].[Capture ID], [2012 raw].[Internet Site]
FROM [2012 raw]
ORDER BY 1,2;

Do you see what looks like a lot of duplicate records? If so, that is because you have a lot of records that may have these two fields the same, and the other fields you are leaving out may be different. It is important to understand that not displaying those other fields does NOT alter the record count in any way.

Now, if you just want to count the number of different, unique Internet Sites associated with each Capture ID (weeding out all the duplicates), you will need to get rid of those duplicates first (you can use an Aggregate Query or use the DISTINCT statement with your select), So you will either need to do this in two queries, or one nested query. The nested query might look something like this:

SELECT [Query1].[Capture ID], Count([Query1].[Internet Site]) as [CountOfInternet Site]
FROM
(SELECT DISTINCT [2012 raw].[Capture ID], [2012 raw].[Internet Site]
FROM [2012 raw]) as Query1
GROUP BY [Query1].[Capture ID];