How do Subtotal and Grand total in sql quries

Posted by Cpatil1000 under ASP.NET on 6/9/2014 | Points: 10 | Views : 2030 | Status : [Member] | Replies : 4
hi,
I want write a sql quries for Subtotal and Grand tota

OrderID Order Date OrderAmt
----------- ---------- ---------- ----------
1 10/11/2003 10.50
2 10/11/2003 11.50
3 10/11/2003 1.25
Sub Total 23.25
4 10/12/2003 100.57
5 10/12/2003 19.99
Sub Total 120.56
6 10/13/2003 47.14
7 10/13/2003 10.08
8 10/13/2003 7.50
9 10/13/2003 9.50
Sub Total 74.22
Grand Total 218.03




Responses

Posted by: Elizabeth_Keen on: 6/9/2014 [Member] Starter | Points: 25

Up
0
Down
Hi
you can get an idea of doing subtotal from below link:
http://stackoverflow.com/questions/19387650/how-to-add-a-subtotal-row-in-sql
and further for Grand total you can use this query:
select Order, date,sum(QrderAmt) as Total from tbl1 group by OrderDate

select sum(OrderAmt) as GrandTotal from tbl1.
select sum(Subtotal) as GrandTotal from equipment
Hope you get an idea from this.
http://www.ati-erp.com

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsm_Gsv on: 6/10/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

In Oracle you can get it by using compute and break commands.
BREAK on specific column will give subtotal.
BREAK on report will give Grand Total.


---------------------------------------
Live the life you've dreamed

Regards
MADHU

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 6/12/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer
http://www.databasejournal.com/features/mssql/article.php/3112381/SQL-Server-Calculating-Running-Totals-Subtotals-and-Grand-Total-Without-a-Cursor.htm

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 6/12/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
DECLARE @tab TABLE(OrderID int, OrderDate Date,  OrderAmt DEC(5,2))
INSERT @tab
SELECT 1, '10/11/2003', 10.50 union all
SELECT 2 , '10/11/2003', 11.50 union all
SELECT 3 , '10/11/2003', 1.25 union all
SELECT 4 , '10/12/2003', 100.57 union all
SELECT 5 , '10/12/2003', 19.99 union all
SELECT 6 , '10/13/2003', 47.14 union all
SELECT 7 , '10/13/2003', 10.08 union all
SELECT 8 , '10/13/2003', 7.50 union all
SELECT 9 , '10/13/2003', 9.50


SELECT CASE WHEN OrderId= 0 AND OrderDate IS NULL THEN 'Grand Total'
WHEN ORDERID= 0 THEN 'Sub Total' ELSE '' END as ReportHeading, OrderID , ISNULL(OrderDate, '99991231') OrderDate, OrderAmt
FROM (SELECT * FROM @tab UNION
SELECT 0, OrderDate,SUM(OrderAmt)
FROM @tab
GROUP BY OrderDate WITH ROLLUP)T
ORDER BY OrderDate, CASE WHEN OrderId= 0 AND OrderDate IS NULL THEN 3
WHEN ORDERID= 0 THEN 2 ELSE 1 END ;


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response