Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

937 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now