1

I have a table (lets call it "Items") that contains a set of names, groups and statuses. For example

Name | Group | Status

FF      A      ON
GG      A      OFF
HH      A      UNKN
ZZ      B      ON
YY      B      OFF

I am trying to aggregate the status of all records in a given group, by taking the most relevant status (in order by relevance: UNKN, OFF, ON).

Edit 1: These statuses are only examples, and their names and orders could change in my application, so that should be configurable in the query.

For example, if I query for the overall status of Group A, the status should be UNKN, and if I query for Group B, the status should be OFF.

Edit 2: It is possible that there are multiples of the same status for a group. For example two records that are UNKN.

The query I have managed is to select all items from a group. For example Group A:

SELECT Items.[Group], Items.[Status]
FROM Items
WHERE (((Items.[Group])="A"));

Produces:

Name | Group | Status

FF      A      ON
GG      A      OFF
HH      A      UNKN

but I can't boil it down to the single most relevant status for every group. I have tried to use CASE WHEN and IF EXISTS but I can't get it to work. Any input?

Edit 3:

As an example of the desired output for the overall group status:

Group | OverallStatus

A         UNKN
B         OFF
Improve this question
3
  • Which RDBMS are you using? Are you using both MySQL and Access? Commented Feb 14, 2017 at 14:37
  • 1
    could you add another table to make an order? Commented Feb 14, 2017 at 14:42
  • Sorry for the confusion. Its Access. Thanks for the edit,. @gbianchi I would rather not add another table, but if you an approach you think is better, I am open to suggestions. Commented Feb 14, 2017 at 16:08

4 Answers 4

Reset to default
3

If you can build another table, a simple solution would be:

Add another table with the values in the order you want.

enter image description here

Then, just build a query like this:

SELECT TOP 1 Table1.*, Table2.VALUE
FROM Table1 INNER JOIN Table2 ON Table1.status = Table2.STATUS
WHERE Table1.group="A"
ORDER BY  Tabla2.VALUE DESC

If the status changes or are added new ones, or you need a new order, just refresh the new table.

EDIT

Acording to the new info by OP, the query can be write in another way. The previous query take into account showing all the record in table1.

If you only need the group and the "max" status, you can use something like this:

SELECT A.group, Table2.STATUS
FROM (SELECT Table1.group, Max(Table2.VALUE) AS MaxVALUE
FROM Table1 INNER JOIN Table2 ON Tabla1.status = Table2.STATUS
GROUP BY Table1.group) as A INNER JOIN Table2 ON A.MaxVALUE= Table2.VALUE;
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

This approach is the one I would use, especially since "their names and orders could change in my application". Much easier to modify one table than to edit all of the queries that might make use of the "relevance" ranking.
I like that approach also. The only thing is that your query produces all of the records with that status, rather than a single record for the group and its overall status. I am trying to put a "Group by" statement, but I can't get it to work.
@gtdevel you never told us what name you would like to show in your resultset. This will show the first record, not all. But if several on group A has UNKN, what row do you want to show?
In the case of multiple UNKN (or any status), then there would be only one record output as UNKN for the group. The goal is to get the single most significant status for each group. So the output for group A should have a "Group" field and an overall "Group Status" field. I thought that was clear in the question but I guess not.
@gtdevel please add the desired output on the question so we can review the answers according to what you need
|
1

Use conditional aggregation and some other logic:

select grp,
       switch(sum(iif(status = "UNK", 1 0) > 0, "UNK",   -- any unknowns
              sum(iif(status = "OFF", 1, 0) > 0, "OFF",  -- any offs
              "ON"
             ) as group_status                  
from items
group by grp;

This counts the number of each status and then uses that to determine the overall group status. Your question is not really explicit about the rules, but I think these capture what you are trying to do. It should be easy enough to modify for other rules.

Improve this answer

2 Comments

Thanks for the answer. Do you know how we could combine the approach from @gbianchi's answer where the status values are stored in a separate table?
@gtdevel . . . I don't understand. Your question only mentions one table. If you have another question, you should ask it as another question, not a comment.
0

Using the "length" as gauge, this should fit Access SQL:

Select * 
From Items 
Where Items.Name = (
    Select Top 1 T.Name 
    From Items As T 
    Where T.Group = Items.Group
    Order By Len(T.Status) Desc)
Improve this answer

1 Comment

Thanks for the answer Gustav, the length can't be assumed to be used as a gauge. I will clarify my question. See my comment on @Amitabh answer.
0

Assuming that the status column will have only three distinct values as shown in data example, you can try below query:

SELECT * 
FROM ITEMS
WHERE (GROUP,LENGTH(STATUS)) IN (
    SELECT GROUP,MAX(LENGTH(STATUS))
    FROM ITEMS
    GROUP BY GROUP)

Thanks, Amitabh

Improve this answer

2 Comments

That's a clever approach, but the syntax is not understood by Access SQL.
The actual statuses don't need to be exactly as in the example. The example is representative of the case I am working on, but the length of the status won't necessarily correlate with its importance (unfortunately). The importance of the status should be defined in the query (i.e. which status is chosen first, second, third, etc).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.