Solved

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

Posted on 2004-08-18
7
910 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HTML <font style="color:red"> 9 31
SQL Group By Question 4 19
Where is the Help Section? 8 26
Substring works but need to tweak it 14 14
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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