See the explanation for Step by Step Solution and configuration.
Explanation:
Solution :
Step 1 : Select Maximum, minimum, average , Standard Deviation, and total quantity.
val results = sqlContext.sql('.....SELECT MAX(price) AS MAX , MIN(price) AS MIN ,
AVG(price) AS Average, STD(price) AS STD, SUM(quantity) AS total_products FROM products......) results. showQ
Step 2 : Select minimum and maximum price for each product code.
val results = sqlContext.sql(......SELECT code, MAX(price) AS Highest Price', MIN(price)
AS Lowest Price'
FROM products GROUP BY code......)
results. showQ
Step 3 : Select Maximum, minimum, average , Standard Deviation, and total quantity for each product code, hwoever make sure Average and Standard deviation will have maximum two decimal values.
val results = sqlContext.sql(......SELECT code, MAX(price), MIN(price),
CAST(AVG(price} AS DECIMAL(7,2)) AS Average', CAST(STD(price) AS DECIMAL(7,2))
AS 'Std Dev\ SUM(quantity) FROM products
GROUP BY code......)
results. showQ
Step 4 : Select all the product code and average price only where product count is more than or equal to 3.
val results = sqlContext.sql(......SELECT code AS Product Code',
COUNTf) AS Count',
CAST(AVG(price) AS DECIMAL(7,2)) AS Average' FROM products GROUP BY code
HAVING Count >=3"M") results. showQ
Step 5 : Select maximum, minimum , average and total of all the products for each code.
Also produce the same across all the products.
val results = sqlContext.sql( """SELECT
code,
MAX(price),
MIN(pnce),
CAST(AVG(price) AS DECIMAL(7,2)) AS Average',
SUM(quantity)-
FROM products
GROUP BY code
WITH ROLLUP""" )
results. show()