CODEWITHSUNDEEP
phpmysql
raj malhotra
raj malhotra

Reputation: 23

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

I have a table with 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 to group partyname ,constituency with 'count', and a row 'SUM'

but it count only max value from each constituency and then display count and row sum.

below is my expected output for above table

partyname       Count
    BJP          2

    AAP          1

bcoz bjp leads from nagpur -east and nagpur west .so it display count 2 and aap leads from nagpur - north then it display only 1 count.

plz suggest me query......

 <?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 } ?>

Upvotes: 0

Views: 84

Answers (3)

SonalPM
SonalPM

Reputation: 1337

Here is the Solution for Your Problem.. Hope this will work..

select t1.partyname as PARTY, count(*) as WinCount
from voter_count t1, voter_count t2 where t1.votes > t2.votes
and t1.constituency=t2.constituency
group by t1.partyname

Upvotes: 1

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

Assuming you don’t care about same votes count for different parties (handling the same votes count for different parties in the same district would make the query far more complicated, because you will get unpredictable value for MAX column in the inner query):

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;

First of all, the inner query selects maximal values for votes. The result of inner query execution would be:

Nagpur-East,1200
Nagpur-North,1500
Nagpur-West,4800

After that, join maximals and count districts.

AAP,1
BJP,2

Upvotes: 1

Ashish Jagtap
Ashish Jagtap

Reputation: 2819

After reading your question its difficult to understand what exactly you want as output result 

SELECT partyname AS 'Party Name',COUNT(candidatename) AS 'can_count',SUM(votes) AS 'sum'
FROM voter_count 
WHERE votes >= 1500
GROUP BY constituency
ORDER BY can_count DESC;

also check this SQL Fiddle Link

hope this query will help you to get desired output..

Upvotes: 0

Related Questions