[SOLVED] AVG one column, only if DISTINCT in another.

[noverflow]

I have a site where people vote on art.

Im currently using this.

SELECT AVG(vote_score) As Rating FROM vote WHERE vote_print= 212

 

But there are a bunch of people signing up over and over again to push their score higher.

I keep track of all of the ip address the votes came from.  So I was thinking of using DISTINCT, but Im not sure how that would be structured.

 

Im looking for the proper way of this

SELECT AVG(vote_score) As Rating FROM vote WHERE vote_print= 212

But only if DISTINCT (vote_ip)

 

Thanks

[The Little Guy]

Something like this:

 

SELECT DISTINCT(vote_ip), AVG(vote_score) As Rating FROM vote WHERE vote_print= 212

[noverflow]

That was my first thought and try, but I get this back

 

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

 

Something like this:

 

SELECT DISTINCT(vote_ip), AVG(vote_score) As Rating FROM vote WHERE vote_print= 212

[The Little Guy]

SELECT DISTINCT(vote_ip), AVG(vote_score) As Rating FROM vote WHERE vote_print= 212 GROUP BY vote_ip

[noverflow]

That was my second try. 

It works in a way, but it lists all the IP address as groups, and has the averages per address.

 

 

 

 

SELECT DISTINCT(vote_ip), AVG(vote_score) As Rating FROM vote WHERE vote_print= 212 GROUP BY vote_ip

[The Little Guy]

How about this:

 

SELECT DISTINCT(vote_ip), AVG(vote_score) As Rating FROM vote WHERE vote_print= 212 GROUP BY vote_print

[noverflow]

Man... We need to hang out.

That was my #3 try (but that is where I stopped).

 

For what ever reason, it does not only select the distinct IPs. It gives me the same rating as

 

SELECT AVG(vote_score) As Rating FROM vote WHERE vote_print= 212

 

Even though one IP has rated this one 33 times.

 

How about this:

 

SELECT DISTINCT(vote_ip), AVG(vote_score) As Rating FROM vote WHERE vote_print= 212 GROUP BY vote_print

[The Little Guy]

Maybe removing the parenthesis around the distinct value will give you something you want...

 

SELECT DISTINCT vote_ip, AVG(vote_score) As Rating FROM vote WHERE vote_print= 212 GROUP BY vote_print

[noverflow]

No... same thing.

And if I change AVG to COUNT just to make sure, it brings up 117. It should only be 53.

 

 

Maybe removing the parenthesis around the distinct value will give you something you want...

 

SELECT DISTINCT vote_ip, AVG(vote_score) As Rating FROM vote WHERE vote_print= 212 GROUP BY vote_print

[fenway]

Stop using DISTINCT and GROUP BY...

[Barand]

try

SELECT AVG(x.vote_score) as rating FROM
(
   SELECT vote_ip, vote_score 
   FROM vote 
   WHERE vote_print= 212
   GROUP BY vote_ip
) as x

 

(untested)

 

[noverflow]

Nice!!!!

That worked.

 

Any chance doing the same but selecting all prints ?

So I can list them by rating.

 

 

Right now It works with this, but does not neglect the repeat customers. 

 

SELECT print_id, AVG(vote_score) As rating FROM vote, print WHERE vote_print=print_id GROUP BY vote_print

 

try

SELECT AVG(x.vote_score) as rating FROM
(
   SELECT vote_ip, vote_score 
   FROM vote 
   WHERE vote_print= 212
   GROUP BY vote_ip
) as x

 

(untested)

[noverflow]

This didnt work... but I think im on the right track.

 

SELECT x.print_id, AVG(x.vote_score) as Rating FROM 
(
SELECT print_id, vote_ip, vote_score 
FROM vote, print 
WHERE vote_print= print_id GROUP BY vote_ip
) as x
GROUP BY print_id

 

[Barand]

if this doesn't work I'll have to resort to setting up a test table, but perhaps

 

SELECT x. vote_print, AVG(x.vote_score) as Rating FROM

(

  SELECT vote_print, vote_ip, vote_score

  FROM vote

  GROUP BY vote_ip

) as x

GROUP BY x. vote_print

[noverflow]

I looks like it works, but all the scores are even numbers, and limited to 173 entries (should be like 280)

 

I think this might not actually be possible.

There is no way to group by anything without losing information.

 

If I group by IP, then all the voyes the user has done on other prints is lost.

By print, I lose all the votes but one.

 

 

Do I call this solved?

 

if this doesn't work I'll have to resort to setting up a test table, but perhaps

 

SELECT x. vote_print, AVG(x.vote_score) as Rating FROM

(

  SELECT vote_print, vote_ip, vote_score

  FROM vote

  GROUP BY vote_ip

) as x

GROUP BY x. vote_print

[fenway]

There is no way to group by anything without losing information.

If you GROUP BY, and you want non-aggregate columns, then you'll need to join that table back to itself.