CODEWITHSUNDEEP
phpmysql
fcreav
fcreav

Reputation: 360

mysql query not returning correct percentage

I have a table where I am trying to get select results, with a date clause. Group by column, count the total of another column and then work out the percentage of that count associated with the grouped column.

ID | Delaytype | Delayhours |Delaydate

1 | type1 | 2 | 2015-01-10

2 | type2 | 3 | 2015-01-10

3 |type2 | 1 | 2015-02-10

4 | type2 | 1 | 2015-01-10

So a Query on the month of Jan should give me : type 1 = 33.3% type 2 = 66.6%

My query so far is : 

$result = mysqli_query($con, "SELECT Delaytype, SUM(`Delayhours`) / (SELECT Sum(Delayhours)) 
        * 100 as cnt FROM delays  WHERE YEAR(Delaydate) = '".$yearly."' AND MONTH(Delaydate) = '".$month."' GROUP BY Delaytype; ");

However I am getting a 100% result for each Delaytype

thankyou in advance for any help.

Cant see where I can make a table so I apologize for the formatting.

Upvotes: 0

Views: 73

Answers (1)

userlond
userlond

Reputation: 3818

SELECT 
    DelayType,
    SUM(Delayhours) AS Delaytotal,
    ROUND(
        100*(
          SUM(Delayhours) / 
          (
              SELECT SUM(Delayhours) 
              FROM dummy 
              WHERE 
                  Delaydate >= '2015-01-01' AND
                  Delaydate < '2015-02-01'
          )
        ), 
        2
    ) AS Delaypercents
FROM dummy
WHERE 
    Delaydate >= '2015-01-01' AND 
    Delaydate < '2015-02-01'
GROUP BY Delaytype

And catch the fiddle :)

P.S. Thanks to Used_By_Already for new details.

Upvotes: 1

Related Questions