ROLLUP
ROLLUP
(OP)
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
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
CODE --> sql
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
RE: ROLLUP
CODE -->
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
RE: ROLLUP
Thanks
RE: ROLLUP
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
RE: ROLLUP
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,
RE: ROLLUP
Try this:
CODE -->
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
RE: ROLLUP
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.
CODE --> sql
RE: ROLLUP
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads