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.
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)
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:
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?
Contributor
3462 Points
1341 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.
Contributor
6479 Points
5815 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
Peter