By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,455 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Getting a total of a number of group by results

P: 6
I need to summate across a number of line items over a group of invoices, then round down on an invoice by invoice basis to the nearest penny.
I would then like to get a total of THOSE rounded values.
Does my sql need to return the items, and i then manually summate these values by reading each one by one, or can I do this in the sql. Remember, I still need to round on an invoice by invoice basis.
My sql which returns rounded invoice totals is given below:


SELECT
SUM(FLOOR(sii.unitPrice*sii.Qty)) AS total
FROM salesinvoiceItem sii,
salesinvoice si
WHERE sii.invoiceId = si.invoiceId
GROUP BY si.InvoiceId

;

This returns a number of rows which I then need a total for: is there an elegant way of achieving this in sql server in a query?
Apr 2 '08 #1
Share this Question
Share on Google+
1 Reply


P: 6
Sorted it:

SELECT SUM(total)
FROM
(
SELECT
SUM(FLOOR(sii.unitPrice*sii.Qty)) AS total
FROM salesinvoiceItem sii,
salesinvoice si
WHERE sii.invoiceId = si.invoiceId
GROUP BY si.InvoiceId )somename
Apr 2 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.