SQL Server Group by query checking status over multiple rows

yftpprvb 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(92)

My data looks as below

My data table looks like this:

client_id   Status
   1         a
   1         b
   1         d
   1         e
   2         a
   2         d
   3         a
   3         b
   3         e
   4         a
   4         d
   5         b

I need to group by client where status has values both a and d

The result set from above should return

client_id     a_d_Flag
  1             Yes
  2             Yes
  3             No
  4             Yes
  5             No

Query :

SELECT c.client
    , CASE WHEN c.Status = 'a' AND c.status = 'd'
    THEN 'Yes'
    ELSE 'No'
    END AS a_d_Flag
FROM client_details c
GROUP BY c.client
HAVING c.Status = 'a' AND c.status = 'd'

sql-server

来源：https://stackoverflow.com/questions/76470516/group-by-query-checking-status-over-multiple-rows

2条答案

按热度按时间

vd8tlhqk1#

use a case expression to check for Status and count for a or d

select client_id, 
       case when count(case when Status in ('a', 'd') then Status end) = 2
            then 'Yes'
            else 'No'
            end
from   client_details
group by client_id

That is assuming that you does not have duplicate Status for a client_id .

If you have such as like

3         a
3         a
3         b

change the count to count(distinct case ...)

赞(0）回复(0）举报 2023-06-21

k5ifujac2#

You can use conditional aggregation

SELECT
  cd.client_id, 
  a_d_Flag =
    CASE WHEN COUNT(CASE WHEN cd.Status = 'a' THEN 1 END) > 0
          AND COUNT(CASE WHEN cd.Status = 'd' THEN 1 END) > 0
        THEN 'Yes'
        ELSE 'No'
    END
FROM client_details cd
GROUP BY
  cd.client_id;

Note that this method handles duplicate rows of a or d correctly.

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Group by query checking status over multiple rows

2条答案

相关问题

热门标签

最新问答