Count distinct in different columns

[fransanchezoria]

Hi,

As the title says, I need to do a count for distinct values in two columns. For example:

IPC Family ID Another ID
A01C 40059407 4764
A01C 41665653 399
A01C 41665653 1941
A01D 34009322 8415
A01D 34035545 8533
A01D 34035545 8530
A01D 34035545 8531
A01D 38043120 5580

The desired result would be:


IPC Count ofFamily ID CountofanotherID
A01C 2 3
A01D 3 5

I managed to do a distinct count in the first column with a subquery, however I can't get to work the distinct count for the second one.

This is the code I have so far:

SELECT [Chalmers Patents (w2 & namecheck)].[IPC Subclass] AS Expr1, Count([Chalmers Patents (w2 & namecheck)].[Family ID]) AS [Unique patents], Count([Chalmers Patents (w2 & namecheck)].[ID]) AS Applications
FROM (SELECT DISTINCT [Chalmers Patents (w2 & namecheck)].[Family ID], [Chalmers Patents (w2 & namecheck)].[ID], [Chalmers Patents (w2 & namecheck)].[IPC Subclass], [Chalmers Patents (w2 & namecheck)].[Name check] FROM [Chalmers Patents (w2 & namecheck)])  AS [%$##@_Alias]
GROUP BY [Chalmers Patents (w2 & namecheck)].[IPC Subclass], [Chalmers Patents (w2 & namecheck)].[Name check]
HAVING ((([Chalmers Patents (w2 & namecheck)].[IPC Subclass]) Is Not Null) AND (([Chalmers Patents (w2 & namecheck)].[Name check])="check"));

Thank you beforehand!

 

[Galaxiom]

Counts will have to be done in separate subqueries with only the IPC and relevant ID for each then the results joined on IPC.

 

[fransanchezoria]

Counts will have to be done in separate subqueries with only the IPC and relevant ID for each then the results joined on IPC.

Thanks for your answer Galaxiom. I think I know what you mean, but I'm quite a newbie writing stuff in SQL, could you give me an example of how to join two subqueries?

Thanks again beforehand!

 

[Taruz]

select 
           [IPC Subclass],
 (
       select count([Family ID]) from [Chalmers Patents (w2 & namecheck)] where [Family ID]=trz.[Family ID] ) as [Count ofFamily ID], 
 (
        select count([ID]) from [Chalmers Patents (w2 & namecheck)] where [IPC Subclass]=trz.[IPC Subclass] ) as CountofanotherID

from [Chalmers Patents (w2 & namecheck)] as trz

group by [IPC Subclass], [Family ID]

having count([IPC Subclass])>1

Hi...

Use this query, let's see what will happen..

 

[Galaxiom]

If the structure of a subquery is giving you grief, simply make two separate queries and then base a third one on them. It isn't pretty but it is easier until you become fluent in sql.