[RESOLVED] What exactly does "GROUP BY" do?

mband2000

I've been searching google and phpbuilder.... but I still can't grasp the whole concept of GROUP BY.

I wanted to know if anyone could give me a real world example of how GROUP BY can help me... and how it works.

Thanks

nemonoman

Boy, do I LOVE GROUP BY!

Assume a table of invoice values

InvoiceID
InvoiceDate
CustomerID
SalesmanID
Amount

Suppose you wanted to see a summary of all invoice amounts by date

SELECT InvoiceDate, sum(amount)
FROM invoice
GROUP BY InvoiceDate

Suppose you wanted to see a summary of all invoice amounts by Customer

SELECT CustomerID, sum(amount)
FROM invoice
GROUP BY CustomerID

Ssummary of all invoice amounts by Salesman

SELECT SalesmanID, sum(amount)
FROM invoice
GROUP BY SalesmanID

You can get more interesting by using JOINS

Assume each salesman is in a region table
SalesmanID
RegionID

This would summarize regions:

SELECT RegionID, sum(amount)
FROM invoice
inner join region on invoice.SalesmanID=region.SalesamanID
Group by RegionID

Assuming a customer table
CustomerID
Zipcode

This would summarize by zipcode:

SELECT Zipcode, sum(amount)
FROM invoice
inner join customer on invoice.customerID=customer.ID
Group by Zipcode

Here's a fun one: same code, slightly modified summarizes by metro area:

SELECT Substring(Zipcode,1,3), sum(amount)
FROM invoice
inner join customer on invoice.customerID=customer.ID
Group by Substring(Zipcode,1,3)

etc

mband2000

nemonoman thank you very much for the help.. but honestly I still don't understand What exactly Group by is doing.

I understand you query... but what I'm not understanding is what it would output or why you would want it to output using that query.

I'm not really a SQL expert.. so if you can break it down for me on a 4th grade level that would be great... lol 🙂

But again thanks for your help so far.

thorpe

Another example. Say you have a table with the fileds name and points. Now in that table you have the records.

bob 40
ben 10
tam 10
bob 30
ben 5

Now, you want to add all a users points together to get a total points for each user.

SELECT SUM(points) AS points, name FROM tbl GROUP BY name;

produces...

ben 15
bob 70
tam 10

See how it groups the results together by name?

bpat1434

Group by does exactly what it says: it groups the response by the column.

So say you have this set of data:

id  |  Number  |  General Text
------------------------------
 1  |    2     |  Somethign here
 2  |    5     |  Something here too
 3  |    1     |  Another something
 4  |    2     |  Something here 2
 5  |    1     |  Another something 2
 6  |    5     |  Something here too 2

If you execute this query:

SELECT id 
  FROM table 
  GROUP BY Number ASC
  ORDER BY id ASC

Executing that code would give you this result set:

id  |  Number  |  General Text
------------------------------
 3  |    1     |  Another something
 5  |    1     |  Another something 2
 1  |    2     |  Somethign here
 4  |    2     |  Something here 2
 2  |    5     |  Something here too
 6  |    5     |  Something here too 2

So you see? GROUP BY actually groups the results by whatever column it is, and sorts them within that group.

Better?

mband2000

Yes yes!

Now I understand it perfectly... that can really come in handy! I appreciate all of your help everyone.

Thanks.