How to add row to view that contains the total (MySQL)RSS

• • Reply
  rookiecookie

  None

  0 Points

  1 Post

  How to add row to view that contains the total (MySQL)

  Nov 27, 2018 11:46 AM|rookiecookie|LINK

  I know this question has been asked before but I've been trying the solutions and I'm still getting errors.

  I've created a view to view the quantity of life jackets by size:

  ---------------LIFE JACKET LIST------------

  CREATE VIEW Life_Jacket_List AS

  SELECT size,SUM(quantity) AS Quantity

  FROM life_jackets LEFT JOIN equipment USING(model)

  GROUP BY size with ROLLUP

  Which looks like this:

  size	Quantity
  L	48
  M	39
  S	35
  NULL	122

  Basically I just want to change the "null" to "Total". Here is what I've done so far:

  CREATE VIEW Life_Jacket_List AS

  SELECT size, size = ISNULL(size, ‘Total’), TotalLife = SUM(quantity), SUM(quantity AS Quantity

  FROM life_jackets LEFT JOIN equipment USING(model)

  GROUP BY size with ROLLUP

  I'm getting an error though:

  1582 - Incorrect parameter count in the call to native function 'ISNULL'

  Any idea on how to fix this?

• • Reply
  Lannie

  Contributor

  3382 Points

  1318 Posts

  Re: How to add row to view that contains the total (MySQL)

  Nov 29, 2018 03:01 AM|Lannie|LINK

  Try if MySQL supports it (Oracle does)..

  SELECT
    DECODE(GROUPING(size),1,'TOTAL',size) as Size,
    SUM(quantity) AS Quantity
  FROM life_jackets
  LEFT JOIN equipment USING(model)
  GROUP BY size with ROLLUP
  /

  The test value of 1 is the first level of the grouping, otherwise it is 0 the root level of the grouping.