Post reply

SQL error Group by

  • July 23, 2008 at 5:09 am

    #381872

    SELECT

    tbl_cat.collectionid,

    tbl_collection.name, tbl_cat.id,

    tbl_cat.catname, tbl_cat.stat_amount_cat_total,

    count(tbl_track_cat_period.trackid)

    FROM

    tbl_collection

    JOIN tbl_cat on tbl_collection.id = tbl_cat.collectionid

    JOIN tbl_track_cat_period on tbl_cat.id = tbl_track_cat_period.catid

    WHERE

    tbl_collection.groupid = 3 and

    tbl_collection.enabled = 1 and

    tbl_collection.exportdirectmusic = 1 and

    tbl_collection.currentperiod = tbl_track_cat_period.periodid group by tbl_cat.id

    When I try to execute this query i get the following error:

    Msg 8120, Level 16, State 1, Line 1

    Column 'tbl_cat.collectionid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    I want to group by tbl_cat.id to see how many tracks are in this category.

    Any help would be more then welcome, been looking at thsi for hours and I can't seem to find the problem!

    Thanks

  • July 23, 2008 at 5:22 am

    #846914

    HI,

    the problem is that u have not added all columns except the column name inside count function under group by

    .The rule is all columns except the columns related to aggregate functions have to be included in group by clause.

  • July 23, 2008 at 5:28 am

    #846923

    Euh I don't really understand, my knowlage is mainly mySQL and not MSSQL.

    Do you mean I have to put all collums into the group by?

  • July 23, 2008 at 11:16 pm

    #847523

    Yes all columns except the column which is added in aggregate fn.

  • July 24, 2008 at 1:28 am

    #847566

    Thanks alot it worked 🙂

    pff I am use to only setting the group by for the collum i need, thats the way it works in mySQL

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply