Re: How to summarize recordset...Select Distinct alternative?

You need to learn the full power of SQL.
One you have that, a fair proportion of what you want to do can be done in
that.

So you need to master GROUP BY clauses and HAVING clauses and the aggregate
functions that can apply to fields not in the GROUP BY clause

Note: "GROUP BY" does not imply order.
If you get things ordered, that is the luck of the draw.
If you want order, or sorting, use ORDER BY clause to guarantee a result.

Suppose you have a table MyTable and fields Fld1, Fld2, Fld3 all integer and
a PK of Fld1, Fld2, Fld3

Then with data

Fld1, Fld2 Fld3
1 1 1
1 2 1
1 3 1
1 3 2
1 3 4
1 4 1
2 1 4
2 1 5
2 2 4
2 2 5
2 3 5
3 1 2

then

Example 1)
SELECT DISTINCT Fld1 FROM MyTable
might give

Fld1
3
1
2

SELECT DISTINCT Fld1 FROM MyTable ORDER BY 1
would give

Fld1
1
2
3

SELECT Fld1 FROM MyTable GROUP BY Fld1
might give

Fld1
3
1
2

There is no distinction between SELECT DISTINCT and GROUP BY- they
effectively make it unique within what is selected

Example 2)
SELECT Fld1,COUNT(Fld2) AS Fld2Count
FROM MyTable
GROUP BY Fld1
ORDER BY Fld1

would give

Fld1, Fld2Count
1 6
2 5
3 1

The Fld2Count is the number of times Fld2 appears for each Fld1 value

Example 3)
SELECT Fld1,COUNT(DISTINCT Fld2) AS Fld2Count
FROM MyTable
GROUP BY Fld1
ORDER BY Fld1

would give

Fld1, Fld2Count
1 4
2 3
3 1

The Fld2Count is the number of times unique time Fld2 appears for each Fld1
value
All of the aggregate functions SUM, AVG, MIN, MAX can have DISTINCT applied
within

Example 4)
SELECT Fld1,COUNT(Fld2) AS Fld2Count
FROM MyTable
GROUP BY Fld1
HAVING COUNT(Fld2) > 1
ORDER BY Fld1

would give

Fld1, Fld2Count
1 6
2 5

Compare to Example 2. The HAVING clause works after rows are Grouped. It
removes rows that no longer qualifies.
It acts as a filter. The HAVING clause is similar to WHERE.
HAVING is a companion to a GROUP BY clause.
After you have done a GROUP BY, you might want remove newly GROUPed rows by
some criterion.
That is what HAVING is for.

Example 5)
SELECT Fld1,Fld2
FROM MyTable
GROUP BY Fld1

This is an error. It wont work. You cant SELECT Fld2 as it is not part of
the GROUP BY clause.
SQL is asking you - in the event of duplicate Fld1 values, which Fld2 value
should be displayed?
The only way Fld2 can be retrieved is if you make if part of the GROUP BY
clause or use an Aggregate function

Example 6)
SELECT Fld1,MIN(Fld2) as MinFld2
FROM MyTable
GROUP BY Fld1

This will work as the minimum value of Fld2 for each duplicate Fld1 value is
displayed. MIN is an aggregate function.
It might give

Fld1 MinFld2
3 1
1 1
2 1

Stephen Howe


.

Relevant Pages

  • case clause
    ... In my query, I want to be able to put a conditional statement in the 'where' ... clause. ... case when fld1> 100 then ...
    (microsoft.public.sqlserver.programming)
  • Problem on seeking a composite index !
    ... CREATE UNIQUE INDEX idx_Key ON TBL1 (Fld1, Fld2) ... // Set the Fld1 and Fld2 data to the pData ...
    (microsoft.public.sqlserver.ce)
  • ActualSize returns -1 for Text fields
    ... I can't read the contents of the text field (Fld1). ... Same is true if I use the query explicitly listing the ... select Fld1, Fld2 from mytable ... But while this workaround works in this trivial case, ...
    (microsoft.public.data.ado)
  • Re: two queries in one
    ... SELECT fld1, fld2, fld3 FROM table WHERE something ... INSERT INTO Table2 ...
    (microsoft.public.access.queries)
  • Re: iif Function in Query
    ... Duane Hookom ... MS Access MVP ... What I want to happen, is to see the data found in FLD2 if FLD1 is blank, ... FLD1 is a Date/Time and FLD2 is a Text fld. ...
    (microsoft.public.access.queries)