我有一张有6个字段的表:

    partyname   state      constituency     candidatename   district    votes
    BJP       Maharashtra   Nagpur-East     Nitin Gadkari   Nagpur       1200
    AAP       Maharashtra   Nagpur-East     Arvind Kejriwal Nagpur       750
    BJP       Maharashtra   Nagpur-West     Vicky           Nagpur       4800
    AAP       Maharashtra   Nagpur-West     Lucky           Nagpur       3500
    BJP       Maharashtra   Nagpur-North    Rakesh          Nagpur       100
    AAP       Maharashtra   Nagpur-North    Ravan          Nagpur       1500

我想用“count”和一行“SUM”将partyname、选区分组
但它只计算每个选区的最大值,然后显示计数和行和。
下表是我对上表的预期输出
partyname       Count
    BJP          2

    AAP          1

bcoz bjp从那格浦尔以东和那格浦尔以西引出,所以显示计数2
从那格浦尔北部的aap显示只有1个计数。
请建议我查询。。。。。。
 <?php
 $state =  $rowdst['state'];
 $sqlst = "SELECT  MAX(votes) AS Vote,constituency,state, district FROM voter_count where state = '$state' group by  constituency, state";
 $resultst = mysql_query($sqlst);
 while($rowst = mysql_fetch_array($resultst,MYSQL_ASSOC))
    {
    ?>
    <?php
             $vote = $rowst['Vote'];
             $sqlct = "SELECT COUNT($vote) AS counts,partyname,constituency, district FROM voter_count where votes = '$vote' AND state = '$state'";
             $resultct = mysql_query($sqlct);
             $rowct = mysql_fetch_array($resultct,MYSQL_ASSOC)
        ?>
             <tr>
              <td><?php echo $rowct['partyname']; ?></td>
              <td><?php echo $rowct['counts']; ?></td>
        </tr>
<?php } ?>

最佳答案

假设您不关心不同政党的相同票数(处理同一地区不同政党的相同票数将使查询复杂得多,因为您将在内部查询中为MAX列获得不可预测的值):

SELECT RES.PARTYNAME, COUNT(RES.CONSTITUENCY) AS VOTESCOUNT
FROM voter_count RES
JOIN (
    SELECT CONSTITUENCY, MAX(VOTES) AS VOTES
    FROM voter_count
    GROUP BY CONSTITUENCY
) MAXS USING(VOTES, CONSTITUENCY)
GROUP BY PARTYNAME;

首先,内部查询为votes选择最大值。内部查询执行的结果是:
Nagpur-East,1200
Nagpur-North,1500
Nagpur-West,4800

之后,加入最大值和计数区。
AAP,1
BJP,2

08-25 11:34
查看更多