Msg#: 4223010 posted 9:52 pm on Oct 27, 2010 (gmt 0)
I am programing a script using a MySQL database, I am not very good in MySQL and only know some basic stuff, but I am already looking two days for a result for this problem that is maybe simple for many people.
Database of all members who has a profile. DB table 'members' ID | Username ------------- 1 | Ana 2 | Jim 3 | Bob 4 | Joe 5 | Ela
All members has a profile and they can vote for them. 'Profile' is the profile where they have been voted for. 'Member' is the user who have voted. Database of all votes. DB table 'profile_rate' ID | Profile | Member | Rate ---------------------------- 1 | Ana | Jim | Yes 2 | Ana | Bob | No 3 | Jim | Ana | Yes 4 | Bob | Ela | Yes 5 | Joe | Ana | No 6 | Ela | Joe | yes 7 | Bob | Ana | Yes 8 | Ana | Joe | No
Result wanted I want to know for which profile Ana have been voted already. In the case of Ana the result must be: Username | Count ---------------- Ana | 0 Jim | 1 Bob | 1 Joe | 1 Ela | 0
Msg#: 4223010 posted 10:23 am on Oct 28, 2010 (gmt 0)
If you want to get the profiles who were voted for Yes then:
select profile, count(rate) as total from profile_rate where rate='Yes' group by profile
If you wanted to get the profiles who were just voted, remove the where clause, and if you want to check for a specific name add in the where clause the name of the member.
Would be easier if you had a tinyint instead of the Yes/No rate because you could use the mysql sum to add up all the votes. Right now to get the No's you will have to use the same table as an alias and count them.
Msg#: 4223010 posted 8:17 pm on Oct 28, 2010 (gmt 0)
I don't see the point however. The members who aren't voting for Ana can be retrieved as a total if you want to calculate percentages.
select count(member) as total from profile_rate
And then you have the total number of members and from the other query you have the members who voted for Ana. It's faster in to perform the 2 different queries than joining tables and getting the total from all the rows.