Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

group by column not having specific value

I am trying to obtain a list of Case_Id's where the case does not contain a specific RoleId using Microsoft Sql Server 2012.

For example, I would like to obtain a collection of Case_Id's that do not contain a RoleId of 4.

So from the data set below the query would exclude Case_Id's 49, 50, and 53.

    Id      RoleId  Person_Id   Case_Id
   --------------------------------------
    108     4       108         49
    109     1       109         49
    110     4       110         50
    111     1       111         50
    112     1       112         51
    113     2       113         52
    114     1       114         52
    115     7       115         53
    116     4       116         53
    117     3       117         53

So far I have tried the following

SELECT Case_Id
FROM [dbo].[caseRole] cr
WHERE cr.RoleId!=4
GROUP BY Case_Id ORDER BY Case_Id
like image 285
Phil Avatar asked Sep 26 '14 13:09

Phil


People also ask

Can we SELECT column which is not part of GROUP BY?

The direct answer is that you can't. You must select either an aggregate or something that you are grouping by.

Can you GROUP BY something not in SELECT?

Answer. No, you can GROUP BY a column that was not included in the SELECT statement. For example, this query does not list the price column in the SELECT , but it does group the data by that column.

How do you GROUP BY based on conditions?

Syntax: SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 HAVING condition ORDER BY column1, column2; function_name: Name of the function used for example, SUM() , AVG(). table_name: Name of the table. condition: Condition used.

Does GROUP BY ignore NULL values?

Group functions ignore the NULL values in the column. To enforce the group functions ti include the NULL value, use NVL function.


2 Answers

The not exists operator seems to fit your need exactly:

SELECT DISTINCT Case_Id
FROM   [dbo].[caseRole] cr
WHERE  NOT EXISTS (SELECT *
                   FROM   [dbo].[caseRole] cr_inner
                   WHERE  cr_inner.Case_Id = cr.case_id 
                          AND cr_inner.RoleId = 4);
like image 73
Mureinik Avatar answered Oct 13 '22 11:10

Mureinik


Just add a having clause instead of where:

SELECT Case_Id
FROM [dbo].[caseRole] cr
GROUP BY Case_Id
HAVING SUM(case when cr.RoleId = 4 then 1 else 0 end) = 0
ORDER BY Case_Id;
like image 41
Gordon Linoff Avatar answered Oct 13 '22 10:10

Gordon Linoff