I can't seem to create the correct query. I have 2 tables and I need to return results from both. Here is the basic structure:
challengeid catid votes score
categoryid cat scale entrants
These are the relevant fields. I am trying to return the highest score, highest votes, scale, entrants and cat for each categoryid. categoryid and catid are associated with each other. Basically I thought this query would work:
SELECT cat, scale, entrants, MAX(score) AS highest, SUM(score) AS total, AVG(score) AS average
FROM vbc_categories, vbc_challenges
WHERE categoryid = catid
GROUP BY categoryid
It doesnt work like I thought it would. Am I missing something?
, max(chgs.score) as highest
, sum(chgs.score) as total
, avg(chgs.score) as average
from vbc_categories as cats
join vbc_challenges as chgs
Anyone else able to help me. A new problem. I have 2 other columns in the vbc_challenges table. Lets call them field1 and field2 that's basically how they're called. I need to count the number of unique entries in those columns. So if field1 has something in it and the same entry is in field2 then only count it once. Is there an easy way to do this?