CODEWITHSUNDEEP
sqlms-access
Req_7
Req_7

Reputation: 85

Count each unique values

I am using SQL in MS-Access. I would like to count each unique value from the column code in my table.

that's my table:

| **code** |   **description**      |      **date**    | **level** |  
|  AAA     |       example          |    01/01/2020    |     x     |
|  AAA     |       example          |    02/05/2020    |     z     |
|  BBB     |       example          |    09/09/2020    |     y     |
|  CCC     |       example          |    12/10/2020    |     z     |

expected result:

    | **code** |   **description**      |      **date**    | **level** | **count** |
    |  AAA     |       example          |    01/01/2020    |     x     |    2      |
    |  AAA     |       example          |    02/05/2020    |     z     |    2      |
    |  BBB     |       example          |    09/09/2020    |     y     |    1      |
    |  CCC     |       example          |    12/10/2020    |     z     |    1      |

I know the following query works:

SELECT code, count(*) FROM table GROUP BY code;

But I wanted the select to count the code column, and show all the other columns as well. I created this query:

SELECT code, description, date, level, count(code) 
FROM table 
GROUP BY code, description, date, level;

But it shows all lines in the "count" column as 1 (even those with repeated values).

Upvotes: 0

Views: 46

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You want to show all rows (no aggregation). You want to show an additional column with an aggregation. You can do this aggregation in a subquery.

SELECT
  code, description, date, level, 
  (SELECT COUNT(*) FROM table AS t2 WHERE t2.code = t.code) AS code_count
FROM table AS t
ORDER BY code, date;

As to your own query: You are pseudo aggregating. As all rows are different from each other, your GROUP BY on all columns keeps the original rows. Then you count how many rows you find in each "group" which is always 1 (the row itself).

Upvotes: 3

Related Questions