michaellunsford Posted April 28, 2009 Share Posted April 28, 2009 Here's the situation. This database has about 150 different categories. This query is supposed to lump all the votes per category together and return the count. However, I'm thinking if someone voted for the same person in two categories, the vote might get lumped into the wrong category. What do you think? SELECT *,COUNT(*) AS `mycount` FROM `table` GROUP BY `vote` HAVING `mycount` > 1 ORDER BY `category`,COUNT( * ) DESC Link to comment https://forums.phpfreaks.com/topic/155921-solved-does-this-query-do-what-i-think-it-does/ Share on other sites More sharing options...
kickstart Posted April 28, 2009 Share Posted April 28, 2009 Hi Not sure on the structure of you table (ie, is vote a count, or just a marker for a vote with one row per vote), but it would appear that you want to group on the category as well. All the best Keith Link to comment https://forums.phpfreaks.com/topic/155921-solved-does-this-query-do-what-i-think-it-does/#findComment-820985 Share on other sites More sharing options...
fenway Posted April 28, 2009 Share Posted April 28, 2009 1) you can't use * with group by 2) the value of "category" will be meaningless 3) you're not using your column alias in your order by Link to comment https://forums.phpfreaks.com/topic/155921-solved-does-this-query-do-what-i-think-it-does/#findComment-821237 Share on other sites More sharing options...
michaellunsford Posted May 3, 2009 Author Share Posted May 3, 2009 1) you can't use * with group by 2) the value of "category" will be meaningless 3) you're not using your column alias in your order by The goal of the query is to report how may votes each item received in each category. It seems to be working since I added kickstart's suggestion. Here's the new query: SELECT `category`,`vote`,COUNT(`vote`) AS `counti` FROM `table` GROUP BY `category`,`vote` HAVING `counti` > 3 ORDER BY `category`,`counti` DESC 1) SELECT * seems to be returning the results I expect. However, I modified the field query to have no *. 2) ORDER BY `category`keeps the `category` field together, making it easier to determine a winner without having to resort later. 3) yeah, I don't know why I missed this one. It has been remedied. Link to comment https://forums.phpfreaks.com/topic/155921-solved-does-this-query-do-what-i-think-it-does/#findComment-825167 Share on other sites More sharing options...
kickstart Posted May 4, 2009 Share Posted May 4, 2009 1) SELECT * seems to be returning the results I expect. However, I modified the field query to have no *. MySQL is pretty tolerant of this, unlike most other flavours of SQL. However consider a table of first name and surname, and you want a count of each surname:- FirstName, Surname John, Smith Andrew, Smith Fred, Blogs If you tried something like SELECT *, COUNT(*) FROM Names GROUP BY Surname, you would get the counts, but with the count of Smith what surname would be brought back? All the best Keith Link to comment https://forums.phpfreaks.com/topic/155921-solved-does-this-query-do-what-i-think-it-does/#findComment-825547 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.