Tutorial by Examples

Orders Table CustomerIdProductIdQuantityPrice12510013220014150021450356700 When grouping by a specific column, only unique values of this column are returned. SELECT customerId FROM orders GROUP BY customerId; Return value: customerId123 Aggregate functions like count() apply to each group...

One might want to GROUP BY more than one column declare @temp table(age int, name varchar(15)) insert into @temp select 18, 'matt' union all select 21, 'matt' union all select 21, 'matt' union all select 18, 'luke' union all select 18, 'luke' union all select 21, 'luke' union all select 1...

Group by is often used with join statement. Let's assume we have two tables. The first one is the table of students: IdFull NameAge1Matt Jones202Frank Blue213Anthony Angel18 Second table is the table of subject each student can take: Subject_IdSubject1Maths2P.E.3Physics And because one student c...

Because the WHERE clause is evaluated before GROUP BY, you cannot use WHERE to pare down results of the grouping (typically an aggregate function, such as COUNT(*)). To meet this need, the HAVING clause can be used. For example, using the following data: DECLARE @orders TABLE(OrderID INT, Name NVA...

The ROLLUP operator is useful in generating reports that contain subtotals and totals. CUBE generates a result set that shows aggregates for all combinations of values in the selected columns. ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected col...