I have a data table with a data source of the following SQL query:

SELECT entry.id 'task',
title.meta_value 'task_title',
hours.meta_value 'hours_estimated',
CASE WHEN workflowstep.meta_value='pending' THEN 'Unapproved' WHEN workflowstep.meta_value='approved' THEN 'Approved' ELSE 'Not Sure' END 'status'
FROM wp_tmee_gf_entry entry
LEFT OUTER JOIN wp_tmee_gf_entry_meta workflowstep
ON entry.form_id=workflowstep.form_id
AND entry.id=workflowstep.entry_id
AND workflowstep.meta_key='workflow_step_status_3'
LEFT OUTER JOIN wp_tmee_gf_entry_meta hours
ON entry.form_id=hours.form_id
AND entry.id=hours.entry_id
AND hours.meta_key=21
LEFT OUTER JOIN wp_tmee_gf_entry_meta title
ON entry.form_id=title.form_id
AND entry.id=title.entry_id
AND title.meta_key=6
LEFT OUTER JOIN wp_tmee_gf_entry_meta users
ON entry.form_id=users.form_id
AND entry.id=users.entry_id
AND users.meta_key=2
LEFT OUTER JOIN wp_tmee_usermeta userclient
ON users.meta_value=userclient.user_id
AND userclient.meta_key='sherpa_client'
WHERE userclient.meta_value='%VAR1%'

The hours.meta_value has a data type of longtext. See attachment 2020-11-24-13-49-40.png.

I'm trying to show the total of hours.meta_value (which is renamed to "Estimated" in the table), but it comes up as 0. See attachment 2020-11-24-13-51-18.png.

I think the problem has to do with the value being longtext so I tried casting the value to either DECIMAL or FLOW in the SQL code by replacing this line:

hours.meta_value 'hours_estimated',

with either of these two lines:

CAST(hours.meta_value AS DECIMAL(4,2)) 'hours_estimated',
CAST(hours.meta_value AS FLOAT) 'hours_estimated',

However, both of those lines give me the following error when I try saving the table:

MySQL said: Unknown column 'CAST' in 'field list'

Can you help me get the "Estimated" column total to be the correct non-zero value?

Thanks for the tip. It's not a big table so I disabled server-side processing and the total is now working correctly.

Thanks for the tip about creating a MySQL View if it's a big table. I'll keep that in mind for the future.

You can close the ticket.