question

moose782 avatar image
moose782 asked

Percentage Calculation with two decimal digit rounded off in mysql

I have a query that I would like to have the perc_complete rounded off (17.4122) to (17.41).

SELECT
weldlogs.job_ID,
weldlogs.weld_phase,
weldlogs.shop_field,
weldlogs.area,
SUM(CASE WHEN weldlogs.vt_date > date_sub(NOW(), INTERVAL 1 DAY) THEN weld_inches.adjusted_weld_inches ELSE 0 END) AS daily_inches,
SUM(CASE WHEN weldlogs.vt_date = SUBDATE(CURDATE(),1) THEN weld_inches.adjusted_weld_inches ELSE 0 END) AS yesterday,
SUM(CASE WHEN (YEARWEEK(vt_date) = YEARWEEK(CURRENT_DATE)) THEN weld_inches.adjusted_weld_inches ELSE 0 END) AS weekly_inches,
SUM(CASE WHEN YEARWEEK(weldlogs.vt_date) = YEARWEEK(CURRENT_DATE - INTERVAL 7 DAY) THEN weld_inches.adjusted_weld_inches ELSE 0 END) AS last_week,
SUM(CASE WHEN YEAR(weldlogs.vt_date ) = YEAR(CURDATE()) AND MONTH(weldlogs.vt_date ) = MONTH(CURDATE())THEN weld_inches.adjusted_weld_inches ELSE 0 END) AS monthly_inches,
SUM(CASE WHEN SUBSTRING(weldlogs.vt_date  FROM 1 FOR 7) = SUBSTRING(CURRENT_DATE - INTERVAL 1 MONTH FROM 1 FOR 7) THEN weld_inches.adjusted_weld_inches ELSE 0 END) AS last_month,
SUM(CASE WHEN (weldlogs.vt_date IS NOT NULL) THEN weld_inches.adjusted_weld_inches ELSE 0 END) AS total_inches_completed,
SUM(weld_inches.adjusted_weld_inches) AS total_inches_needed,
(SUM(CASE WHEN (weldlogs.vt_date IS NOT NULL) THEN weld_inches.adjusted_weld_inches ELSE 0 END))-(SUM(weld_inches.adjusted_weld_inches)) AS remaining,
(SUM(CASE WHEN (weldlogs.vt_date IS NOT NULL) THEN weld_inches.adjusted_weld_inches ELSE 0 END))/SUM(weld_inches.adjusted_weld_inches) AS perc_complete
FROM weld_inches
LEFT OUTER JOIN weldlogs ON weld_inches.pipe_size = weldlogs.pipe_size AND weld_inches.wall_thickness = weldlogs.wall_thickness AND weld_inches.schedule = weldlogs.pipe_schedule
GROUP BY weldlogs.job_ID, weldlogs.weld_phase, weldlogs.shop_field, weldlogs.area


querymysql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.