Grand Total Per Day

[Cpreston]

Hi

I have the following script which lists a totalvolume by a product level code.

So I may have for one day several rows. What I want to achieve is to have a Grand Total for each day in a row below the last date for the day.
Not sure if this is possible at all or if someone as an idea how I can sum the total for each day.

Thanks in advance

SELECT dbrderHeader.DateRequired,dbo.ProductGroup.Name AS [Level 2], SUM(dbrderLine.TotalVolume)
AS [Total Volume]
FROM dbrderHeader INNER JOIN
dbrderLine ON dbrderHeader.OrderID = dbrderLine.OrderID INNER JOIN
dbo.Product ON dbrderLine.ProductID = dbo.Product.ProductID INNER JOIN
dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID INNER JOIN
dbo.ProductGroup AS ProductGroup_1 ON dbo.ProductGroup.ParentID = ProductGroup_1.ProductGroupID
WHERE (dbrderHeader.StockIssued = 0) AND (ProductGroup_1.Name = 'Arborflor') AND (dbrderHeader.DateRequired >= GETDATE()) OR
(dbrderHeader.StockIssued = 0) AND (dbrderHeader.DateRequired >= GETDATE()) AND (dbo.ProductGroup.Name IN ('mouldings', 'loose stock', 'Spindles',
'Newels', 'Handrail Kits', 'DeckAncillaries', 'DeckPosts', 'DeckRails', 'MDF Loose', 'Length stocks', 'MDF architraves loose', 'MDF skirtings loose',
'MDF windowboard loose')) AND (dbrderHeader.DateRequired >= CAST(GETDATE() AS DATE)) AND (dbrderHeader.DateRequired <= DATEADD(day, 7,
CAST(GETDATE() AS DATE)))
GROUP BY dbrderHeader.DateRequired, dbo.ProductGroup.Name
ORDER BY dbrderHeader.DateRequired

 

[TamarGranor]

If I correctly understand what you want, you can use ROLLUP() for this. Change your GROUP BY clause to :

GROUP BY dbo.ProductGroup.Name, ROLLUP(dbo.OrderHeader.DateRequired)

I've written about ROLLUP. You can see the article at

http://www.tomorrowssolutionsllc.com/Articles/Summarizing aggregated data Part 1.pdf.

(Because I'm writing for Visual FoxPro developers, the first half or so shows how to do this kind of thing in that product, which doesn't have ROLLUP.)

Tamar

 

[Cpreston]

Hi

I tired the

GROUP BY dbo.ProductGroup.Name, ROLLUP(dbrderHeader.DateRequired)

but I am getting

Msg 10707, Level 15, State 1, Line 13
The CUBE() and ROLLUP() grouping constructs are not allowed in the current compatibility mode. They are only allowed in 100 mode or higher.

Any ideas please

Thanks

 

[Cpreston]

A little step forward I sued this line

GROUP BY GROUPING SETS (dbo.ProductGroup.Name, dbrderHeader.DateRequired ), (dbrderHeader.DateRequired )

Attached is a screen shot of how it looks

From this you can see that it is totalling the daily totals (which is great) but it puts it as NULL and also it as mixed up slightly

The Total for 14/06 is at the top but the total for 15/06 is 2 down in the list for 15/06.

So can the NULL name be TOTAL in anyway and also can it be at the bottom of each day instead of mixed up in them??

Thanks

 

[TamarGranor]

Yes, use COALESCE( dbrderHeader.DateRequired,dbo.ProductGroup.Name, "TOTAL") AS [Level 2] in the field list.

Tamar

 

[Cpreston]

HI

I have my select part like below

SELECT COALESCE (dbrderHeader.DateRequired,dbo.ProductGroup.Name) AS [Level 2], SUM(dbrderLine.TotalVolume)
AS [Total Volume]

However it shows the result for Level 2 and TotalVolum but does not show the column for Product group

Any ideas please

Thanks

 

[TamarGranor]

Your result will include exactly the columns you specify in the field list. You're specifying two columns, so that's what you get. What do you actually want?

Tamar

 

[Cpreston]

It should return

Date Required, ProductGroup Name and TotalSum

Thanks

 

[TamarGranor]

Rather than try to give you an exact answer, I'll explain. COALESCE() takes a list of expressions and returns the first non-null value. So wrap that around whichever field was giving you nulls that you didn't want.

Tamar