How to simulate the subtotal in Select or group by query ?

[hvsummer]

I want to simulate the pivot table.
how to sum the subtotal of the father field, like this

ID - SubID - SumOfSubID -- TotalbyID
1 --- A ------- 11 ------------------------ 95
1 --- B ------- 13 ------------------------ 95
1 --- C ------- 71 ------------------------ 95
2 --- T ------- 33 ------------------------ 55
2 --- H ------- 22 ------------------------ 55

how to calculate the TotalbyID like this (ex: ID = 1 then TotalByID = 11+13+71) ?
At the moment I'm using stacked query, but how can I do this with subquery or any method faster ?

[Rabbit]

This is the first time I've heard the phrase stacked query. What is that?

One option to emulate subtotals is to union in an aggregate query that groups by your subtotal category.

[NeoPa]

It seems to me that you can either use linked queries, as you are, or Domain Aggregate functions such as DSum(). I expect what you have is already the faster of the two anyway.

[NeoPa]

Rabbit:
This is the first time I've heard the phrase stacked query. What is that?

I assumed the OP was referring to an aggregated subquery linked to the main data source on the common fields (In this case just [ID]).

[hvsummer]

@Rabbit: Stacked query is the query used inside another query, sound like traped query. let me see, UNION can combine 2 query and I don't have to play a query inside another ? ==

@NeoPa: Dsum() and his relative are whom I want avoid the most,
they slow me down too much haha
I'll try rabbit's suggestion, union, but I have never use UNION before lol, let see if this can be done.

[NeoPa]

HVSummer:
@NeoPa: Dsum() and his relative are whom I want avoid the most

Very wise. Note I was not suggesting you try that as I assumed what you had was already better.

HVSummer:
I'll try rabbit's suggestion, union, but I have never use UNION before lol, let see if this can be done.

If you haven't played with UNION queries before then certainly go off and explore them. They can be very powerful and add flexibility.

In this case, I don't see where the benefit would come from above and beyond the use of subqueries (Subqueries in SQL), which is what I assume you started with.

Having said that, Rabbit is certainly extremely clever, and particularly so when it comes to SQL, so I expect there's something I've missed.

[hvsummer]

well, UNION must have the same select field's number, and it work like
append query, append bonus query into a query ==
if I use subquery, how to do it :-? (usually stacked query faster than subquery, but I'll try once, i won't let this simple task take away 2 slot queries from me ==)
my code uptill now (subquery method):

SELECT A.ID, A.SUBID, SUM(A.VOLUME) AS SUMOFSUBID, B.TOTALBYID
FROM A INNER JOIN 
(SELECT ID, SUM(A.VOLUME) AS TOTALBYID FROM A) AS B ON A.ID = B.ID
ORDER BY A.ID;

[NeoPa]

I'd be looking at something pretty similar.

SELECT   [A].[ID]
       , [A].[SUBID]
       , SUM([A].[VOLUME]) AS [SUBIDVOLUME]
       , [NOPARSE][[/NOPARSE]B].[IDVOLUME]
FROM     [A]
         INNER JOIN
         (SELECT   [ID]
                 , SUM([VOLUME]) AS [IDVOLUME]
          FROM     [A]
          GROUP BY [ID]) AS [NOPARSE][[/NOPARSE]B]
  ON     [A].[ID]=[NOPARSE][[/NOPARSE]B].[ID]
GROUP BY [A].[ID]
       , [NOPARSE][[/NOPARSE]B].[ID]
       , [A].[SUBID]
       , [NOPARSE][[/NOPARSE]B].[IDVOLUME]
ORDER BY [A].[ID]
       , [A].[SUBID]

[hvsummer]

wow, It did work, Thank NeoPA, you showed me that I forgot "group by" in a group by query == shame on me haha.

Tested on simple DB. next, apply this model to complex one lol

btw, I'll try to study UNION method, uptonow I only see it as a Append query-to-query from what I can find on Google.

last problem if use UNION looklike this
[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=85 00&stc=1&d=1445 023709[/IMGNOTHUMB]
really simulated subtotal, but no-use to next step ^^, still thank Rabbit.
[IMGNOTHUMB]http://bytes.com/attachments/attachment/8500d1445023709/1.png[/IMGNOTHUMB]

[zmbd]

On a side note, from good ole Allen Brown

http://allenbrowne.com/subquery-02.html

Note the differentiation of "stacked" queries vs. subquery.
In Access the "stacked query" is stored query that is referred to via a second query, third, etc...

The subquery we all know and love is where the SQL is created within the primary sql.

(AB has a related article here: http://allenbrowne.com/QueryPerfIssue.html )

I often start by building the individual queries for troubleshooting each piece, stacking as I go, and then combine for use in code... or just to tidy things up.

The issue I have with this is that stacking queries is a favorite SQL-Injection method used against RDMS (or any DBE) that is capable of running async multiple queries... or, well, so, I've been told...

[NeoPa]

HVSummer:
Thanks NeoPa

You're very welcome. I'm glad I was able to nudge you a little in the right direction.

ZMBD:
In Access the "stacked query" is stored query that is referred to via a second query, third, etc...

I'm curious where that term comes/came from. I've never heard a QueryDef object referred to as a stacked query before.

[Rabbit]

Sorry, the union wouldn't work. I misread the requirements. I thought the subtotals needed to be on their own row.

[hvsummer]

@Rabbit:
well, last time I want to simulate Subtotal,
but this time I found the way to do Subtotal, real subtotal with UNION

SELECT ID, SubID, Sum(Volume) as MTD
FROM Table1
GROUP BY ID, SubID
ORDER BY ID, SubID
UNION ALL
SELECT ID, "SubTotal "&Group as SubID, Sum(Volume) as MTD
FROM Table1
GROUP BY ID
ORDER BY ID, SubID;

Funny that, Idea just pop up when I answer another request lol

[Rabbit]

hvsummer, that's what my suggestion was in post #2. Except your sample results didn't match what your query would create, which is why I withdrew my suggestion and NeoPa made his suggestion.

But also, this kind of stuff is handled better at the report level because it allows for better formatting options.

[hvsummer]

@Rabbit: better formating is great, but user may want to calculate on the subtotal sothat, query sometime still usefull