Show distinct value based on one field (1 Viewer)

[tmyers]

I am having a slight problem with one of my queries. It is just a "history" query that shows all quotes starting from yesterday into the past. The problem is that a quote number can exist multiple times (the JobID however is still unique to each one). When a quote number shows up multiple times, there are different "packages" for that quote. For the purposes of this query however, I need to only show it once. How can I get that to happen?

As you can see, one is showing up twice. Can I somehow drill it down to only show a given quote number once?
Here is the current SQL behind the query:

SELECT DISTINCT First(tblJobDetails.QuoteNum) AS FirstOfQuoteNum, tblJobDetails.JobName, tblJobDetails.BidDate, tblUsers.FirstName, tblJobDetails.CompletedDate, tblJobDetails.JobAwarded, tblJobDetails.JobLost
FROM tblUsers INNER JOIN tblJobDetails ON tblUsers.UserID = tblJobDetails.UserID
GROUP BY tblJobDetails.JobName, tblJobDetails.BidDate, tblUsers.FirstName, tblJobDetails.CompletedDate, tblJobDetails.JobAwarded, tblJobDetails.JobLost
HAVING (((tblJobDetails.BidDate)<Date()));

 

[Pat Hartman]

Something you are selecting is causing the quote to appear multiple times. Eliminate that field or aggregate it.

 

[Isaac]

Maybe if you only group by the two fields that duplicate?

 

[jdraw]

When a quote number shows up multiple times, there are different "packages" for that quote.

Sounds like there is some other factor involved -that is, quote number is not unique.
For a list of unique QuoteNum and JobName you could try

SELECT DISTINCT QuoteNum  & JobName
FROM tblJobDetails

 

[Gasman]

You are not showing the relevant picture?
I suspect also that your group on every field is causing the problem?

 

[tmyers]

Maybe if you only group by the two fields that duplicate?

Doing that seemed to correct it.

 

[Isaac]

Doing that seemed to correct it.

Glad to hear it's working !!

 

[Pat Hartman]

Maybe if you only group by the two fields that duplicate?

That's not the way totals queries work. All columns are aggregated either using functions or the group by clause so I'm not sure why following your suggestion "worked". Something else was at play.

 

[Isaac]

Pat, maybe we're just not on the same page yet I refer to one thing (not well explained at all, I admit) and you refer to another.

If you group by only 2 of the fields, and then use a random function like 'first' on the other two (say 4 total), you may get the result you want.
In this case the values will all come out correctly - but this won't necessarily work correctly for all data, just some.

Attached is an example where qry2 "gets rid of" the extra "duplicate" value (duplicate as in, the first 2 fields are dupes).

 

[Pat Hartman]

The aggregate query in your example aggregates ALL columns. That was the only point I was making. When you create a totals query, EVERY FIELD must be aggregated. That means either group by or some function. I have no doubt that changing the way a column is aggregated would change the results, that is what my original response said.

Something you are selecting is causing the quote to appear multiple times. Eliminate that field or aggregate it.

Only grouping by two fields doesn't solve the problem, it simply creates an error since ALL fields must be aggregated. tMyers' query contained a field that either shouldn't have been included at all or should have been aggregated differently. Since we don't know what the data looks like, we don't know the change in aggregation actually solved the problem or now causes a different one.

 

[Isaac]

Did you look at my uploaded sample? It works quite well and does not cause an error.

Note that I did not merely say, only aggregate by 2 columns. I said specifically only group by two columns.

That means either group by or some function

Right - either one or the other. Group by the first two, use a function on the 2nd two, as my uploaded sample demonstrates.

 

[Pat Hartman]

Isaac, I am not criticizing your example and I didn't say that it caused an error. Your original comment to just group by two of the many columns selected would not work. The point, again, is that EVERY column selected in a totals query must be aggregated somehow. That's all. If you select 10 columns, you can't just group by two of them. You have to aggregate all of them either by grouping or using a function.

 

[Isaac]

you can't just group by two of them.

Well, but you can. You can just group-by two of them, and aggregate the rest. But I see what you mean.

 

[Pat Hartman]

You can group by 0 fields or dozens of fields in a totals query. Grouping IS aggregating and every column must be aggregated. It is a very fine point that I am trying to make and I don't think we are on the same page yet. But I don't know how else to say it. So let's just move on I'm sure I am boring everyone so I'll stop.

 

[Isaac]

You have to aggregate all of them either by grouping or using a function.

"by grouping or using a function".

Group on 2, use a function on the rest.

Aggregation is the function performed on a group.

Grouping is not aggregating. Some columns are grouped. Other, different, columns, are aggregated within the group.

 

[Pat Hartman]

Maybe we're using different dictionaries. Here's three opinions.

aggregate
verb
ag·gre·gate | \ ˈa-gri-ˌgāt \
aggregated; aggregating
Definition of aggregate (Entry 2 of 3)
transitive verb
1: to collect or gather into a mass or wholeThe census data were aggregated by gender.
2: to amount to (a whole sum or total) : TOTALaudiences aggregating several million people

VERB

1. form or group into a class or cluster.
   "the butterflies aggregate in dense groups"
   synonyms
   combine · put · group · bunch · unite · pool · mix · blend · merge · mass · join · fuse · conglomerate · coalesce · consolidate · collect · throw · consider together

ADJECTIVE

1. formed or calculated by the combination of many separate units or items; total.
   "the aggregate amount of grants made"
   synonyms
   total · combined · whole · gross · accumulated · added · entire · complete · full · comprehensive · overall · composite

DEFINITION OF group

• nounnumber of individuals collectively
• verbbring together
• verbclassify, sort

SYNONYMS FOR group​

• association
• band
• body
• class
• club
• company
• crowd
• faction
• gang
• organization
• party
• society
• troop
• accumulation
• aggregation
• assemblage
• assembly
• assortment
• batch
• battery
• bevy
• bunch
• bundle
• cartel
• category
• chain
• circle
• clique
• clot
• clump
• cluster
• clutch
• collection
• combination
• combine
• conglomerate
• congregation
• coterie
• covey
• crew
• formation
• gathering
• grade
• league
• lot
• mess
• pack
• parcel

 

[Isaac]

When you create a query involving these techniques, some columns are in Group By, and aggregate functions are being performed on others.
There is no cross over. Period.

We are talking about database queries, not Merriam Webster.

 

[ebs17]

An approach that usually works much better: calculate first, then join.
Calculation:

SELECT
   MIN(JobID) AS MinJobID
FROM
   tblJobDetails
WHERE
   BidDate < Date()
GROUP BY
   QuoteNum

It is possible that more fields have to be grouped. In any case, it is filtered immediately and then the smallest JobID per group is determined. In order to get all fields of the table, the table is joined again. The tblUser table can now also be included. Alltogether:

SELECT
   JD.QuoteNum,
   JD.JobName,
   JD.BidDate,
   U.FirstName,
   JD.CompletedDate,
   JD.JobAwarded,
   JD.JobLost
FROM
   tblUsers AS U
      INNER JOIN
         (
            (
               SELECT
                  J.*
               FROM
                  (
                     SELECT
                        MIN(JobID) AS MinJobID
                     FROM
                        tblJobDetails
                     WHERE
                        BidDate < Date()
                     GROUP BY
                        QuoteNum
                  ) AS S
                     INNER JOIN tblJobDetails AS J
                     ON S.MinJobID = J.JobID) AS JD
         )
      ON U.UserID = JD.UserID

This query is more efficient than a grouping orgy over all fields.