Professional Web Applications Themes

2 table query problem - Coldfusion Database Access

Im having trouble with this query. I have 2 tables. I only want to display the events table but I want to sort the results based on the users average rating on the article(events). SELECT events.evSubject, events.evID, AVG(ev_rating.rating) AS "rating" FROM events, ev_rating WHERE events.evType = 12 AND events.evPrivate = 0 AND events.evStat = 3 AND ev_rating.rtID = events.evID ORDER BY rating I get error: General error: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause Im not a huge sql guru but I know most of you are. I wasnt ...

  1. #1

    Default 2 table query problem

    Im having trouble with this query. I have 2 tables. I only want to display
    the events table but I want to sort the results based on the users average
    rating on the article(events).

    SELECT events.evSubject, events.evID, AVG(ev_rating.rating) AS "rating" FROM
    events, ev_rating WHERE events.evType = 12 AND events.evPrivate = 0 AND
    events.evStat = 3 AND ev_rating.rtID = events.evID ORDER BY rating

    I get error: General error: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
    with no GROUP columns is illegal if there is no GROUP BY clause

    Im not a huge sql guru but I know most of you are. I wasnt sure how else to
    do this or sort the list like I want.

    glossen Guest

  2. #2

    Default Re: 2 table query problem

    Like the error message said, since you are performing an aggregate function
    without a GROUP BY.

    SELECT events.evSubject, events.evID, AVG(ev_rating.rating) AS "rating"
    FROM events, ev_rating
    WHERE events.evType = 12
    AND events.evPrivate = 0
    AND events.evStat = 3
    AND ev_rating.rtID = events.evID
    GROUP BY events.evSubject, events.evID
    ORDER BY rating

    Phil

    paross1 Guest

  3. #3

    Default Re: 2 table query problem

    Thanks Phil. Im having an issue with that query. It wasnt sorting by the
    average rating for that record. That table(events) only has 5 records right
    now. But each event has many ratings (hence the AVR). I put that query in and
    the results were as follows:

    evID Subject rating
    1 event1 3.758
    2 event2 3.758
    3 event3 3.758

    and so on.... I know what they should be but I se that it just took an average
    once, do ya kinda see what im saying?? Is there any way to retrieve the unique
    average for each event?

    Thank You

    glossen Guest

Similar Threads

  1. Alter table query problem
    By Jenny Rice in forum MySQL
    Replies: 3
    Last Post: June 8th, 08:16 AM
  2. Query w/bad table structure
    By rmorgan in forum Coldfusion Database Access
    Replies: 9
    Last Post: March 2nd, 06:35 PM
  3. Replies: 4
    Last Post: February 10th, 07:42 PM
  4. Mulitple Table Query Help
    By Ralph Freshour in forum PHP Development
    Replies: 1
    Last Post: September 3rd, 03:49 AM
  5. Temporary Table Query
    By Jonathan Derbyshire in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 4th, 07:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139