help needed counting unique values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Given this tabel as an example I would like to produce a query counting the
unique number of column 3 (drinks) for cloumn a and b.

Table:

1 2 3
a b beer
a b beer
a b cola
a b fruit
b g beer
b g cola
b g cola
b g cola
b g beer

I can get this Query

1 2 CountOf3
a b 2
a b 1
a b 1
b g 2
b g 3

but i want this one


1 2 CountOf3
a b 3 for a and b we have 3 unique
drinks
b g 2 for b and g we have 2 unique
drinks


Ths for your help!
 
Dear Hard:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable) T
WHERE [1] IN ("a", "b")
GROUP BY [1], [2]
ORDER BY [1], [2]

Look at the subquery:

SELECT DISTINCT [1], [2], [3] FROM YourTable

This eliminates the duplication so you can count unique values.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Is it possible to put the in ("a", "b") in a subquery as well?

Tom Ellison said:
Dear Hard:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable) T
WHERE [1] IN ("a", "b")
GROUP BY [1], [2]
ORDER BY [1], [2]

Look at the subquery:

SELECT DISTINCT [1], [2], [3] FROM YourTable

This eliminates the duplication so you can count unique values.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hello,

Given this tabel as an example I would like to produce a query counting
the
unique number of column 3 (drinks) for cloumn a and b.

Table:

1 2 3
a b beer
a b beer
a b cola
a b fruit
b g beer
b g cola
b g cola
b g cola
b g beer

I can get this Query

1 2 CountOf3
a b 2
a b 1
a b 1
b g 2
b g 3

but i want this one


1 2 CountOf3
a b 3 for a and b we have 3 unique
drinks
b g 2 for b and g we have 2 unique
drinks


Ths for your help!
Click to expand...
Click to expand...
 
Dear Working:

Yes, like this:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable
WHERE [1] IN ("a", "b")) T
GROUP BY [1], [2]
ORDER BY [1], [2]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Is it possible to put the in ("a", "b") in a subquery as well?

Tom Ellison said:
Dear Hard:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable) T
WHERE [1] IN ("a", "b")
GROUP BY [1], [2]
ORDER BY [1], [2]

Look at the subquery:

SELECT DISTINCT [1], [2], [3] FROM YourTable

This eliminates the duplication so you can count unique values.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hello,

Given this tabel as an example I would like to produce a query counting
the
unique number of column 3 (drinks) for cloumn a and b.

Table:

1 2 3
a b beer
a b beer
a b cola
a b fruit
b g beer
b g cola
b g cola
b g cola
b g beer

I can get this Query

1 2 CountOf3
a b 2
a b 1
a b 1
b g 2
b g 3

but i want this one


1 2 CountOf3
a b 3 for a and b we have 3 unique
drinks
b g 2 for b and g we have 2 unique
drinks


Ths for your help!
Click to expand...
Click to expand...
Click to expand...
 
Thanx for you input that that still leaves me having to know the possible
values that will be in the IN clause "a" and "b". So how would I write a
more genric one ... valid for all possible values ... using a sub query?

Tom Ellison said:
Dear Working:

Yes, like this:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable
WHERE [1] IN ("a", "b")) T
GROUP BY [1], [2]
ORDER BY [1], [2]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Is it possible to put the in ("a", "b") in a subquery as well?

Tom Ellison said:
Dear Hard:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable) T
WHERE [1] IN ("a", "b")
GROUP BY [1], [2]
ORDER BY [1], [2]

Look at the subquery:

SELECT DISTINCT [1], [2], [3] FROM YourTable

This eliminates the duplication so you can count unique values.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts



Hello,

Given this tabel as an example I would like to produce a query counting
the
unique number of column 3 (drinks) for cloumn a and b.

Table:

1 2 3
a b beer
a b beer
a b cola
a b fruit
b g beer
b g cola
b g cola
b g cola
b g beer

I can get this Query

1 2 CountOf3
a b 2
a b 1
a b 1
b g 2
b g 3

but i want this one


