August 28, 2003 at 3:48 am
I want a query that returns only the grand total with no subtotals.
For example, I want a resultset of all orders for a day that includes customer id, name and includes the total amount of the order. If I use WITH ROLLUP I get subtotals for each customer which I don't want - I simply want one row per customer plus the overall total.
I can, of course, do this via a UNION query (first query returns the row per customer bit and the second query the overall total) which performs fine, but I wondered if there was a 'proper' way of doing it in one query.
August 28, 2003 at 3:51 am
quote:
I want a query that returns only the grand total with no subtotals.For example, I want a resultset of all orders for a day that includes customer id, name and includes the total amount of the order. If I use WITH ROLLUP I get subtotals for each customer which I don't want - I simply want one row per customer plus the overall total.
Use a simple Group by Command as
Select customerId,name,Sum(AMount) from
orders Order by CustomerId,Name
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 28, 2003 at 4:42 am
Sorry, I didn't word my question clearly.
Getting the line total isn't the problem. Getting an extra row at the end of the recordset with the overall total is the problem.
August 28, 2003 at 4:50 am
quote:
Sorry, I didn't word my question clearly.Getting the line total isn't the problem. Getting an extra row at the end of the recordset with the overall total is the problem.
Sorry I did not read it properly.
Your original solution of union seems to be the ideal way
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 28, 2003 at 4:54 am
Does this need to be done in SQL Server?
I think this is presentation stuff, that could easily be handled at the front end
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 28, 2003 at 5:59 am
Hi Frank,
Yes, you're right it could be done in the front-end and probably should (to reduce the load on the server). I was just wandering if I was missing some trick in T-SQL.
August 29, 2003 at 1:13 am
Yes you can do it with SQL, but I don't know
wether I would do it this way ....
SELECT CASE
WHEN GROUPING(CONTROL_1)=1 AND GROUPING(CONTROL_2)=1
THEN 'TOTAL'
ELSE 'DETAIL'
END TYPE
, CONTROL_1
, CONTROL_2
, SUM(HED_AMOUNT_L_H) SUBTOTAL
FROM DM2_HIS_HED HH
GROUP BY CONTROL_1,CONTROL_2 WITH ROLLUP
HAVING (GROUPING(CONTROL_1)=0 AND GROUPING(CONTROL_2)=0)
OR (GROUPING(CONTROL_1)=1 AND GROUPING(CONTROL_2)=1)
-- Could even do 'HAVING GROUPING(CONTROL_1)=GROUPING(CONTROL_2)'
August 29, 2003 at 4:39 am
Thanks Gary, that's spot on!!
August 30, 2003 at 3:25 am
You could use COMPUTE(Amount) or take the
SUM(Amount) and group by the rest of the columns specified in the SELECT Statement, to achive you result.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply