I have the following query on a student enrollment table, which has the student_id, first_name, enrollment_date (DATETIME) , price (Float) as the columns, My problem is when I run this query on MySQL I get a "BLOB" value for the price column on the first part of the query when there is a value for price and in the second section of the query as well.
I want to extract the results to a csv, and do not want "NULL" in the excel sheet, hence I have used the IFNULL condition which seems to be the reason for "BLOB" value coming on the price column. If I don't have IFNULL on the price column I get the results with "NULL" being set for price column.
If I change the IFNULL(price,'') to IFNULL(price, 0) then also things work but I am artificially putting a '0' for price when it is null this I don't want to do ... any help ?
select
student_id AS AS `student_id`,
first_name AS `first_name`,
enrolment_date AS `enrolment_date`,
IFNULL(price, '') AS `price`,
IFNULL(price * 0.1, '') AS `gst`,
IFNULL(price * 1.1, '') AS `price_ex_gst`
from
student_enrolment
where
student_id = 123 and
month(enrolment_date) = 10
union all
select
student_id AS `student_id`,
count(1) AS `count(1)`,
'Total' AS `Total`,
sum(`price`) AS `sum(price)`,
(sum(`price`) * 0.1) AS `gst`,
(sum(`price`) * 1.1) AS `price_ex_gst`
from
student_enrolment
where
student_id = 123 and
month(enrolment_date) = 10
I think the problem are the data types. Convert the price to a string and then use
ifnull()
:As a note: I would use
coalesce()
instead ofifnull()
, becausecoalesce()
is the ANSI standard function for this purpose.