?
Solved

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

Posted on 2004-08-18
7
Medium Priority
?
922 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

800 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