Solved

Query with Total at Bottom

Posted on 2006-06-13
3
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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