CODEWITHSUNDEEP
sqlsql-server-2008group-by
sky91
sky91

Reputation: 3180

How to categories different strings as a group using SQL?

table: Invoice

InvoiceID   Status
=========   =======
1           Incompleted
1           Incompleted
2           Completed
2           Incompleted
3           Completed
3           Completed
4           Incompleted
4           Incompleted
4           Completed

my expected result:

InvoiceID   Status
=========   ======
1           Incompleted
2           Incompleted
3           Completed
4           Incompleted

The logic of grouping is,
If Incompleted exists in the column "Status" while grouping the ID and Status,
then return “Incompleted";
If only the Completed exists in the column "Status" while grouping,
then return "Completed";

SQL:

SELECT InvoiceID, Status
FROM Invoice
GROUP BY InvoiceID, Status

(This sql output the result which is not I expected. And I do not how to implement the logic to the Status column)
Anyone has the idea? Thanks in advance.

Upvotes: 2

Views: 73

Answers (2)

Anand
Anand

Reputation: 41

  1. In the inner query get only the status which is 'Incompleted'. Link the top query for
    matching ids which will fetch all the 'Incompleted'.
  2. In the inner query get only the status which is 'Incompleted'. Link the top query for
    non-matching ids which will fetch all the 'Completed'.
  3. Get the union
SELECT distinct st1.InvoiceID, 'Incompleted' as Status FROM StatusTest st1  
where InvoiceID in  
(select distinct InvoiceID from StatusTest st2 where status = 'Incompleted')  
UNION  
SELECT distinct st1.InvoiceID, 'Completed'  as Status FROM StatusTest st1  
where InvoiceID not in  
(select distinct InvoiceID from StatusTest st2 where status = 'Incompleted')  
order by InvoiceID

Upvotes: 0

Vulcronos
Vulcronos

Reputation: 3456

Try this:

SELECT InvoiceID, MAX(Status)
FROM Invoice
GROUP BY InvoiceID

Sql Server should consider Incompleted to be larger than Completed. If there are any Incompleted in teh InvoiceID, that will be returned, else Completed. If you have a weird coalation and MAX returns Completed then just use min.

Upvotes: 4

Related Questions