Group By Problem

[craigfw]

Greetings, I'm pretty slow at SQL for as long as I've been associated with SQL Server. But non the less...

I have a query that runs correctly until I attempt to add a group by. I know it must be something not too difficult, but, I'm at a loss.

select [Entry No_], [Sale No_], [Posting Date]
from "LARS-403-Test$Timber Ticket Ledger Entry"
where [Posting Date] between '2006-02-02' and '2006-12-02'

order by [Sale No_]
/*group by [Posting Date]*/

Entry No_ Sale No_ Posting Date
--------- --------- -----------------------
263015 SS500056 2006-02-03 00:00:00 000
263015 SS500056 2006-02-03 00:00:00 000
etc..

But when I introduce the group by clause I get:

select [Entry No_], [Sale No_], [Posting Date]
from "LARS-403-Test$Timber Ticket Ledger Entry"
where [Posting Date] between '2006-02-02' and '2006-12-02'

order by [Sale No_]
group by [Posting Date]

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'group'.

OR

if the group by is before the order by then I get this:

Server: Msg 8120, Level 16, State 1, Line 2
Column 'LARS-403-Test$Timber Ticket Ledger Entry.Entry No_' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[LKBrwnDBA]

You have to GROUP BY all columns not "AGGREGATED":

select  [Entry No_], [Sale No_], [Posting Date]
from "LARS-403-Test$Timber Ticket Ledger Entry"
where [Posting Date] between '2006-02-02' and '2006-12-02'

order by [Sale No_]
group by [Entry No_], [Sale No_],[Posting Date]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: The above is equivalent to:

SELECT DISTINCT ... FROM...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[craigfw]

Thanks for the feedback. You're correct about the not agregating (Is that a word?)the columns which are in the Select into the Where clause.