CODEWITHSUNDEEP
mysql
Nibur
Nibur

Reputation: 49

How to calculate the percentage of different values in same column and group by month of current year?

I want to take the percentage of only SLA's in the sla_osla table and group them by all the months of current year

Here is my table

http://sqlfiddle.com/#!9/179518/2

I have tried the following query

    SELECT MONTH(service_date),((nums/(count(*)))*100) AS perc 
      FROM orders CROSS JOIN (SELECT MONTH(service_date) AS months, COUNT(*) AS 
   nums FROM orders WHERE YEAR(service_date) = year(CURRENT_DATE()) AND sla_osla = 
  'sla' 
     GROUP BY MONTH(CURRENT_DATE())) table1
     WHERE YEAR(service_date) = YEAR(current_date) AND sla_osla IN ('sla','osla')
     GROUP BY MONTH(service_date)

I want output like this

month percentage

    1       75%
    2       50%

Upvotes: 1

Views: 139

Answers (3)

StackSlave
StackSlave

Reputation: 10627

http://sqlfiddle.com/#!9/179518/129

SELECT MONTH(service_date) month, CONCAT(FORMAT(SUM(sla_osla='sla')/COUNT(*)*100,0),'%') percentage FROM orders WHERE YEAR(CURDATE())=YEAR(service_date) GROUP BY MONTH(service_date);

Upvotes: 0

exussum
exussum

Reputation: 18560

http://sqlfiddle.com/#!9/179518/76

This is more simple sql

Select year(service_date), month(service_date), sum(sla_osla = "sla")/ count(*) * 100 FROM orders group by year(service_date), month(service_date)

I've also grouped by year here.

Edit if you want the percent sign etc then use this

http://sqlfiddle.com/#!9/179518/82

Upvotes: 3

Shoaeb
Shoaeb

Reputation: 919

SELECT month(service_date) as dmonth,count(1) 
  FROM orders where sla_osla='sla'
 group by dmonth

Upvotes: 0

Related Questions