Home page
 
 Home 
 ASP 
 PHP 
 SQL 
 HTML 
 JavaScript 
 Search 
 Contact 
 
Search
or browse popular tags
Subscription

Sign up for the free email newsletter for new tips, tutorials and more. Enter your email address below, and then click the button.

Privacy Policy

RSS Twitter

SQL: GROUP BY

Print

The aggregate functions, such as Sum, Count, Min, or Max are used to produce grand totals. Values output by them are just like the totals that appear at the end of each column listing in a report. You can also use these functions to output sub-total values. The Group By clause of the Select statement lets you split up the values in a column as a whole. It combines into a single record all records that have identical values in a particular field or combination of fields. The Group By clause can group the results by one or more columns.

The syntax for an Group By statement is as follows:

SELECT column_name_1,...,column_name_n, aggregate_function (expression)
FROM table_name
[WHERE condition]
GROUP BY column_name1,...,column_name_n

The Where clause is optional. If a Where keyword presents in SQL statement, it comes before GROUP BY clause.

Example #1

Select Item, Sum(Price) as TotalSum
From Antiques
Group by Item

This query returns a list of different items and a field called 'TotalSum' that counts the result sum of antiques that supply each item.

Example #2

Select Item, Max(Price) as MaxPrice
From Antiques
Group by Item

This will return names of each item and the maximum price for it.

Example #3

Select SellerID, Count(*) as Number_of_Sellers
From Antiques
Where BuyerID=15
Group by SellerID

The preceding example returns a list of seller's ids and a field called Number_of_Sellers that counts the number of sellers whose BuyerID equal to 15.


If you'd like to see how it works, enter SQL statement and press Execute
 




Tags:

Add To: Add to dzone dzone | Digg this digg | Add to del.icio.us del.icio.us | Stumble it stumbleupon

  • Comments





Copyright © 2005-2014             www.WebCheatSheet.com All Rights Reserved.