Calculating a Subtotlal

[koutlaw30]

I am trying to create a subtotal that calculates the account numbers based on the first 2 digits.

Account ShortDesc LongDesc Balance
1000 Petty Cash Operating Cash 250.00
1001 Bradley Bank Checking Account 500.00
1100 Downey Bank Checking Account 200.00
1101 BoardmanFoods Bakery Department 150.00
1201 BoardmanFoods Bakery Department 130.00
1202 Petty Cash Operating Cash 400.00


SELECT Account, ShortDesc, LongDesc, Balance, SUM(Balance) OVER (partition by NULL) AS Subtotal FROM dbo.COA
WHERE Account LIKE '10%'
OR
Account LIKE '20%'
OR
Account LIKE '30%' OR
Account LIKE '40%'

I want the result to look like this:

Account ShortDesc LongDesc Balance Subtotal
1000 Petty Cash Operating Cash 250.00
1001 Bradley Bank Checking Account 500.00 750.00

1100 Downey Bank Checking Account 200.00
1101 BoardmanFoods Bakery Department 150.00 350.00

1201 BoardmanFoods Bakery Department 130.00
1202 Petty Cash Operating Cash 400.00 530.00


I would also like to have a Grand_Total field that calculates the grand total at the end of the query.

 

[markros]

You can create a stored procedure and use two simple CTEs (if SQL Server 2005 or up) for adding SubTotals to your query (and GranTotal).

However, the question sounds like it's done from some report? If yes, the report itself should have options allowing to add totals and grandtotals.

Anyway, if my idea is not clear to you, I can help writing the exact queries for you to include SubTotal and GrandTotal fields (these fields would be in all records).

 

[koutlaw30]

Yes, Could you please help me write the queries for me to include SubTotal and GrandTotal fields? Thanks for the help!

 

[markros]

with cte_SubTotals as (SELECT Account, ShortDesc, LongDesc, Balance, SUM(Balance) OVER (partition by LEFT(Account,2)) AS SubTotal FROM dbo.COA
WHERE Account LIKE '10%'
OR
Account LIKE '20%'
OR
Account LIKE '30%' OR
Account LIKE '40%')

select cte_SubTotals.*, (select sum(Balance) from cte_SubTotals) as GrandTotal from cte_SubTotals

From the top of my head (not tested)