GROUP BY is not calculated correctly

I have this statement:

SELECT COUNT(ID) AS numberVote, AWARD_ID, NOMINEE_ID, VOTER_ID, MULTI_CODE 
FROM b_awards_vote 
WHERE AWARD_ID = 8 
GROUP BY MULTI_CODE

      

My table looks like this:

ID   |  AWARD_ID  | NOMINEE_ID  | VOTER_ID  | MUTLI_CODE
1    |  8         | 3           | 1         | 4837
2    |  8         | 4           | 1         | 4837
3    |  8         | 5           | 1         | 4837

      

However, returning to php:

$numberVote = $row['numberVote'];

      

This statement returns 3. Here are some details on what I want:

  • All entries with the same MULTI_CODE are actually 1 voice.
  • So this example in the table above, I want to count 1 vote. In the following table:

    ID   |  AWARD_ID  | NOMINEE_ID  | VOTER_ID  | MUTLI_CODE
    1    |  8         | 3           | 1         | 4837
    2    |  8         | 4           | 1         | 4837
    3    |  8         | 5           | 1         | 4837
    4    |  8         | 4           | 3         | 7480
    5    |  8         | 5           | 3         | 7480
    
          

  • I would like this count to return: 2 as there are two different MULTI_CODEs as there are two different voice groups.

+3


source to share


1 answer


This query will count the number of groups MUTLI_CODE

you have:

  SELECT  COUNT( distinct MULTI_CODE) AS NumOfGroups ,ID AS numberVote, AWARD_ID, NOMINEE_ID, VOTER_ID, MULTI_CODE 
  FROM b_awards_vote 
  WHERE AWARD_ID = 8 

      



see SQLFiddle for reference

+3


source







All Articles