Re: Group By?!

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Brendan Reynolds (brenreyn)
Date: 02/02/05 

Date: Wed, 2 Feb 2005 15:44:40 -0000

"Newb-Lost-In-A-Sea-Of-Tables"
<Newb-Lost-In-A-Sea-Of-Tables@discussions.microsoft.com> wrote in message
news:64D84DEA-2F4A-44DA-93B7-4767DADFAFAC@microsoft.com...
<snip>
> In this statement, the group by clause added shortens the amount of data
> displayed. If I were to take out that huge group by clause, I get more
> data
> displayed. I wanted to know why the Group By clause lessens the amount of
> data shown.

Let's try and demonstrate the principle with a simpler query. Take the
Orders table from the Northwind database. Create a simple query that selects
the CustomerID and EmployeeID like so ...

SELECT Orders.CustomerID, Orders.EmployeeID
FROM Orders;

This query will return one row for every record in the Orders table, 830
rows in my copy of Northwind.

Now lets group by CustomerID and EmployeeID, like so ...

SELECT Orders.CustomerID, Orders.EmployeeID
FROM Orders
GROUP BY Orders.CustomerID, Orders.EmployeeID;

This query will return one row for each unique combination of CustomerID and
EmployeeID, 465 rows in my copy of Northwind. For example, in my copy of
Northwind, Nancy Davolio has taken two orders for customer 'Alfreds
Futterkiste'. The original query, without grouping, will return these two
rows. The second query, with grouping, will return one row for the
combination Alfreds Futterkiste/Nancy Davolio, and one row for each other
unique combination of CustomerID and Employee. 

-- 
Brendan Reynolds (MVP)

Relevant Pages

  • Re: finding overlaps
    ... Create a query into this table. ... Drag MeetingDate and CustomerID into the grid. ... >> Accept Group By in the Total row under these fields. ... Drag EmployeeID into the grid. ...
    (microsoft.public.access.queries)
  • Re: join method
    ... Please post the SQL statement of your query. ... Microsoft Office/Access MVP ... if I join on CustomerID field I can get this: ... if I join on both CustomerID and EmployeeID I only will get the first ...
    (microsoft.public.access.queries)
  • Re: finding overlaps
    ... Create a query into this table. ... Drag MeetingDate and CustomerID into the grid. ... Accept Group By in the Total row under these fields. ... Drag EmployeeID into the grid. ...
    (microsoft.public.access.queries)
  • Re: remote sql query
    ... Perhaps it's the location of the WHERE clause? ... OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM ... only getting the qualified rows returned to the local server. ... The query returns only a few rows. ...
    (microsoft.public.sqlserver.connect)
  • Q: How to make correlated subquery do what I want?
    ... Here's a query to run against Northwind. ... select employeeid, customerid, orderdate ...
    (microsoft.public.sqlserver.programming)