Solved

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

Posted on 2004-08-18
7
912 Views
Last Modified: 2008-02-01
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
Comment
Question by:testersonian
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11835996
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11836003
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
 
LVL 2

Expert Comment

by:nexusSam
ID: 11836446
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
 

Author Comment

by:testersonian
ID: 11845472
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
 
LVL 2

Accepted Solution

by:
nexusSam earned 50 total points
ID: 11850503
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question