How do I create a Total value 1

[Cpreston]

Hi

I have the following SQL View which lists all Products with the total for their own group for the date.
I would like to create a total for each date but cannot work out how to do this within the statement. Could someone please point me in the right direction

SELECT DeliveryDate, SUM(TREX) AS TREX, SUM(LooseStock) AS [Loose Stock], SUM(Mouldings) AS Mouldings, SUM(LengthStocks) AS [Length Stocks], SUM([Deck Ancillaries]) AS [Deck Ancillaries], SUM([Deck Rails]) AS [Deck Rails], SUM([MDF architraves loose]) AS [MDF architraves loose],
SUM([MDF skirtings loose]) AS [MDF skirtings loose], SUM([MDF windowboard loose]) AS [MDF windowboard loose], SUM(Newels) AS Newels, SUM(Spindles) AS Spindles
FROM dbo.[148-vwLoadingAnalysisForSmartView_FeedCP]
GROUP BY DeliveryDate
ORDER BY DeliveryDate DESC


Thanks

 

[gmmastros]

Do you mean this???

SELECT	DeliveryDate, 
		SUM(TREX) AS TREX, 
		SUM(LooseStock) AS [Loose Stock], 
		SUM(Mouldings) AS Mouldings, 
		SUM(LengthStocks) AS [Length Stocks], 
		SUM([Deck Ancillaries]) AS [Deck Ancillaries], 
		SUM([Deck Rails]) AS [Deck Rails], 
		SUM([MDF architraves loose]) AS [MDF architraves loose], 
		SUM([MDF skirtings loose]) AS [MDF skirtings loose], 
		SUM([MDF windowboard loose]) AS [MDF windowboard loose], 
		SUM(Newels) AS Newels, 
		SUM(Spindles) AS Spindles,

		SUM(TREX) + SUM(LooseStock) + SUM(Mouldings) + SUM(LengthStocks) + SUM([Deck Ancillaries]) + SUM([Deck Rails]) + SUM([MDF architraves loose]) + SUM([MDF skirtings loose]) + SUM([MDF windowboard loose]) + SUM(Newels) + SUM(Spindles) AS Total
		
FROM	dbo.[148-vwLoadingAnalysisForSmartView_FeedCP]
GROUP BY DeliveryDate
ORDER BY DeliveryDate DESC

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Hi

I just ran it and looks more or less like I am after. Only problem is if there is a NULL in the groups it then gives the total as NULL
Is there a way around this

Thanks

 

[gmmastros]

Wrap each part in Coalesce, like this...

Coalesce(SUM(TREX), 0) + Coalesce(SUM(LooseStock), 0) + Coalesce(SUM(Mouldings), 0) etc....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Hi

Thanks a lot, I also managed it with this method

SUM(isnull(TREX,0)) + SUM(isnull(LooseStock,0)) + SUM(isnull(Mouldings,0)) + SUM(isnull(LengthStocks,0)) + SUM(isnull([Deck Ancillaries],0)) + SUM(isnull([Deck Rails],0)) + SUM(isnull([MDF architraves loose],0)) + SUM(isnull([MDF skirtings loose],0)) + SUM(isnull([MDF windowboard loose],0)) + SUM(isnull(Newels,0)) + SUM(isnull(Spindles,0)) AS Total
FROM dbo.[148-vwLoadingAnalysisForSmartView_FeedCP]

But not sure if it is the preferred method.

Brilliant replies thanks