grouping data in single row

Question

I create and populate the following table:

CREATE TABLE plch_sales
(
   product     VARCHAR2(10)
 , country     VARCHAR2(10)
 , year        NUMBER
 , sales       NUMBER
)
/

BEGIN
   INSERT INTO plch_sales VALUES ('BANANA', 'US', 2009, 200);
   INSERT INTO plch_sales VALUES ('BANANA', 'US', 2010, 300);
   INSERT INTO plch_sales VALUES ('BANANA', 'GB', 2009, 400);
   INSERT INTO plch_sales VALUES ('BANANA', 'GB', 2010, 350);
   INSERT INTO plch_sales VALUES ('BANANA', 'DK', 2010, 250);
   INSERT INTO plch_sales VALUES ('APPLE' , 'US', 2009, 100);
   INSERT INTO plch_sales VALUES ('APPLE' , 'GB', 2009, 150);
   INSERT INTO plch_sales VALUES ('APPLE' , 'GB', 2010, 150);
   INSERT INTO plch_sales VALUES ('APPLE' , 'DK', 2009, 250);
   INSERT INTO plch_sales VALUES ('APPLE' , 'DK', 2010, 250);
   INSERT INTO plch_sales VALUES ('PEAR'  , 'GB', 2010, 150);
   INSERT INTO plch_sales VALUES ('PEAR'  , 'DK', 2009, 300);
   INSERT INTO plch_sales VALUES ('PEAR'  , 'DK', 2010, 350);

   COMMIT;
END;
/

My boss wants sales totals listed by product, by country and a grand total.

Which of the choices produce this desired output:

TOTAL   PRODUCT    COUNTRY         SALES
------- ---------- ---------- ----------
Product APPLE      TOTAL             900
Product BANANA     TOTAL            1500
Product PEAR       TOTAL             800
Country TOTAL      DK               1400
Country TOTAL      GB               1200
Country TOTAL      US                600
Grand   TOTAL      TOTAL            3200

I don't need only sql rather need to understand the technicality of sql. If any one could help please how to solve this type of sql queries?

Answer 1

An easier approach that @GordonLinoff's answer to this is to split your problem into multiple queries that are easy to understand and union them:

select 'Product' as category, product as item, sum(sales) from plch_sales
group by product
union all
select 'Country' as category, country as item, sum(sales) from plch_sales
group by country
union all
select 'Total' as category, 'TOTAL' as item, sum(sales) from plch_sales
group by 1

yields

CATEGORY    ITEM    SUM(SALES)
Product     PEAR    800
Product     BANANA  1500
Product     APPLE   900
Country     US      600
Country     DK      1400
Country     GB      1200
Total       TOTAL   3200

A solution that yields the same result (with a little bit different columns), that's way easier to understand.

Answer 2

You can use grouping sets and some conditional logic:

select (case when product is not null then 'Product'
             when country is not null then 'Country'
             else 'Total'
        end) as total,
       coalesce(product, 'Total') as product,
       coalesce(country, 'Total') as country,
       sum(sales)
from plch_sales
group by grouping sets ( (product), (country), () );

This uses the short-hand that NULL represents the grouping columns. This works because you have no NULL values in this case. You can also use the GROUPING() "function".