locked
Group by completion RRS feed

  • 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
    Tuesday, December 8, 2020 2:03 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 9, 2020 10:01 AM

All replies

  • User452040443 posted

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

    Tuesday, December 8, 2020 5:53 PM
  • 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

    Wednesday, December 9, 2020 2:29 AM
  • 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
    Wednesday, December 9, 2020 7:56 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 9, 2020 10:01 AM
  • 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

    Wednesday, December 9, 2020 2:45 PM