Group by completion

Question

User448619955 posted

I have a Table as below:

UserName	CompanyName	Group
Test1	cp1	IT
Test1	cp1	HR
Test2	cp2	HR

how can I write a query to get the result :

UserName	CompanyName	HasItAceess
Test1	cp1	1
Test2	cp2	0

Answer 1

User452040443 posted

Why is the result 1 for Test1/cp1 and 0 for Test2/cp2? What is the criteria?

Answer 2

User1535942433 posted

Hi vahid.ch,

Accroding to your description,I don't understand your requirment clearly.

What is HasItAceess? Which column is group by? I'm guessing that you need to distinct the username.

Just like this:

select DISTINCT UserName,CompanyName from GT 

If this isn't match your requirment,you could post more details to us.

Best regards,

Yijing Sun

Answer 3

User448619955 posted

thanks for the feedback.

actually I want to bring those users who have access to IT role, it doesn't matter if they have access to another role.

if they have access to IT role, then I want to set a flag in my query with true and if they don't have access to IT role, so accordingly the result will be false.

something like this:

select ....

 CASE WHEN Group='IT' 
                  THEN 1 ELSE 0 END AS HasITAccess from ...

in this case the result will be:

UserName	CompanyName	HasItAceess
Test1	cp1	1
Test1	cp1	0
Test2	cp2	0

so, I have to remove the second row because Test 1 has access to It role and my requirement will be :

UserName	CompanyName	HasItAceess
Test1	cp1	1
Test2	cp2	0

Answer 4

User448619955 posted

I found the solution:

SELECT  Username,CompanyName,MAX(HasITAccess) FROM
(
      SELECT DISTINCT Username,CompanyName,
				 CASE WHEN GroupName='IT' 
				 THEN 1 ELSE 0 END AS HasITAccess
      FROM [Table]) AS result
GROUP BY Username,CompanyName

Answer 5

User452040443 posted

You can leave your query as follows:

SELECT
  Username,
  CompanyName,
  MAX(CASE WHEN GroupName = 'IT' THEN 1 ELSE 0 END) AS HasITAccess
FROM [Table]
GROUP BY 
  Username,
  CompanyName

Hope this help