[NOVICE] group by rollup and cube

Поиск
Список
Период
Сортировка
От john snow
Тема [NOVICE] group by rollup and cube
Дата
Msg-id CAE67tvX188M=fbzE8tQahfWhCN6sUWSZH7HojVw+DC7RA6=33A@mail.gmail.com
обсуждение исходный текст
Ответы Re: [NOVICE] group by rollup and cube  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список pgsql-novice
Дерево обсуждения
i'm trying to learn how to do these from these examples that i found in:


after modifying the code to run in postgresql 10, i'm finding that even though i'm getting subtotal and grand total values same as those in the article, i'm not quite getting the report rows in the desired orders.

create table purchase_item (
  id serial primary key,
  supplier varchar,
  type varchar,
  amount money,
  purchase_date date
);

INSERT INTO purchase_item (supplier, type, amount, purchase_date) VALUES
      ('McLendon', 'Hardeware',2121.09,'2014-01-12'),
      ('Bond','Electrical',12347.87,'2014-01-18'),
      ('Craftsman','Hardware',999.99,'2014-01-22'),
      ('Stanley','Hardware',6532.09,'2014-01-31'),
      ('RubberMaid','Kitchenware',3421.10,'2014-02-03'),
      ('RubberMaid','KitchenWare',1290.90,'2014-02-07'),
      ('Glidden','Paint',12987.01,'2014-02-10'),
      ('Dunns','Lumber',43235.67,'2014-02-21'),
      ('Maytag','Appliances',89320.19,'2014-03-10'),
      ('Amana','Appliances',53821.19,'2014-03-12'),
      ('Lumber Surplus','Lumber',3245.59,'2014-03-14'),
      ('Global Source','Outdoor',3331.59,'2014-03-19'),
      ('Scotts','Garden',2321.01,'2014-03-21'),
      ('Platt','Electrical',3456.01,'2014-04-03'),
      ('Platt','Electrical',1253.87,'2014-04-21'),
      ('RubberMaid','Kitchenware',3332.89,'2014-04-20'),
      ('Cresent','Lighting',345.11,'2014-04-22'),
      ('Snap-on','Hardware',2347.09,'2014-05-03'),
      ('Dunns','Lumber',1243.78,'2014-05-08'),
      ('Maytag','Appliances',89876.90,'2014-05-10'),
      ('Parker','Paint',1231.22,'2014-05-10'),
      ('Scotts','Garden',3246.98,'2014-05-12'),
      ('Jasper','Outdoor',2325.98,'2014-05-14'),
      ('Global Source','Outdoor',8786.99,'2014-05-21'),
      ('Craftsman','Hardware',12341.09,'2014-05-22');

*********************
FOR 
select 
  coalesce(type, 'Grand Total') as PurchaseType,
  Sum(amount) as SummarizedPurchaseAmount
from purchase_item
group by ROLLUP(type);

****** how can i get the Grand Total row to be the last row in the result set?


FOR
select 
  extract(month from purchase_date) as PurchaseMonth,
  case
    when extract(month from purchase_date) is null then 'Grand Total'
else coalesce(type,'Monthly Total')
  end as PurchaseType,
  sum(amount) as SummarizedPurchaseAmount
from purchase_item
group by ROLLUP(extract(month from purchase_date), type);

**** how can i also get PurchaseMonth  and  PurchaseType to be in the same ascending reporting order
as shown in the article?


FOR
select 
  extract(month from purchase_date) as PurchaseMonth,
  case
    when extract(month from purchase_date) is null 
then coalesce('Grand Total for ' || type, 'Grand Total')
else coalesce(type,'Monthly Total')
  end as PurchaseType,
  sum(amount) as SummarizedPurchaseAmount
from purchase_item
group by CUBE(extract(month from purchase_date), type);

**** how can i also get PurchaseMonth, PurchaseType, Monthly Total, and Grand Total for [PurchaseType] to be in the same reporting order
as shown in the article?

thank you for helping!

В списке pgsql-novice по дате отправления:

Предыдущее
От: Lutz Horn
Дата:
Сообщение: Re: [NOVICE] How to get n records from parent table and theirchildren
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: [NOVICE] How to get n records from parent table and theirchildren