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

  • Question

  • User-1160571205 posted

    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?

    Tuesday, November 27, 2018 11:46 AM

All replies

  • User269602965 posted

    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.

    Thursday, November 29, 2018 3:01 AM
  • User364663285 posted

    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)



    Saturday, January 5, 2019 2:08 AM