Solved

Query with Total at Bottom

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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

22 Experts available now in Live!

Get 1:1 Help Now