sabecs
asked on
MySQL - remove $ from results and add "-" for a specific group?
Hi,
Is it possible to modify my select statement below to include a negative sign "-" for rec_yearly_total when budget_type is equal to 'E' ?
Also can I remove a '$' sign from the rec_yearly_total column?
Thanks,
Andrew
Is it possible to modify my select statement below to include a negative sign "-" for rec_yearly_total when budget_type is equal to 'E' ?
Also can I remove a '$' sign from the rec_yearly_total column?
Thanks,
Andrew
SELECT
case when budget_type = 'I' then 'INCOME'
when budget_type = 'E' then 'EXPENSE'
else budget_type
end AS 'INCOME or EXPENSE'
, rec_description AS DESCRIPTION
, case when budget_group_id = 1 then 'Income'
when budget_group_id = 2 then 'Commitments'
when budget_group_id = 3 then 'Home'
when budget_group_id = 4 then 'Utilities'
when budget_group_id = 5 then 'Education'
when budget_group_id = 6 then 'Health'
when budget_group_id = 7 then 'Shopping'
when budget_group_id = 8 then 'Transport'
when budget_group_id = 9 then 'Entertainment'
when budget_group_id = 10 then 'Eating Out'
else budget_group_id
end AS 'GROUP HEADING'
, case when rec_frequency = 'D' then 'Daily'
when rec_frequency = 'W' then 'Weekly'
when rec_frequency = 'F' then 'Fortnightly'
when rec_frequency = 'M' then 'Monthly'
when rec_frequency = 'Y' then 'Yearly'
else rec_frequency
end AS FREQUENCY
, rec_total AS TOTAL
, rec_yearly_total AS 'YEARLY TOTAL'
FROM `budget_planner`
WHERE user_id = '5028' ORDER BY budget_type, budget_group_id
ASKER
Thanks chaau, the "$' sign is contained in the rec_yearly_total field, for example $123.45 and I was wondering if I can remove it to leave just the 123.45 ?
Perhaps I could remove the first char somehow via the query?
Perhaps I could remove the first char somehow via the query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks chaau, that is perfect...
Open in new window
However, I do not see $ sign anywhere in the SQL query. It must be the frontend that puts it. Can you advise what you use for the reporting?