mySQL average?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

mySQL average?

Post by psychotomus »

everytime someone votes, it sets up the votes and vote_overall based upon the day... but everytime i run this query i get no results and theres records of vote_overall's in my database? WHY?

Code: Select all

 
SELECT name, username, theme_id, screen_url, SUM( `downloads` ) AS total_downloads, SUM( `votes` ) AS total_votes, AVG( 'vote_overall' ) AS vote_score, SUM( `demos` ) AS total_demos, SUM( `views` ) AS total_views
FROM theme_stats_by_day
WHERE 1 
AND `day` 
BETWEEN $start_date
AND $end_date 
GROUP BY `theme_id` 
ORDER BY vote_score DESC 
LIMIT 0 , 12 
 
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mySQL average?

Post by califdon »

Your query is a Select query, it only selects data from the database, it doesn't update the database. And you should NOT update the database with calculated values. Only raw data should be stored in a database, except under a few quite specific conditions. Your query is doing the following:

Code: Select all

SELECT name, username, theme_id, screen_url,  [color=#008000]<--- selecting 4 data fields from the table[/color]
   SUM( `downloads` ) AS total_downloads,  [color=#008000]<-- calculating the sum of a field and reporting it as total_downloads[/color]
   SUM( `votes` ) AS total_votes,  [color=#008000]<-- calculating the sum of another field and reporting it as total_votes[/color]
   AVG( 'vote_overall' ) AS vote_score,  [color=#008000]<-- calculating the average of another field and reporting it as vote_score[/color]
   SUM( `demos` ) AS total_demos,  [color=#008000]<-- calculating the sum of another field and reporting it as total_demos[/color]
   SUM( `views` ) AS total_views  [color=#008000]<-- calculating the sum of another field and reporting it as total_views[/color]
Are you saying that you have 5 fields in your table with names like total_downloads and the others? If so, your table design is probably incorrect, but in any case a Select query cannot ever update data.

If there is data in the fields downloads, votes, vote_overall, demos, and views, I would expect that you should be getting results from your query.

You also used single quotes around vote_overall, which makes it a string, which the query reports out, but that is not coming from your database, it's because you used single quotes instead of backticks.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Re: mySQL average?

Post by psychotomus »

califdon wrote: You also used single quotes around vote_overall, which makes it a string, which the query reports out, but that is not coming from your database, it's because you used single quotes instead of backticks.

it worked ;]
Post Reply