ROLLUP

[Cpreston]

Hi

I have a query which is sued in our ERP system view. The view allows us to select the dates but does not give us the ability to Grand Total things.

The query is a SUM of fields in another view and works fine. However I would like to have a Grand Total of SUM(PackCounter) AS [Total Packs] I added in the With ROLLUP but this does not show as named field and just adds it at the bottom and also gives Grand Total for all fields where it can and puts NULL in the rest. How can I get the grand total so it gives it a named field like GrandTotal and then I can link it to the ERP system view. Thanks in advance

SELECT        TOP (100) PERCENT DateRequired, SUM(Carcassing) AS Carcassing, SUM(Russian) AS Russian, SUM(Scandinavian) AS Scandinavian, SUM(Fencing) AS Fencing, SUM([Bulk Shed]) AS [Bulk Shed], 
                         SUM([Mini-packs]) AS [Mini-packs], SUM(Bespoke) AS Bespoke, SUM(Arbordeck) AS Arbordeck, SUM(MDF) AS MDF, SUM(Arborflor) AS Arborflor, SUM(PackCounter) AS [Total Packs]
FROM            dbo.[148-vwPacksSoldByGroup&Customerp2CP]
GROUP BY DateRequired WITH ROLLUP
ORDER BY DateRequired DESC

 

[Mike Lewis]

Isn't this what the COALESCE clause is for?

SELECT TOP (100) PERCENT COALESCE (DateRequired, 'Total Packs') ... etc.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Cpreston]

Do you have to put COALESCE for every Field. I did try that yesterday but got stuck with the syntax and could no get it working

Thanks

 

[Mike Lewis]

Do you have to put COALESCE for every Field.

I don't think so. When you think about it, you are adding exactly one row per group, and that row contains the totals for all the columns. As far as I know, there is no way of specifying that you want totals for some columns and not others. What COALESCE does is let you specify the name that goes in the column that you are grouping on (DateRequired in this case) - regardless of which column's totals you are interested in, so you only COALESCE the grouping column.

I am not sitting at my system at the moment, so I haven't tried this myself. If I am wrong, someone else here will put me right.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Cpreston]

Hi

It is the syntax I am not getting

I have started like and the first AS then as a red underscore and is not recognised

SELECT TOP (100) PERCENT COALESCE (DateRequired), SUM(Carcassing) AS Carcassing,

 

[Mike Lewis]

You're nearly there.

Try this:

SELECT
  TOP (100) PERCENT [b]COALESCE (DateRequired, 'Total Packs')[/b], SUM(Carcassing) AS Carcassing, 
   [i]... etc. ...[/i]
FROM  dbo.[148-vwPacksSoldByGroup&Customerp2CP]
GROUP BY DateRequired WITH ROLLUP
ORDER BY DateRequired DESC

You don't need an AS clause with the COALESCE column. The column name will be Total Packs.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Cpreston]

Hi

Ok my code I as attached but I ma now getting this error

Msg 295, Level 16, State 3, Line 20
Conversion failed when converting character string to smalldatetime data type.
Warning: Null value is eliminated by an aggregate or other SET operation.

SELECT        TOP (100) PERCENT COALESCE(DateRequired, 'TotalPacks'), SUM(Carcassing) AS Carcassing, SUM(Russian) AS Russian, SUM(Scandinavian) AS Scandinavian, SUM(Fencing) AS Fencing, SUM([Bulk Shed]) AS [Bulk Shed], 
                         SUM([Mini-packs]) AS [Mini-packs], SUM(Bespoke) AS Bespoke, SUM(Arbordeck) AS Arbordeck, SUM(MDF) AS MDF, SUM(Arborflor) AS Arborflor, SUM(PackCounter) AS [Total Packs]
FROM            dbo.[148-vwPacksSoldByGroup&Customerp2CP]
GROUP BY DateRequired WITH ROLLUP
ORDER BY DateRequired DESC

 

[Mike Lewis]

Ah, yes. I see what's happening. DateRequired is a smalldatetime, but we are trying to place a string ('Total Packs') into the same column. I think you will need to use CONVERT() to convert DateRequired to a string. But I'm not sure how that will play with the grouping and ordering. Might need to experiment a bit.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads