• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 930
  • Last Modified:

aggregate function or group by clause error? I'm a sql2000 newbie!

Here's my query:
SELECT
       c.name
      ,c.parent_id
      ,c.description
      ,c.type_id
      ,ct.content_id
      ,ct.time_spent
      ,max(ct.date) as last_view
      ,count(ct.content_id) as count
      
FROM
       content_tracker ct
      ,content c
WHERE
      ct.user_id = '15'
AND
      c.id = ct.content_id
GROUP BY
      ct.content_id
ORDER BY
      last_view desc

Now, I've used mySQL for a while and it works fine there... I'm porting my database class over so my apps will work with sql2000. Can someone point me in the right direction? Thanks in advance!

I'm getting this error from the query analyzer:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.parent_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.type_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'ct.time_spent' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
testersonian
Asked:
testersonian
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
You can either do it like this:

SELECT
     ct.content_id
     ,ct.time_spent
     ,max(ct.date) as last_view
     ,count(ct.content_id) as count
     
FROM
      content_tracker ct
     ,content c
WHERE
     ct.user_id = '15'
AND
     c.id = ct.content_id
GROUP BY
     ct.content_id
ORDER BY
     last_view desc

Or like this:
SELECT
      MAX(c.name)
     ,MAX(c.parent_id)
     ,MAX(c.description)
     ,MAX(c.type_id)
     ,ct.content_id
     ,ct.time_spent
     ,max(ct.date) as last_view
     ,count(ct.content_id) as count
     
FROM
      content_tracker ct
     ,content c
WHERE
     ct.user_id = '15'
AND
     c.id = ct.content_id
GROUP BY
     ct.content_id
ORDER BY
     last_view desc

Or ... Well I think you get the idea.  You really need to tell us what you need as a result.
0
 
Anthony PerkinsCommented:
It looks like I missed a column:

SELECT
     ct.content_id
     ,max(ct.date) as last_view
     ,count(ct.content_id) as count
     
FROM
      content_tracker ct
     ,content c
WHERE
     ct.user_id = '15'
AND
     c.id = ct.content_id
GROUP BY
     ct.content_id
ORDER BY
     last_view desc

Or:
SELECT
      MAX(c.name)
     ,MAX(c.parent_id)
     ,MAX(c.description)
     ,MAX(c.type_id)
     ,ct.content_id
     ,MAX(ct.time_spent)
     ,max(ct.date) as last_view
     ,count(ct.content_id) as count
     
FROM
      content_tracker ct
     ,content c
WHERE
     ct.user_id = '15'
AND
     c.id = ct.content_id
GROUP BY
     ct.content_id
ORDER BY
     last_view desc
0
 
nexusSamCommented:
SQL server is complaining because you must have the same columns in the GROUP BY as in the SELECT (unless they are part of an agg function such as AVG, MAX, MIN and SUM. If what you are trying to do is list all content 15 rows with the content_id column supressed for repeating values then using MAX on name, parent, etc won't work, you need to include them in the GROUP BY and use extr alogic to Supress Repeating
Let us know!
0
 
testersonianAuthor Commented:
Thanks for the quick responses...

I need to return everything in my SELECT clause, c.name, c.parent_id,c.description,c.type_id,ct.content_id,ct.time_spent,max(ct.date) as last_view,count(ct.content_id) as count. Here's a (php) print_r() result from my successful mySQL query:
Array
(
    [0] => stdClass Object
        (
            [name] => #1470 - Painting and Depainting
            [parent_id] => 39
            [description] =>
            [type_id] => 2
            [content_id] => 15
            [time_spent] => 00:00:50
            [last_view] => 2004-08-19 10:55:29
            [count] => 6
        )

    [1] => stdClass Object
        (
            [name] => #1486 Air Quality Compliance (Basic)
            [parent_id] => 0
            [description] => General air quality information for all audiences
            [type_id] => 1
            [content_id] => 34
            [time_spent] => 00:00:01
            [last_view] => 2004-08-17 13:27:57
            [count] => 11
        )
}
There's code in there that checks the type_id and only displays it if it's not type_id == 4 or type_id == 5 (I'd like to build that into the query as well, but I need this to work first...)  I could break it up into seperate queries, but I'd rather not.

I'm only displaying one content title/description, even if the user has seen the same content 50 times, and I need to display the last date they viewed it, as well as the time spent for the latest view. I hope that helps.
0
 
nexusSamCommented:
Try this

SELECT
      c.name
     ,c.parent_id
     ,c.description
     ,c.type_id
     ,ct.content_id
     ,ct.time_spent
     ,max(ct.date) as last_view
     ,count(ct.content_id) as count
     
FROM
      content_tracker ct
     ,content c
WHERE
     ct.user_id = '15'
AND
     c.id = ct.content_id
GROUP BY
     c.name
     ,c.parent_id
     ,c.description
     ,c.type_id
     ,ct.content_id
     ,ct.time_spent
ORDER BY
     last_view desc
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now