MySQL counting problem

Forum Moderators: open

Message Too Old, No Replies

MySQL counting problem

Select from one db table and count from a other db table.

Libre

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

If someone can help me with this problem...

Libre

1:58 am on Oct 28, 2010 (gmt 0)

With this query...

SELECT `profile_rate`.`Profile` , COUNT( * ) AS Count
FROM `profile_rate`
WHERE `profile_rate`.`Member` = 'Ana'
GROUP BY `profile_rate`.`Profile`

I get this result...
Profile | Count
----------------
Jim | 1
Bob | 1
Joe | 1

But I dont know how to join the other usernames to the Profile field

enigma1

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.

Libre

4:29 pm on Oct 28, 2010 (gmt 0)

The problem it that I want to get the profiles where Ana did not vote for.

On this moment I get first the rows where Ana has voted for, save it in a hash, for to check it later with a other query for a row where Ana did not voted for.

enigma1

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.