Subtotal and Grandtotal Query

[Simply Me]

how do i create the query for the attached file?

[RhinoBull]

Use some report designer where you can create groups and easily add the SubTotal and GrandTotal fields.

That's the easy way.

[Simply Me]

i use CR for it and created a group on Client Name but i can put all together client with the name starting with SOCO and get the subtotal for it.

[szlamany]

Another method is to do three queries - with UNION ALL between them. You need some "hidden" columns to accomplish the ordering of the data so that detail rows are followed by the proper sub-total row and that the grand total appears last.

We use this method often for on-screen displays of financial data exactly as you have shown.

[Simply Me]

any sample code please szlamany?

[szlamany]

Something like this:

Select SLFund,Sum(CreditAmt) "Credit",Sum(DebitAmt) "Debit"
	From Ledger_T
	Where FiscalYr=2007
	Group by SLFund
Union All
Select 'Total',Sum(CreditAmt),Sum(DebitAmt)
	From Ledger_T
	Where FiscalYr=2007
Order by 1

will produce this

SLFund Credit                Debit                 
------ --------------------- --------------------- 
00     792437959.9800        783178605.8400
01     177198927.4500        428240800.6500
03     .0000                 696615.0000
04     .0000                 .0000
22     1160643.0500          1169541.2000
24     3328528.3800          3232697.3500
25     3804055.4000          5509371.1700
26     311709.1600           13557.1600
.
.
.
83     10847789.9700         19387104.9000
84     1389528.9000          1584028.0600
87     2027578.0100          1695525.1400
89     5550044.9900          4904125.6400
90     .0000                 .0000
91     7000000.0000          7000000.0000
99     1181486.0900          1321246.9200
Total  1011301154.1200       1272637349.1100

(30 row(s) affected)

I am ordering by column 1 - but you could easily "build" a sort column that is smart enough to put your subtotals at the bottom of each group and your grand total at the bottom.

[Simply Me]

The following are the contents of my tables:

Under tblClient:
ClientID ClientName Budget
Client1 SOCO-Main 5000.00
Client2 SOCO-Apopong 960.00
Client3 SOCO-Calumpang 960.00
Client4 Doctor's Hospital 3000.00

Under tblClient_Consume_Products:
ProductID ClientID Qty Purchases SalesPrice
Prod0001 Client1 5.00 18.75 21.56
Prod0002 Client1 3.00 18.00 20.70
Prod0002 Client4 2.00 18.00 20.70
Prod0001 Client3 3.00 18.75 21.56
Prod0001 Client2 2.00 18.75 21.56
Prod0002 Client2 1.00 18.00 20.70
Prod0001 Client4 1.00 18.75 21.56
Prod0002 Client4 1.00 18.00 20.70

I have a query using the two tables above and INSERT the results in a temporary table named as tblTempMonthly. Below is my query:

'  Delete tblTemp records before inserting records to it.
  sSQL = "DELETE FROM tblTempMonthly"
  oConn.Execute sSQL
 
  sSQL1 = "INSERT INTO tblTempMonthly " & _
            "(ClientName, " & _
            "Budget, " & _
            "Purchases, " & _
            "Selling)" & _
  "SELECT tblClient.ClientName, " & _
            "tblClient.ClientBudget,  " & _
            "SUM(tblClient_Consume_Products.Purchases * tblClient_Consume_Products.Quantity) AS Purchases, " & _
            "SUM(tblClient_Consume_Products.SalesPrice * tblClient_Consume_Products.Quantity) As Selling " & _
  "FROM tblClient INNER JOIN " & _
        "tblClient_Consume_Products ON  " & _
        "tblClient.ClientID = tblClient_Consume_Products.ClientID " & _
  "WHERE (tblClient_Consume_Products.DateOut BETWEEN '" & Format(DTPStart.Value, "mm-dd-yyyy") & "' AND '" & Format(DTPEnd.Value, "mm-dd-yyyy") & "' ) " & _
  "GROUP BY tblClient.ClientName, tblClient.ClientBudget"
  oConn.Execute sSQL1

Result of the above query:
ClientName Budget Purchase Selling
SOCO-Apopong 960.00 55.50 63.82
SOCO-Calumpang 960.00 56.25 64.68
Doctor's Hospital 3000.00 72.75 83.66
SOCO-Main 5000.00 147.75 169.90

I used the records of temporary table in Crystal Report 9. I used grouped by clientname and here is the result:
ClientName Budget Purchase Selling
Doctor's Hospital 3000.00 72.75 83.66
SOCO-Apopong 960.00 55.50 63.82
SOCO-Calumpang 960.00 56.25 64.68
SOCO-Main 5000.00 147.75 169.90
Total 9920.00 332.25 382.06

i want it to be like this:
SOCO-Apopong 960.00 55.50 63.82
SOCO-Calumpang 960.00 56.25 64.68
SOCO-Main 5000.00 147.75 169.90
Sub-Total 6920.00 259.50 298.40

Doctor's Hospital 3000.00 72.75 83.66
Sub-Total 3000.00 72.75 83.66
Grand Total 9920.00 332.25 382.06


Please Help me.

[szlamany]

Usually with a UNION ALL you need to fake a column that you can then order by.

I don't know what makes a SOCO vs a "Doctor's Hospital" different in your data - but let's assume it's a field called SocoFlag with a Y or N

Select x,y,z,Case When SocoFlag='Y' Then 'A' Else 'B' End + tblClient.ClientName...
Union All
Select 'Sub-Total',sum(y),sum(z),Case When SocoFlag='Y' Then 'AZZ' Else 'BZZ' End
Union All
Select 'Total',sum(y),sum(z),'C'
Order by 4

The 'A', 'B' or 'C' as the first character of the 4th column helps to group the data rows with the "sub-total" rows. Then you simply ORDER BY that column and everything comes out as expected.

[Simply Me]

SOCO and Doctor's are all client. I just wanted to get the subtotal of all SOCO Client and subtotal of the rest of the client and then get the grandtotal as what is reflected in the attachment. Thanks zslamany...I'll try to do what you said.