VistaDB 5

Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions. One row is returned for each group. Aggregate functions in the SELECT clause <select> list provide information about each group instead of individual rows.

GROUP BY is a clause on a SELECT statement to control how the results are grouped when used with aggregate functions in the summary rows.

When a column is used in an aggregate function the column may be included in a group by statement to ensure that the ordering and intent of the aggregate are enforced.


As an example let's consider a situation where you want to summarize all the invoices for a given month from a sales table, but you want to see them per day of the sale. You also want to ensure that all invoices for the same date are counted together, and you want to total up the sales for the day in a single value.

Group By Example
Copy Code
declare @startdate datetime;
declare @enddate datetime;

set @startdate = '2009-09-15';
set @enddate = '2009-10-15';

select ISNULL(sum(totalpaid),0) as DailyTotal, ISNULL(count(invoiceid),0) as Invoices,
   DATEPART( day, createddate) as SaleDay,
   DATEPART( month, createddate) as SaleMonth,
   DATEPART( month, createddate) as SaleYear
from invoices 
where createddate >= @startdate and createddate <= @enddate and Void <> 1
group by
   DATEPART( day, createddate ),
   DATEPART(month, createddate),
   DATEPART( year, CreatedDate)
order by SaleYear 

The group by has to match the same part splits as what is performed in the row, but you can't use the row alias themselves (SQL Server doesn't allow it).

See Also