homepage Welcome to WebmasterWorld Guest from 54.205.99.71
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL counting problem
Select from one db table and count from a other db table.
Libre




msg:4223012
 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




msg:4223108
 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




msg:4223294
 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




msg:4223446
 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




msg:4223569
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved