Aggreagte - adding new column to table (1 Viewer)

88videos

New member
Local time
Today, 12:09
Joined
Jul 22, 2019
Messages
4
Welcome.


My first post = first problem with Access. I use MS Access for a short time. I am familiar with MS SQL, but Access disappoints me. It is difficult for me to write commands, because many options does not work.


My current problem. I want to aggregate and add column with aggregated statistics to table in a one query. OVER PARTITION does not work, so I need to write 2 queries(1 - group by SEX INDEX LEVEL 2 - group by SEX INDEX). I am sure that more sophisticed solution can do this in one query, but i need help.



data for example


my input


ID SEX YEAR INDEX LEVEL
1 1 MALE 2019 WEALTH low
2 2 MALE 2018 STRENGHT low
3 3 MALE 2019 WEALTH low
4 4 MALE 2019 WEALTH average
5 5 MALE 2018 STRENGHT high
6 6 MALE 2018 WEALTH average
7 7 MALE 2019 STRENGHT high
8 8 MALE 2018 STRENGHT low
9 9 MALE 2019 STRENGHT low
10 10 MALE 2018 WEALTH low
11 11 MALE 2019 STRENGHT high
12 12 MALE 2018 STRENGHT low
13 13 MALE 2019 STRENGHT low
14 14 MALE 2019 STRENGHT low
15 15 MALE 2019 STRENGHT high
16 16 FEMALE 2018 WEALTH low
17 17 FEMALE 2018 STRENGHT average
18 18 FEMALE 2019 WEALTH high
19 19 FEMALE 2018 WEALTH average
20 20 FEMALE 2018 STRENGHT low
21 21 FEMALE 2019 WEALTH high
22 22 FEMALE 2018 WEALTH average
23 23 FEMALE 2018 WEALTH average
24 24 FEMALE 2018 STRENGHT low
25 25 FEMALE 2018 STRENGHT low
26 26 FEMALE 2018 WEALTH low
27 27 FEMALE 2019 STRENGHT high
28 28 FEMALE 2019 STRENGHT low
29 29 FEMALE 2018 WEALTH high
30 30 FEMALE 2019 STRENGHT high




my output should look like this


SEX INDEX LEVEL x.x x.y
1 FEMALE STRENGHT low 4 7
2 FEMALE STRENGHT high 2 7
3 FEMALE STRENGHT average 1 7
4 FEMALE WEALTH low 2 8
5 FEMALE WEALTH high 3 8
6 FEMALE WEALTH average 3 8
7 MALE STRENGHT low 6 10
8 MALE STRENGHT high 4 10
9 MALE WEALTH low 3 5
10 MALE WEALTH average 2 5




Thanks in advance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:09
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to the forum. Have you tried using a subquery? It's hard to read the sample data you posted. Are you able to reformat it using the code tags to line up the columns? Either that or post a sample Access or Excel file showing test data and expected result from the query. Thanks.
 

plog

Banishment Pending
Local time
Today, 14:09
Joined
May 11, 2011
Messages
11,611
I want to aggregate and add column with aggregated statistics to table in a one query

First, don't over-jargon us. There's of lot database terms above but not any cohesive database idea.


Code:
ID SEX YEAR INDEX LEVEL
1 1 MALE 2019 WEALTH low

Second,don't delimit your data with a character that appears in the data. You've used 4 spaces in the column heading row and 5 spaces in the data row. Which data value contains a space? No idea.

Use a comma to delimit your data.

Third, explain what type of aggregation you are doing? Presumably a count, but what about the other numeric value in the expected results? Explain what x.x and x.y represent please.
 

Micron

AWF VIP
Local time
Today, 15:09
Joined
Oct 20, 2018
Messages
3,476
I read it as a Count of INDEX and a Count of LEVEL based on SEX (there are 7 of FEMALE STRENGHT (sp?) and 4 of them are low. I don't see how it's possible because there's no way to group on SEX if every value is preceded by a number. Maybe I have the goal right, but I agree that the data sample is of no help.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:09
Joined
Feb 19, 2013
Messages
16,553
suggest virtual table is the way to go in access - basically including your second query in the first query


Code:
SELECT A.sex, A.index, Level, count(A.ID) as X, B.Y
FROM myTable A INNER JOIN (SELECT sex, index, count(ID) AS Y FROM myTable) B ON A.sex=B.sex AND A.Index=B.Index
GROUP BY A.sex, A.index
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:09
Joined
Feb 19, 2013
Messages
16,553
think the first data column is a rownumber
 

88videos

New member
Local time
Today, 12:09
Joined
Jul 22, 2019
Messages
4
CJ_London understand my goal, but I need to admit - I didn' t describe it well.
And my data wasn't gooed prepared to paste it on forum --- was '\t' as separator ...Sorry for that.



I can write 2 queries .


1) this works

SELECT sex,
index, count(ID) AS Y
FROM MYtable
GROUP by sex, index




2) this works too



SELECT
A.sex,
A.index,
A.Level,
count(A.ID) as X
FROM MYtable A
GROUP BY A.sex, A.index, A.Level



but i want to keep this in one query.


According to sugestion, I used query inside other query, but i failed with this...


My query



SELECT
A.sex,
A.index,
A.Level,
count(A.ID) as X,
B.Y
FROM MYtable A
INNER JOIN
(
SELECT sex,
index, count(ID) AS Y
FROM MYtable
GROUP by sex, index
) B
ON A.sex=B.sex AND A.Index=B.Index
GROUP BY A.sex, A.index, A.Level





I tried with CJ_London code, but without success.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:09
Joined
Feb 19, 2013
Messages
16,553
but i failed with this...
but without success.
tells us nothing - you get an error? wrong result? something else?

You appear to have quoted my air code back to me - and I can't debug aircode - do you really have a table called 'MYtable'?

Only thing I can see that might be causing an issue is if your field is really called Index. Index is a reserved word and should not be used for field, table or control names. Recommend change it to a more meaningful name. Alternatively try surrounding with square brackets which works a lot of the time.

Here is a link to reserved words - use them if you are happy spending a lot of time trying to figure out why something isn't working

https://support.office.com/en-us/ar...-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2
 

88videos

New member
Local time
Today, 12:09
Joined
Jul 22, 2019
Messages
4
I tried this



SELECT A.sex, A.index, A.Level as lev, count(A.ID) as X, B.Y
FROM MYtable A INNER JOIN (SELECT sex, index, count(ID) AS Y FROM MYtable) B ON A.sex=B.sex AND A.Index=B.Index
GROUP BY A.sex, A.index, lev


sex is not part of agg function

I modified query to this


SELECT
A.sex,
A.index,
A.Level,
count(A.ID) as X,
B.Y
FROM MYtable A
INNER JOIN
(
SELECT sex,
index, count(ID) AS Y
FROM MYtable
GROUP by sex, index
) B
ON A.sex=B.sex AND A.Index=B.Index
GROUP BY A.sex, A.index, A.Level


in QUERY 'Y' is not part of agg fuction
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:09
Joined
Feb 19, 2013
Messages
16,553
Ah OK, you need to add grouping of B.Y to the outer query - last line

GROUP BY A.sex, A.index, A.Level, B.Y
 

Users who are viewing this thread

Top Bottom