asked on
Select Rows including their SUM in the last
Hi,
I have requirement as below:
My table:
Name Amount
Raj 100
Manj 200
Jag 300
John 400
Required Result:
Name Amount
Raj 100
Manj 200
Jag 300
John 400
Total 1000
I tried using UNION and it working.
But I want to write this in one query. or any other better way without using UNION or temp tables.
Thanks in advance.
I have requirement as below:
My table:
Name Amount
Raj 100
Manj 200
Jag 300
John 400
Required Result:
Name Amount
Raj 100
Manj 200
Jag 300
John 400
Total 1000
I tried using UNION and it working.
But I want to write this in one query. or any other better way without using UNION or temp tables.
Thanks in advance.
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
Jagdish Devaku
As far as I know, it is not possible except for UNION, temp tables or table variable...
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
this may meet your needs, there are drawbacks such as you have to avoid a name being NULL
SELECT COALESCE(dq.name, 'TOTAL'), dq.total FROM (select name, sum(amount) AS total from YourTable
group by name with rollup) AS DQ
SELECT COALESCE(dq.name, 'TOTAL'), dq.total FROM (select name, sum(amount) AS total from YourTable
group by name with rollup) AS DQ
Also, without an order by you never know how the rows will line up. If this matters...
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I tried using UNION and it working.
But I want to write this in one query.
Just a minor quible: A SELECT statement with a UNION clause is still one query.
But I want to write this in one query.
Just a minor quible: A SELECT statement with a UNION clause is still one query.
ASKER
Thanks all for the help.
I am checking the queries.
I am checking the queries.
ASKER
Thanks for all your support.