How do Subtotal and Grand total in sql quries

Posted by Cpatil1000 under ASP.NET on 6/9/2014 | Points: 10 | Views : 4316 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

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

Latest Posts