• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Query with Total at Bottom

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
redmountainmachinery
Asked:
redmountainmachinery
  • 2
1 Solution
 
Patrick MatthewsCommented:
Hi redmountainmachinery,

Afet the HAVING clause, add:

COMPUTE SUM bylinetotal

Regards,

Patrick
0
 
redmountainmachineryAuthor Commented:
Thanks...

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

thanks a bunch :-D
0
 
Patrick MatthewsCommented:
You're most welcome :)

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now