Request a Demo
Home   >   mailing lists

[NOVICE] group by rollup and cube - Mailing list pgsql-novice

From john snow
Subject [NOVICE] group by rollup and cube
Date
Msg-id CAE67tvX188M=fbzE8tQahfWhCN6sUWSZH7HojVw+DC7RA6=33A@mail.gmail.com
Whole thread
Responses Re: [NOVICE] group by rollup and cube
List pgsql-novice
Tree view
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 by date:

Previous
From: Lutz Horn
Date:
Subject: Re: [NOVICE] How to get n records from parent table and theirchildren
Next
From: Laurenz Albe
Date:
Subject: Re: [NOVICE] How to get n records from parent table and theirchildren