Add Max(Count(column)) in SQL MS Access

Question

I have the following example dataset and as you can see the column X is the class ID, column Y is each class name, and finally column Z is the other universities class ID used for each subject. The goal is to get X, and Z with a grouping by X and get the most common ID used by Z (Other universities).

Sheet 1:

X	Y	Z
123	22	122
123	22	123
123	22	122
256	21	256
256	21	255
341	33	400
341	33	400

The outcome should be:

X	Z
123	122
256	255
341	400

I tried by adding the following query, but it is only returning the maximum from all the the table but not only for each value in column Z.

SELECT Sheet1.X, Sheet1.Z
  FROM Sheet1
  GROUP BY Sheet1.X, Sheet1.Z
  HAVING COUNT(Sheet1.Z) =
    (SELECT MAX(sheet2.count) FROM
        (SELECT Sheet1.X, Sheet1.Z, COUNT(Sheet1.Z) AS count
        FROM Sheet1
        GROUP BY Sheet1.X, Sheet1.Z) as sheet2);

Any suggestion of what I am doing wrong?

Gordon Linoff · Accepted Answer · 2021-03-04T13:47:03.377

0

This is a real pain in MS Access. But if you want one row per X, then you can do:

SELECT s1.X, s1.Z
FROM Sheet1 as s1
GROUP BY s1.X, s1.Z
HAVING s1.Z = (SELECT TOP (1) s2.Z
               FROM Sheet1 as s2
               WHERE s2.X = s1.X
               GROUP BY s2.Z
               ORDER BY COUNT(*) DESC, s2.Z
              );

If you want multiple rows in the event of ties, then use:

SELECT s1.X, s1.Z
FROM Sheet1 as s1
GROUP BY s1.X, s1.Z
HAVING COUNT(*) IN (SELECT TOP (1) COUNT(*)
                    FROM Sheet1 as s2
                    WHERE s2.X = s1.X
                    GROUP BY s2.Z
                   );

I should note that this would be simpler in just about any other database.

edited Mar 04 '21 at 13:47

answered Mar 03 '21 at 12:24

Gordon Linoff

1,242,037
58
646
786

Thanks a lot for your reply Gordon, but actually none of those examples are working. I am wondering if it is because of sheet2 stated in your examples it is just a duplication of the current table Sheet1 or are you considering other option to get that table? I appreciate your help. – Juan Espinosa Mar 04 '21 at 10:34
@JuanEspinosa . . . That was a typo. Your question only mentions one table so this should only refer to one table. – Gordon Linoff Mar 04 '21 at 13:47
Thank you so much for your help! this clearly solves my issue. I have to learn to think in that way. – Juan Espinosa Mar 04 '21 at 15:48

Add Max(Count(column)) in SQL MS Access

1 Answers1