Posted by John Bell on 08/28/05 13:10

Hi

The is a group aimed at SQL Server and therefore you may want to post in a
more appropriate forum.

When posting table definitions (DDL) and example data (in a usable form) are
always less ambiguous than a drawn out description see
http://www.aspfaq.com/etiquette.asp?id=5006 although the means of obtaining
these may be different on MySQL. You current attempt(s) and expected output
will also be a good indication of what you are trying to achieve.

I don't know enough about MySQL to post accurate examples , but assuming
your date is in a 8 character column (say formatted 'CCYYMMDD') and each
user is identified by an ID column, then the following may work (although it
may not be the fastest of solutions!):

SELECT a.id, a.date,
(SELECT COUNT(*) FROM MyData b where b.Date = a.Date and Rating = 'Bad' ) AS
TodaysBad,
(SELECT COUNT(*) FROM MyData b where b.Date = a.Date ) AS TodaysTotal,
(SELECT COUNT(*) FROM MyData b where b.Date <= a.Date and Rating = 'Bad' )
AS CumulativeBad,
(SELECT COUNT(*) FROM MyData b where b.Date <= a.Date ) AS CumulativeTotal
FROM MyDate A
WHERE A.Date = '20050827'

John


<fwells11@hotmail.com> wrote in message
news:1125084576.325630.252130@g44g2000cwa.googlegroups.com...
> Hi there. As you will see from my questions, I am an SQL newb. I
> dabble but never get to spend enough time to get proficient so base any
> feeedback on that basis please. This is all theoretical information at
> this point so I am also going to post this in a MySQL related group. I
> will create some designs and post back to the group if I get any
> feedback I can use.
>
> Problem:
>
> I would like to be able to keep a running percentage total in a field
> associated with my users. In order to calculate the totals, I will
> parsing a text file with entries from my users in it. The parser (AWK
> etc) will search the file for specific text, compare it to information
> in another file and output some entries into a csv file which can
> subsequently be imported into the database.
>
> The users make posts that are considered good and bad and the rating
> percentage must be based on that. For example, if a user makes 10
> posts in a day, and 4 of them are considered 'bad' by my criterion, the
> rating should reflect a score of 60% for that day.
>
> However, the rating is an ongoing value that will be adjusted daily and
> I must maintain a running total against all previous posts. So, lets
> say on day two the same user posts 10 more times and 3 are 'bad', I
> must adjust his score to reflect a total percentage rating which would
> then be 20 posts with 7 being bad for an overall rating of 65% etc.
>
> My question is, how should I go about recording and calculating all
> this information?
>
> Here are my thoughts. I have a users table with a field called
> something like 'Rating' which stores the overall value (65% etc). This
> value would have to be calculated from fields in another table like
> 'Posts' which records each post in 'Good' and "Bad' fields that
> increment. The Good and Bad fields would be incremented (populated)
> from the text that gets imported etc.
>
> Looking for thoughts from experienced db designers please. Thanks a
> lot in advance for any responses.
>

[Back to original message]