Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ROLLUP

Status
Not open for further replies.
Cpreston

Cpreston

MIS
Mar 4, 2015
987
GB
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

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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
Do you have to put COALESCE for every Field.
Click to expand...

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
 
Upvote 0 Downvote
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,
 
Upvote 0 Downvote
You're nearly there.

Try this:

Code: 
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
 
Upvote 0 Downvote
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.

SQL: 
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ladyemrys
  • Locked
  • Question
Place Rollup at the bottom and group by total in the rollup
Replies
0
Views
95
ladyemrys
ladyemrys
wbodger
  • Locked
  • Question
selective rollups?
Replies
7
Views
220
wbodger
wbodger
hwkranger
  • Locked
  • Question
SSRS Question -- Multi-group rollups -- recursive heirarchy
Replies
2
Views
130
hwkranger
hwkranger
greg52
  • Locked
  • Question
Rollup Problem
Replies
8
Views
197
greg52
greg52
brxmas
  • Locked
  • Question
Stored Procedure Using RollUP
Replies
0
Views
90
brxmas
brxmas

Part and Inventory Search

Sponsor

Back
Top