Sum by character within value (1 Viewer)

sumdumgai

Registered User.
Local time
Today, 19:14
Joined
Jul 19, 2007
Messages
453
Is it possible to group sums based on the 3rd character within a value? For example, let's say I have a table T1 with fields F1 and F2 with values as this:


F1, F2
2A1C, 30
2A2C, 15
2A1D, 20
2A1E, 10
2A2D, 5


Can you create a query that sums by the 3rd character in a field so that the resultant datasheet gives:


C, Qty
1, 60
2, 20



Thanks for any help.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:14
Joined
Aug 30, 2003
Messages
36,118
You can use the Mid() function to get the third character and group on it. Probably the different components of the existing field should be stored separately.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:14
Joined
Sep 21, 2011
Messages
14,048
Extract that third character with the MID() function as a separate field and group/total on that.
If you cannot do it all in one query, create a query with that field, and then a totals query on the first query.
 

sumdumgai

Registered User.
Local time
Today, 19:14
Joined
Jul 19, 2007
Messages
453
Something like this but I can't get it right?


Code:
SELECT T1.[F1], Sum(T1.F2) AS SumOfF2
FROM T1
GROUP BY mid(T1.[F1],3,1);
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:14
Joined
Sep 21, 2011
Messages
14,048
Try

Code:
SELECT MID(F1,3,1) AS C, Sum(F2) AS Qty FROM T1
GROUP BY MID(F1,3,1)
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:14
Joined
Jan 23, 2006
Messages
15,364
Try
Code:
SELECT Mid(T1.[F1],3,1) AS ThirdChar
, Sum(T1.F2) AS Qty
FROM T1
GROUP BY Mid(T1.[F1],3,1);


OOOoooops: I see Gasman has replied while I was testing.
 

Dreamweaver

Well-known member
Local time
Today, 23:14
Joined
Nov 28, 2005
Messages
2,466
I would look at adding an extra field to store the 3rd value as you will have one problem after another and it could even impact the speed of querys once you get a good sized recordset.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 28, 2001
Messages
27,001
sumdumgai - I do not want to be an obstructionist, but I see troubles like this continuing in your future. This is yet another example of "winging it" - which will occur when you are trying to fit a ton of "stuff" (you know what KIND of stuff) into a half-ton truck. I remember the problem you are working through.

There are two ways to handle this kind of thing and you picked the second, probably because it occurred "after the fact" of original design.

The CORRECT way to handle this was to have anticipated the need to search by sub-set, in which case you would have made the searchable sub-set markers as individual fields. Then if you needed to see them together, you could do a query that concatenated them - but when you needed them separately, they would be separate. However, you have a size issue that makes that difficult.

You can do nothing about this now and are forced to use the MID() function to pull apart what you want. (That was the SECOND way to do this.) Remember this the next time you start designing things for searching.

As a technical note, I believe this might have been approached a different way with stuff you actually have. Am I right in guessing that this thing you are searching is your product code that is actually a mish-mosh of several factors? Which is why you are trying to focus on a single character?

Because if so, you could STILL do the search directly another way - by a JOIN with your detailed product table where you have a single product key that LOOKS like 2A1C, but you have individual attributes in individual fields corresponding to the 2, A, 1, and C separately. Further, if you use the "IN" construct, you might even make this a tad faster. If I am correct in my supposition, here is what your query MIGHT look like...

Code:
SELECT T1.PROD, SUM( T1.QTY ) AS TSUM FROM T1 INNER JOIN PTABLE ON T1.PROD = PTABLE.PROD WHERE PTABLE.F3 = "C" GROUP BY T1.PROD ;

This might also work and MIGHT be faster:

Code:
SELECT T1.PROD, SUM( T1.QTY ) AS TSUM FROM T1 WHERE T1.PROD IN 
( SELECT PRODID FROM PTABLE WHERE F3 = "C" ) ;

Obviously, I'm using PTABLE as the name of your product table. Either of these approaches might work if you are still using the database that you described in your earlier sessions. And if I guessed wrong, then I wasted my time but less of yours.
 

Users who are viewing this thread

Top Bottom