[Given Table]

SQL Command on Access

'''

SELECT [WERKS], count(*) FROM (SELECT DISTINCT Status FROM [Sheet1$]) FROM [Sheet1$] GROUP BY [WERKS]

'''

Expected Output

AXYZ-2

BXYZ-2

Actual Output :

Error in FROM Clause

CodePudding user response：

You can try:

SELECT COUNT(Code), Code FROM tableName WHERE Status not like 'Success' GROUP BY Code

This work for all Status that are not equal to 'Success'.

CodePudding user response：

If you bust it into two groupings you can get the output you want

SELECT t1.Code, Count(t1.Code) AS NumStatus
FROM 
    (SELECT [Sheet1$].Code, [Sheet1$].Status
    FROM [Sheet1$]
    GROUP BY [Sheet1$].Code, [Sheet1$].Status)  AS t1
GROUP BY t1.Code;

CodePudding user response：

try this

SELECT code, count(Distinct code , status) FROM [Sheet1$] GROUP BY code