CODEWITHSUNDEEP
mysqlsql
T.r.
T.r.

Reputation: 65

How to remove decimal digit from result in mysql?

I want to remove digit after decimal how to solve it?

My query is:

SELECT city_name,
       Assignedto,
       COUNT(Assignedto) AS TC,
       CONCAT(count(CASE
                        WHEN STATUS = 'CLOSED' THEN 1
                        ELSE NULL
                    END) * 100 / count(1), '%') AS SC,
       CONCAT(count(CASE
                        WHEN STATUS = 'PENDING' THEN 1
                        ELSE NULL
                    END) * 100 / count(1), '%') AS PC,
       SUM(TIMESTAMPDIFF(MINUTE,Request_Date, Xetr))/60 AS WH,
       (154440-sum(TIMESTAMPDIFF(MINUTE,Request_Date, Xetr))/60) AS VH,
       CONCAT(COUNT(Feedback_Rate)/COUNT(Assignedto)*100,'%') AS Feed_Percent,
       SUM(Feedback_Rate)/(count(Feedback_Rate)*5)*5 AS AVG_Feedback
FROM `it_service_ticket`
INNER JOIN `it_problem`ON `it_service_ticket`.`it_problem_id`=`it_problem`.`it_problem_id`
INNER JOIN `city_master` ON `it_service_ticket`.cityid=`city_master`.city_id
WHERE `it_service_ticket`.`xetr` BETWEEN '2016-04-01 12:00:00 AM' AND '2017-02-28 12:00:00 PM'
GROUP BY Assignedto
ORDER BY city_name ASC;

Output

+-------------------------+-------------------------+-------+------------+----------+------------+--------------+-----------+---------+
|       City_Name         |       AssigneeTo        |  TC   |    SC      |   PC     |    WH      |     VH       | Feedback  | Average |
+-------------------------+-------------------------+-------+------------+----------+------------+--------------+-----------+---------+
| Ahmedabad               | [email protected]  |  297  | 100.0000%  | 0.0000%  |  147.0667  | 154292.9333  | 43.4343%  |  4.4031 |

| Ahmedabad | [email protected] | 297 | 100.0000% | 0.0000% | 147.0667 | 154292.9333 | 43.4343% | 4.4031 |

Upvotes: 5

Views: 23999

Answers (2)

sarbudeen
sarbudeen

Reputation: 170

You Should Use TRUNCATE() in Mysql for Example

SELECT TRUNCATE(525.668545, 3) -- 525.668

Upvotes: 5

Utsav
Utsav

Reputation: 8093

  1. If you want to round off decimal places, use ROUND(yourColumn,0) function.

    So 13.78 will become 14

  2. If you want to get rid of decimal places, user FLOOR(yourColumn)

    So 13.78 will become 13

So for example

SUM(TIMESTAMPDIFF(MINUTE,Request_Date, Xetr))/60 AS WH

should be changed to

ROUND(SUM(TIMESTAMPDIFF(MINUTE,Request_Date, Xetr))/60,0) AS WH

Edit: This would take care of your %.

CONCAT(
        ROUND(count(CASE
            WHEN STATUS = 'PENDING' THEN 1
            ELSE NULL
        END) * 100 / count(1),0)
        , '%') AS PC

Do the same for all the columns you need to remove decimal places.

Upvotes: 9

Related Questions