CODEWITHSUNDEEP
sqlms-access
gtdevel
gtdevel

Reputation: 1513

Conditional Output in SQL

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

Upvotes: 1

Views: 987

Answers (4)

gbianchi
gbianchi

Reputation: 2138

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;

Upvotes: 3

Amitabh
Amitabh

Reputation: 21

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

Upvotes: 0

Gustav
Gustav

Reputation: 55816

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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.

Upvotes: 1

Related Questions