SELECT product, SUM(quantity) as qty
FROM inventory
GROUP BY product;
SELECT warehouse, product, SUM(quantity) AS qty
FROM inventory
GROUP BY warehouse, product;
UNION ALL
SELECT warehouse, null, SUM(quantity) as qty
FROM inventory
GROUP BY warehouse
UNION ALL
SELECT null, product, SUM(quantity) as qty
FROM inventory
GROUP BY product
UNION ALL
SELECT null, null, SUM(quantity) as qty
FROM inventory;
SELECT COALESCE(warehouse, 'ALL') as store,
COALESCE(product, 'ALL') as product,
SUM(quantity) qty
FROM INVENTORY
GROUP BY
GROUPING SETS(
(warehouse, product),
(warehouse),
(product),
()
)
ORDER BY qty desc;
SELECT COALESCE(warehouse, 'All Stores'),
COALESCE(product,'All Phones'),
SUM(quantity) as qty
FROM inventory
GROUP BY ROLLUP (warehouse, product);
SELECT
COALESCE(warehouse, '...All Warehouses') AS warehouse,
COALESCE(product, '...All Products') AS product,
SUM(quantity)
FROM
inventory
GROUP BY
CUBE(warehouse, product)
ORDER BY
warehouse,
product;
SELECT
COALESCE(department_name, '-') AS department,
COALESCE(job_title,'-') AS job,
COUNT(*),
SUM(salary) AS salary
FROM
employees_cubed
INNER JOIN departments_cubed USING (department_id)
INNER JOIN jobs_cubed USING (job_id)
GROUP BY
CUBE(department_name,job_title)
ORDER BY
department_name, job_title ASC NULLS LAST;
SELECT
COALESCE(department_name, '-') AS department,
COALESCE(job_title,'-') AS job,
COUNT(*) ,
SUM(salary) salary
FROM
employees_cubed
INNER JOIN departments_cubed USING (department_id)
INNER JOIN jobs_cubed USING (job_id)
GROUP BY
CUBE(department_name,job_title)
ORDER BY
department_name ASC NULLS LAST;
SELECT
COALESCE(department_name, '∑') AS department,
COALESCE(job_title,'∑') AS job,
COUNT(*),
SUM(salary) AS salary
FROM
employees_cubed
INNER JOIN departments_cubed USING (department_id)
INNER JOIN jobs_cubed USING (job_id)
GROUP BY
CUBE(department_name, job_title)
ORDER BY
department_name, job_title ASC NULLS LAST;
SELECT warehouse, COALESCE(product,'All Phones'),
SUM(quantity) as qty
FROM inventory
GROUP BY warehouse, ROLLUP(product);
SELECT *
FROM store_sales;
SELECT
DATE_PART('year', order_date) AS fyear,
DATE_PART('month', order_date) AS month,
gross,
SUM(gross) OVER(
PARTITION BY DATE_PART('year', order_date)
ORDER BY DATE_PART('year', order_date),
DATE_PART('month', order_date)
) AS monthly_total_gross
FROM store_sales
ORDER BY order_date;
