SQL Query for DISTINCT Records

[keiem]

Lets say I have three fields in a table. They are:

[SITUSNUM]
[SITUSNAME] and
[STYLE1]

The [SITUSNUM] and [SITUSNAME] fields contain street address values, some of which are duplicates. I would like to have a select query find the unique addresses. Then I would like to generate a count of the values in [STYLE1] for the records in the resulting list.

Thus far, I have figured out how to use SELECT DISTINCT to query a unique list of values, and I know how to use DCOUNT() to count the values in the [STYLE1] field. What I haven't figured out, is how to associate the [STYLE1] with the results of the SELECT DISTINCT query of the other two fields without skewing the results.

I have played around with subqueries a little, as well as querying from another query. Unfortunately, I don't seem to have the syntax and/or have enough understanding of the relationships to achieve what I'm after.

I'm looking for advice, and possible solution. My database design is actually a bit more complicated than what I've presented, but the above should serve to start the discussion.

Thanks!

 

[jmeadows7]

Ok - I created the SQL through the query builder to demonstrate this:
The table i created for the test is
SITUSNUM - TEXT
SITUSNAM - TEXT
STYLE1 - TEXT

The test data is as follows:

SITUSNUM SITUSNAM STYLE1
test test style1
test test style1
test test style2
test2 test2 style1
test2 test2 style1
test2 test2 style2
test2 test2 style3
test3 test3 style8

The SQL builder built the following:

SELECT zzz.SITUSNUM, zzz.SITUSNAM, zzz.STYLE1, Count(zzz.STYLE1) AS CountOfSTYLE1
FROM zzz
GROUP BY zzz.SITUSNUM, zzz.SITUSNAM, zzz.STYLE1;


The result is:

SITUSNUM SITUSNAM STYLE1 CountOfSTYLE1
test test style1 2
test test style2 1
test2 test2 style1 2
test2 test2 style2 1
test2 test2 style3 1
test3 test3 style8 1

I think this is what you are looking for - HTH.

Jimmy

 

[keiem]

This is almost what I'm looking for, but not quite. In end it may serve my purposes, though. I am able to achieve the same result by using the DISTINCT function, then counting the results.

What I'm really after, is if there is a way to query the unique values of SITUSNUM/SITUSNAME first. Then count the values of various other fields associated with them, including STYLE1. In the example you provided, the SQL statement counts the unique values of all three fields. That is:

test test style1 occurs 2 times
test test style2 occurs only once
test2 test2 style 1 occurs two times, etc.

This works fine, except that there is no guarantee that all of the field combinations are consistently the same. I may have to create a separate query to count each combination of three fields that I need statistics for, i.e. not just STYLE1, but also MASSING1, WINDOWS1, etc. etc.

What I would like to do is find the unique values for the first two fields (SITUSNUM & SITUSNAME) and then count the STYLE1 values exclusively from the query results.

For example, if I have the table data:

SITUSNUM SITUSNAME STYLE1
test test style1
test test style1
test test style2
test2 test2 style1
test2 test2 style1
test2 test2 style2
test2 test2 style3
test3 test3 style8

The answers I'm looking for are:

test test style2
test2 test2 style1
test3 test3 style8

followed by:

style1 = 2
style2 = 2
style3 = 1
style8 = 1

I know that the logic of "why" I want it this way may not make sense. Theoretically, all of the SITUSNAME/SITUSNUM combinations should have the same STYLE1, MASSING1, etc. So I may end up just doing what you suggested above.

Also, when I tried the script you provided, I received the following error: "You tried to execute a query that does not include the specified expression 'FIELDNAME' as part of an aggregate function."

Thanks!

 

[keiem]

Oops. Just noticed a typo in my previous post...

The part, "the answers I'm looking for are:

test test style2
test2 test2 style1
test3 test3 style8"

Instead, should be

test test
test2 test2
test3 test3