php - SUM of grouped COUNT which only calaculate max value and display count of max -

php - SUM of grouped COUNT which only calaculate max value and display count of max -

i have table 6 fields:

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

i want grouping partyname ,constituency 'count', , row 'sum'

but count max value each constituency , display count , row sum.

below expected output above table

partyname count bjp 2 aap 1

bcoz bjp leads nagpur -east , nagpur west .so display count 2 , aap leads nagpur - north display 1 count.

plz suggest me query......

<?php $state = $rowdst['state']; $sqlst = "select max(votes) vote,constituency,state, district voter_count state = '$state' grouping constituency, state"; $resultst = mysql_query($sqlst); while($rowst = mysql_fetch_array($resultst,mysql_assoc)) { ?> <?php $vote = $rowst['vote']; $sqlct = "select count($vote) counts,partyname,constituency, district voter_count votes = '$vote' , 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 } ?>

assuming don’t care same votes count different parties (handling same votes count different parties in same district create query far more complicated, because unpredictable value max column in inner query):

select res.partyname, count(res.constituency) votescount voter_count res bring together ( select constituency, max(votes) votes voter_count grouping constituency ) maxs using(votes, constituency) grouping partyname;

first of all, inner query selects maximal values votes. result of inner query execution be:

nagpur-east,1200 nagpur-north,1500 nagpur-west,4800

after that, bring together maximals , count districts.

aap,1 bjp,2

php mysql