Solved

Query with Total at Bottom

Posted on 2006-06-13
3
230 Views
Last Modified: 2006-11-18
I remember reading a something about SQL2000 Where I could return an overall total at the bottom of my sql results...

For Example... I have this little query


SELECT     item, SUM(bylinetotal) AS Amounts, ref_item
FROM         tbl_InvoiceItem
GROUP BY item, ref_item
HAVING      (ref_item = @ItemNumber) AND (SUM(bylinetotal) <> 0) AND (NOT (item LIKE N'TIRE%') AND NOT (item LIKE N'TR%') AND NOT (item IN (N'DL', N'DP',
                      N'FUEL', N'PPT', N'TDP')))

Results...

Item          Amounts               Ref_Item
AT81307        192203.7800           AT81307
HOLIDAY        -1005.0000            AT81307
MECH         -2330.0000             AT81307
OT            30918.5200      AT81307
RAIN           -7655.0000              AT81307

Is there a way to automatically add a total of the amounts to this query? Without doing subqueries?

Chuck
0
Comment
Question by:redmountainmachinery
  • 2
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 16895480
Hi redmountainmachinery,

Afet the HAVING clause, add:

COMPUTE SUM bylinetotal

Regards,

Patrick
0
 

Author Comment

by:redmountainmachinery
ID: 16895553
Thanks...

I remembered it as soon as I saw the word compute :-)

thanks a bunch :-D
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 16899416
You're most welcome :)

Patrick
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question