SQL Server group by/ order by
sql-server, t-sql
Solution
Any time you use an aggregate function (COUNT,SUM,MAX, etc.) you need to include all other columns in a GROUP BY clause. COUNT in your example is returning the number of records that have the same value for `datepart(yy, orderdate)`, `datename(mm, orderdate)`, `datepart(mm, orderdate)`.
An example:
SELECT col1, col2, col3, MAX(col4)
FROM MyTable
GROUP BY col1, col2, col3
Say this returned:
1,2,3,9
1,2,5,9
If you changed your query to this:
SELECT col1, col2, MIN(col3), MAX(col4)
FROM MyTable
GROUP BY col1, col2
It would return:
1,2,3,9
Notice that I added an Aggregate function to `col3` (MIN) so I was able to remove `col3` from my GROUP BY clause.
Problem
I have the following query ``` select datepart(yy, orderDate) as 'year', datename(mm, OrderDate) as 'month', count(*) as 'Orders' from orders (yeah I know its missing the schema, its from a book) group by datepart(yy, orderdate), datename(mm, orderdate) order by datepart(yy, orderdate), datename(mm, orderdate); ``` which returns 3 columns but `datename(mm, orderdate)` returns a string and therefore ordering by it puts August before January etc. The solution to this was the following: ``` select datepart(yy, orderDate) as 'year', datename(mm, OrderDate) as 'month', count(*) as 'Orders' from orders (yeah i know its missing the schema, its from a book) group by datepart(yy, orderdate), datename(mm, orderdate), datepart(mm, orderdate) order by datepart(yy, orderdate), datepart(mm, orderdate); ``` I'm still a little confused with the whole group by / order by sections and how it actually works. As far as I have understood, group by is creating a work table with 4 columns (that might be wrong) `datepart(yy, orderdate), datename(mm, orderdate), datepart(mm, orderdate)`, and a `count` column. Each time it encounters an `orderdate` that it has in the work table it increases the count, otherwise it adds a new row? Originally I thought I could remove the `DateName(mm, orderdate)` from within the group by section but the book said that's not possible. If someone could step through what actually happens behind the scenes/point out a resource which explains how this works in a little more detail id appreciate it. thanks for your help.