Geek asks and answers

SQL Server Group by / order by

I have the following request

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 row, and therefore its ordering puts August through January, etc.

The solution to this was as follows:

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 sections and how it works.

As I understand it, group by creates a worksheet with 4 columns (this may be wrong) datepart(yy, orderdate), datename(mm, orderdate), datepart(mm, orderdate)and a column count.

Every time he encounters orderdatewhich he has in the worksheet, he increments the counter, otherwise she adds a new row?

Initially, I thought that I could remove datename(mm, orderdate)from the group by sections, but the book says that this is impossible.

- , / , , , .

.

+5
1

(COUNT, SUM, MAX ..) GROUP BY. COUNT , datepart(yy, orderdate), datename(mm, orderdate), datepart(mm, orderdate).

:

SELECT col1, col2, col3, MAX(col4)
FROM MyTable
GROUP BY col1, col2, col3

:

1,2,3,9
1,2,5,9

:

SELECT col1, col2, MIN(col3), MAX(col4)
FROM MyTable
GROUP BY col1, col2

:

1,2,3,9

, Aggregate col3 (MIN), col3 GROUP BY.

+7

More articles:


All Articles