My SQL query result issue when using aggregate and union all

110 views Asked by At

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 
1

There are 1 answers

1
Gordon Linoff On

I think the problem are the data types. Convert the price to a string and then use ifnull():

  IFNULL(format(price, 4), '') AS `price`,
  IFNULL(format(price * 0.1, 4), '') AS `gst`,
  IFNULL(format(price * 1.1, 4), '') AS `price_ex_gst`

As a note: I would use coalesce() instead of ifnull(), because coalesce() is the ANSI standard function for this purpose.