×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

ROLLUP

thread183-1803753

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

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

Isn't this what the COALESCE clause is for?

CODE --> 

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: ROLLUP

(OP)
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

RE: ROLLUP

Quote:

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

RE: ROLLUP

(OP)
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,

RE: ROLLUP

You're nearly there.

Try this:

CODE --> 

SELECT
  TOP (100) PERCENT COALESCE (DateRequired, 'Total Packs'), SUM(Carcassing) AS Carcassing, 
   ... etc. ...
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

RE: ROLLUP

(OP)
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.

CODE --> 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

RE: ROLLUP

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close