Page 1 of 1

mySQL average?

Posted: Mon Sep 01, 2008 7:38 pm
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 
 

Re: mySQL average?

Posted: Mon Sep 01, 2008 8:22 pm
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.

Re: mySQL average?

Posted: Mon Sep 01, 2008 8:41 pm
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 ;]