SQL Server - Group By Example

Common rule:

---

1. columns without aggregate function, must have to add into GROUP BY clause
2. to filter by aggregate value, have to use having & agg. columns based on requirements

Derived column / Calculated Column:

---

The Derived Column transformation creates new column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input.

SQL Server code:

---

select sh.TerritoryID

   ,sh.CustomerID

   ,sh.DueDate

   ,sum(sh.TaxAmt) totalTax

   ,sum(sh.SubTotal) totalSales

from Sales.SalesOrderHeader sh

where sh.TerritoryID in (4,5,6) and

  YEAR(sh.DueDate)='2006' and   MONTH(sh.DueDate) = '6' and

  datepart(YY, DueDate) = '2006' and --datepart

  DATEDIFF(mm, '2006-06-01', DueDate) = 0

group by sh.TerritoryID, sh.CustomerID, sh.DueDate

having sum(sh.SubTotal) > 10000 and

  sum(sh.SubTotal) > 4000  --alias doesn't support

--order by sh.SubTotal desc, sh.TaxAmt desc

order by 4 desc, 3 desc

Table:

---