A little missunderstood in GROUP BY ln SQL SERVER

for example i tried to view how much news posted grouping by week in year 2009


     SELECT 
        DATEPART(WK,DatePosted) AS WeekNumber, 
        COUNT(NewsID) AS NewsPosts
     FROM News
     WHERE DATEPART(YYYY, dbo.News.DatePosted) = 2009
     ORDER BY WeekNumber



you will get the warning message

error , Line 5
Column 'News.DatePosted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


what's wrong? yup you need to add the group by
so we are going to add the group by

    SELECT
        DATEPART(WK,DatePosted) AS WeekNumber,
        COUNT(NewsID) AS NewsPosts
    FROM News
    WHERE DATEPART(YYYY, dbo.News.DatePosted) = 2009
    GROUP BY WeekNumber
    ORDER BY WeekNumber

still error? try to use the column instead, it's because sql server group by only regonise the column in group by not alias (like in My SQL)


Get list of Ms Access Table

Sub ListAccessTables2(strDBPath)
   Dim cnnDB As ADODB.Connection
   Dim rstList As ADODB.Recordset

   Set cnnDB = New ADODB.Connection

   ' Open the connection.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   ' Open the tables schema rowset.
   Set rstList = cnnDB.OpenSchema(adSchemaTables)

   ' Loop through the results and print the
   ' names and types in the Immediate pane.
   With rstList
      Do While Not .EOF
         If .Fields("TABLE_TYPE") <> "VIEW" Then
            Debug.Print .Fields("TABLE_NAME") & vbTab & _
               .Fields("TABLE_TYPE")
         End If
         .MoveNext
      Loop
   End With
   cnnDB.Close
   Set cnnDB = Nothing
End Sub




I must...

"I must learn to love the fool in me--the one who feels too much, talks too much, takes too many chances, wins sometimes and loses often, lacks self-control, loves and hates, hurts and gets hurt, promises and breaks promises, laughs and cries.

It alone protects me against that utterly self-controlled, masterful tyrant whom I also harbor and who would rob me of my human aliveness, humility, and dignity but for my Fool."

-Theodore I. Rubin-


JQuery Free E book


Better Interaction Design and Web Development with Simple JavaScript Techniques
  • An introduction to jQuery that requires minimal programming experience
  • Detailed solutions to specific client-side problems
  • For web designers to create interactive elements for their designs
  • For developers to create the best user interface for their web applications
  • Packed with great examples, code, and clear explanations
  • Revised and updated version of the first book to help you learn jQuery



Live Like You're Dying


One of these days you’ll be
under the covers you’ll be
under the table and you’ll realize
all of your days are numbered;
all of them one to one hundred.
All of them millions.
All of them trillions.
So what are you gonna do with them all?
You can not trade them in for mall.
no no