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]
       , [B].[IDVOLUME]
FROM     [A]
         INNER JOIN
         (SELECT   [ID]
                 , SUM([VOLUME]) AS [IDVOLUME]
          FROM     [A]
          GROUP BY [ID]) AS [B]
  ON     [A].[ID]=[B].[ID]
GROUP BY [A].[ID]
       , [B].[ID]
       , [A].[SUBID]
       , [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

really simulated subtotal, but no-use to next step ^^, still thank Rabbit.

[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

[Rabbit]

The reporting option also calculates subtotals, I find that 99% of the time, the reporting option is the preferred method.

[hvsummer]

yes, unlucky I'm the rest 1%..
coz I calculate everything in access, can do this ezier in excel, but I don't want setup too much steps. I need subtotal or grantotal as a part of fomular sothat reporting option does not help, I have to generate result (fomular relative to subtotal like Volum-weighted) with query first.

Sometime people have to do that too, so I think we should findout their need to fullfill it rather than give "our best direction" every question.

[Rabbit]

The "best direction" should always take into account the user's need. If they need it in query form and have a valid reason, then I would give them the 1% solution. If they are unclear or don't need it calculated within the query, then the 99% solution is the answer I would give them.

[NeoPa]

HVSummer:
yes, unlucky I'm the rest 1%.

Just to be clear, it's the use case rather than the user that determines what is appropriate for any given scenario. It's possible for a designer to provide a solution that doesn't match the requirements very well. There are always many ways to provide a solution.

Nevertheless, there are sometimes cases when the 1% is needed. That will be clear because the 99% won't work for some reason. It won't be connected in any way with who the developer is. If your choice is to go with the 1% regardless of the use case then that simply means you're not doing it the most appropriate way.

We will always suggest, to the best of our ability, the most appropriate way. We see that as our responsibility. What you do from there is your own lookout. We see that as yours.

[hvsummer]

yea, I know what is the right direction for most of people, and you guy help me much when resolved my funny problem.

the reason i don't use report or form or anything else other than query is that I'm using Access as back-end, excel as front-end, I pull all data from access and show up in excel.

and the real reason, I'm lazy to calculate with excel :p
just want to calculate everything within query then pull out the result. It's hard, but it's worth for me.

[NeoPa]

Indeed. Depending on various parameters of what you need the spreadsheet for and how you're using it, that could make it one of the 1%. Essentially that's for you to decide anyway - not if it's a 1% but rather what you want to do.

As long as we've given the advice we're happy with then it's fine to explore other options.

[Rabbit]

Well, excel can calculate it for you if you feed the results of the query into a pivot table