How to group and order Jet SQL Query by count 2

[Idmpc]

Hi everyone,

As usual my thread title might be confusing, so here's what I've been trying to do but with no success:

I have a table with the fields ERROR and DETAILS.

here's an example of the data inside the table:

  ERROR        DETAIL
    A            D1
    A            D1
    B            D2
    C            D1
    B            D2
    B            D3
    C            D2
    A            D3
    A            D3
    C            D3
    C            D2
    C            D2

What I need to do is a query that can count which of the errors (A, B or C) has appeared more times on the table and order them on a descending order. Then, it needs to count how many times each detail happened for each error, and order them on descending order too.

From the table above, we can see that Error "C" has 5 records, Error "A" has 4 records and Error "B" has 3 records. Then, for Error "C", we can see that Detail "D2" has 3 records, while "D1" and "D3" only have 1 record each, and so on for Errors "A" and "B".

I tried to use the following Query:

SELECT ERROR, DETAIL, COUNT (DETAIL) AS [COUNT]
FROM TABLE
GROUP BY ERROR, DETAIL
ORDER BY  SUM (COUNT (ERROR)) DESC, COUNT(DETAIL) DESC

but I got the following error:
Cannot have aggregate function in ORDER BY clause (SUM(COUNT(APN)))

I also tried using:

ORDER BY COUNT (ERROR) DESC, COUNT (DETAIL) DESC

but that way, the results would NOT be ordered together by ERROR count, they would just be ordered by DETAIL count, for example:

ERROR DETAIL COUNT
  C     D2     3
  B     D2     2
  A     D3     2
  A     D1     2
  C     D3     1
  C     D1     1
  B     D3     1

The results I need should be:

ERROR DETAIL COUNT
  C     D2     3
  C     D3     1
  C     D1     1
  A     D3     2
  A     D1     2
  B     D2     2
  B     D3     1

It is very important for the Error's to be ordered by their total count (C = 5, A = 4, B = 3) and then, for each Error, we order the "Detail" count in descending order.

Does anyone know how should I write the SQL query for this?

Thank you very much!

 

[lespaul]

typed, not tested:

SELECT T.ERROR, T.Detail, Count(*) As DetailCount
FROM TableName T
INNER JOIN (SELECT ERROR, COUNT(*) As ErrorCount FROM TableName GROUP BY ERROR) B on T.ERROR = B.ERROR
GROUP BY T.ERROR, T.DETAIL
ORDER BY B.ErrorCount, 3

replace TableName with YOUR table's name.

HTH

Leslie

Have you met Hardy Heron?

 

[Idmpc]

Thank you lespaul

However, when I tried to type that query of yours (I did change the TableName to my table's name), I got the following error:

You tried to execute a query that does not include the specified expression 'B.ErrorCount' as part of an aggregate function.

Do you have any other ideas?

Thank you!

 

[PHV]

SELECT T.ERROR, T.Detail, Count(*) As DetailCount
FROM TableName T
INNER JOIN (SELECT ERROR, COUNT(*) As ErrorCount FROM TableName GROUP BY ERROR) B on T.ERROR = B.ERROR
GROUP BY T.ERROR, B.ErrorCount, T.DETAIL
ORDER BY B.ErrorCount, 3

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

What that error message is saying is that Access doesn't like the fact that we want to order by a field that isn't included in the group by clause of the query.

We can eliminate that (I think) by using this, but it will have the extra field in your result set that shows the total number of errors at the higher level:

SELECT T.ERROR, T.Detail, B.ErrorCount, Count(*) As DetailCount
FROM TableName T
INNER JOIN (SELECT ERROR, COUNT(*) As ErrorCount FROM TableName GROUP BY ERROR) B on T.ERROR = B.ERROR
GROUP BY T.ERROR, T.DETAIL, B.ErrorCount
ORDER BY B.ErrorCount, 3

Leslie

Have you met Hardy Heron?

 

[Idmpc]

Thank you, PHV and lespaul! It's ALMOST 100% working!

I changed the ORDER BY clause to:

ORDER BY B.ErrorCount DESC, 3

Now it is ordering my Errors correctly, but the details are being ordered from minimum to maximum (ascending), and I also need the details to be descending order.

I tried modifying to this:

ORDER BY B.ErrorCount DESC, 3, T.DetailCount DESC

But then I got the following error message:

Parameter T.DetailCount has no default value.

How can I make it work, and what is the ",3" doing on the ORDER BY clause?

Thank you once again!

 

[PHV]

SELECT T.ERROR, T.Detail, Count(*) As DetailCount
FROM TableName T
INNER JOIN (SELECT ERROR, COUNT(*) As ErrorCount FROM TableName GROUP BY ERROR) B on T.ERROR = B.ERROR
GROUP BY T.ERROR, B.ErrorCount, T.DETAIL
ORDER BY B.ErrorCount DESC, 3 DESC


what is the ",3" doing
It tells the SQL engine to sort by the 3rd field in the SELECT list.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Idmpc]

Alright, that works perfectly!

Thank you very much everyone!