0
SELECT CASE

    WHEN Workgroup = 'Group 1' THEN 'Computers'
    WHEN Workgroup = 'Group 2' THEN 'Laptops'
    WHEN Workgroup = 'Group 3' THEN 'Printers'
    WHEN Workgroup = 'Group 4' THEN 'Scanners'
    WHEN Workgroup = 'Group 5' THEN 'Servers'
    WHEN Workgroup = 'Group 6' THEN 'Routers'
    WHEN Workgroup = 'Group 7' THEN 'Switches'

ELSE 'UNKNOWN Workgroup'

END as Workgroup

,COUNT(*) FROM MASTERTABLE1

WHERE Status = 'OPEN'


AND Workgroup IN ('Group 1','Group 2')
OR Workgroup IN ('Group 3','Group 4')
OR Workgroup IN ('Group 5')
OR Workgroup IN ('Group 6','Group 7')

==================

Team 1 handles Group 1 and Group 2.
Team 2 handles Group 3 and Group 4.
Team 3 handles Group 5
Team 4 handles Group 6 and Group 7.

How can I run this query based on which Team is selected?

If I want to run a query for Team 4, is there a way put this in the query?

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Sung
  • 211
  • 3
  • 15
  • Please spell out your `WHERE` condition. Also post some sample data and the desired result. – PM 77-1 Apr 20 '15 at 22:40
  • [SQL Logic Operator Precedence: And and Or](http://stackoverflow.com/q/1241142/2407212) – Jonathan Apr 20 '15 at 22:42
  • Not related to your question, but you need a group by clause. – Dan Bracuk Apr 20 '15 at 22:52
  • Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Apr 21 '15 at 04:55

1 Answers1

1

Assuming a variable and/or a parameter to select the team...

declare @team tinyint
set @team = 4

SELECT CASE
       WHEN Workgroup = 'Group 1' THEN 'Computers'
       WHEN Workgroup = 'Group 2' THEN 'Laptops'
       WHEN Workgroup = 'Group 3' THEN 'Printers'
       WHEN Workgroup = 'Group 4' THEN 'Scanners'
       WHEN Workgroup = 'Group 5' THEN 'Servers'
       WHEN Workgroup = 'Group 6' THEN 'Routers'
       WHEN Workgroup = 'Group 7' THEN 'Switches'
       ELSE 'UNKNOWN Workgroup'
END as Workgroup,
COUNT(*)
FROM MASTERTABLE1
WHERE Status = 'OPEN'
AND ((@team = 1 AND Workgroup IN ('Group 1','Group 2')) OR 
     (@team = 2 AND Workgroup IN ('Group 3','Group 4')) OR
     (@team = 3 AND Workgroup IN ('Group 5')) OR
     (@team = 4 AND Workgroup IN ('Group 6','Group 7')))
GROUP BY Workgroup

As mentioned in the comments, you were missing a GROUP BY clause and some parentheses.

Josh Part
  • 2,154
  • 12
  • 15