1 2 CountOf3
a b 3 for a and b we have 3
unique
drinks
b g 2 for b and g we have 2 unique
drinks


Ths for your help!
Click to expand...
Click to expand...
Click to expand...
 
Dear Hard:

The IN clause is "valid for all possible value". Is this to be built
to work with a user interface, perhaps a multi-select combo box? I
would need some more specificity to be able to begin to comment
further.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Thanx for you input that that still leaves me having to know the possible
values that will be in the IN clause "a" and "b". So how would I write a
more genric one ... valid for all possible values ... using a sub query?

Tom Ellison said:
Dear Working:

Yes, like this:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable
WHERE [1] IN ("a", "b")) T
GROUP BY [1], [2]
ORDER BY [1], [2]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Is it possible to put the in ("a", "b") in a subquery as well?

Dear Hard:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable) T
WHERE [1] IN ("a", "b")
GROUP BY [1], [2]
ORDER BY [1], [2]

Look at the subquery:

SELECT DISTINCT [1], [2], [3] FROM YourTable

This eliminates the duplication so you can count unique values.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts



Hello,

Given this tabel as an example I would like to produce a query counting
the
unique number of column 3 (drinks) for cloumn a and b.

Table:

1 2 3
a b beer
a b beer
a b cola
a b fruit
b g beer
b g cola
b g cola
b g cola
b g beer

I can get this Query

1 2 CountOf3
a b 2
a b 1
a b 1
b g 2
b g 3

but i want this one


1 2 CountOf3
a b 3 for a and b we have 3
unique
drinks
b g 2 for b and g we have 2 unique
drinks


Ths for your help!
Click to expand...
Click to expand...
Click to expand...
 
Hello,

Thank you very much for your time. Yes it is to be used with values from a
multiselect listbox. I have tested it with a subquery and it works. I'm not
sure if this is the most solid/performant solution, perhpas I'm taking the
long route here.

The users selects the values from te listbox and also marks one as the one
for which the counting needs to be done. Based on that input i create the
query and save it.

This saved query is used for reports, exporting to exel displaying the
results to the user or even creating another query (example a join to
another query that has an ordinary group by query and now the want to see
the unique counts made bu our orignal query for their selected column)

I hope this is some what clear to you,

Thx
 
By the way the performance hit is very painfull, should I consider an
recordset based approach or is this even possible I should ask.

Tom Ellison said:
Dear Hard:

The IN clause is "valid for all possible value". Is this to be built
to work with a user interface, perhaps a multi-select combo box? I
would need some more specificity to be able to begin to comment
further.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Thanx for you input that that still leaves me having to know the possible
values that will be in the IN clause "a" and "b". So how would I write a
more genric one ... valid for all possible values ... using a sub query?

Tom Ellison said:
Dear Working:

Yes, like this:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable
WHERE [1] IN ("a", "b")) T
GROUP BY [1], [2]
ORDER BY [1], [2]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts



Is it possible to put the in ("a", "b") in a subquery as well?

Dear Hard:

SELECT [1], [2], COUNT(*) AS CountOf3
FROM (SELECT DISTINCT [1], [2], [3] FROM YourTable) T
WHERE [1] IN ("a", "b")
GROUP BY [1], [2]
ORDER BY [1], [2]

Look at the subquery:

SELECT DISTINCT [1], [2], [3] FROM YourTable

This eliminates the duplication so you can count unique values.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts



Hello,

Given this tabel as an example I would like to produce a query
counting
the
unique number of column 3 (drinks) for cloumn a and b.

Table:

1 2 3
a b beer
a b beer
a b cola
a b fruit
b g beer
b g cola
b g cola
b g cola
b g beer

I can get this Query

1 2 CountOf3
a b 2
a b 1
a b 1
b g 2
b g 3

but i want this one


1 2 CountOf3
a b 3 for a and b we have 3
unique
drinks
b g 2 for b and g we have 2
unique
drinks


Ths for your help!
Click to expand...
Click to expand...
Click to expand...
 
Hello,

FYI, I solved the performance option by creating a table with the results
for further use.

Best regards!
 
Back
Top