Обсуждение: [NOVICE] group by rollup and cube
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!
john snow wrote: > i'm trying to learn how to do these from these examples that i found in: > > https://www.databasejournal.com/features/mssql/using-the-rollup-cube-and-grouping-sets-operators.html > > 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. A query result has no guaranteed ordering unless you specify one with an ORDER BY clause. ORDER BY is executed after the grouping, so you can use it to sort the result rows. You'll have to come up with an ORDER BY clause that puts the result in the desired order. The NULLS LAST clause will help in some of your cases. For example, for your first question I'd use ORDER BY type NULLS LAST to put the summary line at the bottom. Yours, Laurenz Albe -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice