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

  3422 Points

  1333 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.

• • Reply
  wmec

  Contributor

  6451 Points

  5777 Posts

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

  Jan 05, 2019 02:08 AM|wmec|LINK

  Try this in MYSQL

  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
  union all SELECT 'Total, SUM(quantity)
  FROM life_jackets LEFT JOIN equipment USING(model)

  
  
  

  Many Thanks & Best Regards,
  Ai Guo