Solved

Query with Total at Bottom

Posted on 2006-06-13
3
228 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now