CODEWITHSUNDEEP
sql-servergrouping
macbb
macbb

Reputation: 49

How to group by a certain column in SQL

I have a SQL table that looks like this

enter image description here

I'd like to group by System to show if all tasks are done or not (if all statuses for a system are done then Done else Not Done).

enter image description here

Upvotes: 1

Views: 64

Answers (3)

BJones
BJones

Reputation: 2460

You could use MAX() with GROUP BY. Assuming those are the only two available values for your status column.

SELECT Name, System, MAX(Status) Status
FROM Table
GROUP BY Name, System

Upvotes: 1

Carl
Carl

Reputation: 324

SELECT DISTINCT
    Name,
    System, 
    CASE 
        WHEN (SELECT NULL AS [Empty] FROM Table AS t_inner WHERE t_inner.Status = 'Not Done' AND t_inner.System = t_outter.System AND t_inner.Name = t_outter.Name) EXISTS 
        THEN 'Not Done'
        ELSE 'Done'
    END
FROM
    Table AS t_outter

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31795

Select Distinct Name/System, and then use a subquery for the Status.

The subquery could be a CASE WHEN EXISTS() structure.

Upvotes: 0

Related Questions