Sanjna Malpani Sanjna Malpani -4 years ago 35
SQL Question

CASE WHEN Multiple Conditions

I have multiple tables and I am performing

JOIN
on them. I want to group them on the conditions that a certain field begins with certain letters and then group them by region. This is how I went about

SELECT
CASE
WHEN PNumber LIKE 'F%' THEN 'F'
WHEN PNumber LIKE 'CE%' THEN 'CE'
WHEN PNumber LIKE 'CXT%' THEN 'CXT'
END AS p_by_type
, COUNT(*) AS p_by_count
FROM (
SELECT [xml_P].Id, [xml_P].PNumber, [xml_PSR].ScopeRegion, [xml_SR].RegionCode
FROM [xml_P]
JOIN [xml_PSR]
ON [xml_P].Id = [xml_PSR].Pol
JOIN [xml_SR]
ON [xml_PSR].SR = [xml_SR].Id) PWithRegions
GROUP BY
CASE
WHEN PNumber LIKE 'F%' THEN 'F'
WHEN PNumber LIKE 'CE%' THEN 'CE'
WHEN PNumber LIKE 'CXT%' THEN 'CXT'
END
GO


The above code gives me COUNT without taking region into consideration. How should I go about if I want the counts for each
PNumber
beginning with F,CE,CXT for each unique
[xml_SR].RegionCode
. That is for
[xml_SR].RegionCode = AMER
I want counts of F, CE, CXT and then for
[xml_SR].RegionCode = CH
I want the same and so on.

Executing the nested query:

SELECT [xml_P].Id, [xml_P].PNumber, [xml_PSR].ScopeRegion, [xml_SR].RegionCode
FROM [xml_P]
JOIN [xml_PSR]
ON [xml_P].Id = [xml_PSR].Pol
JOIN [xml_SR]
ON [xml_PSR].SR = [xml_SR].Id


Id, PNumber, ScopeRgion, RegionCode
1 , F586, 42, EMEA
..
22 , CE86, 42, EMEA
..
44 , CXT6, 42, EMEA
..
101 , F56, 42, EMEA
..
105 , F996, 43, CH
..
122 , CXT186, 43, CH
..
44 , CXT196, 43, CH
..
101 , CE5556, 43, CH
..

and executing the complete query gives me a result set something like this:

p_by_type, p_by_count
F , 39
CXT , 258
CE , 77

What I would like the output to be is

F, 25, CH
F, 3, EMEA
..

Answer Source

You just need to add RegionCode to the GROUP BY. However, your query is overly complicated -- no subquery is needed:

SELECT (CASE WHEN p.PNumber LIKE 'F%' THEN 'F'
             WHEN p.PNumber LIKE 'CE%' THEN 'CE'
             WHEN p.PNumber LIKE 'CXT%' THEN 'CXT'
        END) as p_by_type,
       sr.RegionCode,
       COUNT(*) AS p_by_count
FROM xml_P p JOIN
     xml_PSR psr
     ON p.Id = psr.Pol JOIN
     xml_SR sr
     ON psr.SR = sr.Id
GROUP BY (CASE WHEN p.PNumber LIKE 'F%' THEN 'F'
               WHEN p.PNumber LIKE 'CE%' THEN 'CE'
               WHEN p.PNumber LIKE 'CXT%' THEN 'CXT'
          END),
         sr.RegionCode;

Notes:

  • Tables aliases make the query easier to write and to read.
  • The subquery is not needed.
  • You should include whichever region code you want in both the SELECT and the GROUP BY.
  • The use of escape characters such as [ just clutters the query, if they are not needed.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download