Group By Problem

[iceraider]

SELECT   b.bubble_id,
         b.bubble_name,
         b.rate,
         COUNT(DISTINCT bid) as cn,
         DATE_FORMAT(date, '%m-%d-%y') as date 
FROM     bubbles b, rate r 
WHERE    b.id=r.bid 
GROUP BY r.date 
ORDER BY r.date DESC LIMIT 15

Ok thats really invalid, but I need to do this.

This is a rate script, every time someone rates it records what they rated, the day they rated, who they rated.

So I need to select who got rated the best from the past 15 times.

So once I get the results I can print

By highest votes for each day

2003-10-22 | Votes: 10 | Program Name
2003-10-21 | Votes: 12 | Program Name
2003-10-20 | Votes: 03 | Program Name
2003-10-19 | Votes: 24 | Program Name
2003-10-18 | Votes: 07 | Program Name
2003-10-17 | Votes: 13 | Program Name
2003-10-16 | Votes: 44 | Program Name

[LordShryku]

Line breaks....you need line breaks....depereately.

SELECT   b.bubble_id,
         b.bubble_name,
         b.rate,
         COUNT(DISTINCT bid) as cn,
         DATE_FORMAT(date, '%m-%d-%y') as date 
FROM     bubbles b, rate r 
WHERE    b.id=r.bid 
GROUP BY r.date 
ORDER BY r.date DESC LIMIT 15

Now...why is this invalid?

[iceraider]

Ok that returns my dates but the wrong programs and count is counting every vote for that day, and not every vote that program 🙁

[iceraider]

Or I think it's returning that count of bubbles.

[LordShryku]

Much better 🙂

So, I see what your saying. Have you tried adding a secondary GROUP BY, like

GROUP BY r.date, program_name

?

[iceraider]

That just returned every row.

Well let's forget about the the other sql and just try to get it from votes.

SELECT   *, COUNT(*)
FROM     rate
GROUP BY date
ORDER BY date DESC

bid   mid    rate         IP           date     COUNT( * )  

67     1      5      68.61.165.209   2003-10-23       38 
22     2      4     212.181.151.251  2003-10-22       13 

That is way wrong!

Because count(*) needs to return the number of people who rated for the bubble. And every row is a vote.

It's returning just every vote to that date not to the bubble.

[LordShryku]

Ok, the "bubble" just confused me. But anyway....

So by people, you mean distinct IP addy's, right?
Dunno if all the other info is important, so how bout

SELECT   DISTINCT IP, date, count(*)
FROM     rate
GROUP BY IP, date
ORDER BY date DESC

[iceraider]

No, No, No, No 😛

Remember This?

2003-10-22 | Votes: 10 | Program Name
2003-10-21 | Votes: 12 | Program Name
2003-10-20 | Votes: 03 | Program Name
2003-10-19 | Votes: 24 | Program Name
2003-10-18 | Votes: 07 | Program Name
2003-10-17 | Votes: 13 | Program Name
2003-10-16 | Votes: 44 | Program Name

Look Here.

2003-10-22 | Votes: 10(num of people who voted) | Program Name

it's already distinct for member by mid, IP is not needed just for verification.

Let me go over this again,

Lets say I need to results for 2 days.

I want to see what program(bid) got the most votes on those two dayes.

so LIMIT 2 (I get the two dates ex: 2003-10-22, ex: 2003-10-23)

Starting to get it more?

[iceraider]

Guess it's not possible