[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
|
Список | 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