Sub select & union all

Question

SELECT C.ClientCaseNumber,
       Sum(CASE
             WHEN CA.CaseActionDefinitionId IN (28, 29, 30) THEN 1
             ELSE 0
           END) AS [Wezwania],
       Sum(CASE
             WHEN CA.CaseActionDefinitionId IN (14, 21) THEN 1
             ELSE 0
           END) AS [Kontakt],
       Sum(CASE
             WHEN CA.CaseActionDefinitionId = 32 THEN 1
             ELSE 0
           END) AS [SMS],
       Sum(CASE
             WHEN CA.CaseActionDefinitionId = 44 THEN 1
             ELSE 0
           END) AS [Zgon],
       Sum(CASE
             WHEN CA.CaseActionDefinitionId = 49 THEN 1
             ELSE 0
           END) AS [Areszt],
       Sum(CASE
             WHEN CA.CaseActionDefinitionId = 37 THEN 1
             ELSE 0
           END) AS [Odmowa],
       Sum(CASE
             WHEN CA.CaseActionDefinitionId = 39 THEN 1
             ELSE 0
           END) AS [Podważa],
       Sum(CASE
             WHEN CA.CaseActionDefinitionId = 99 THEN 1
             ELSE 0
           END) AS [Ugoda],
       [Adres],
       [Numer],
       [Mail],
       [Powód]
FROM   (SELECT Notes AS [Adres]
        FROM   CaseActionHistory
        WHERE  CaseActionDefinitionId = 68
        UNION ALL
        SELECT Info AS [Numer]
        FROM   CaseActionHistory
        WHERE  CaseActionDefinitionId IN (54, 55, 56, 58,
                                          59, 60, 61, 62, 63)
        UNION ALL
        SELECT Notes AS [Mail]
        FROM   CaseActionHistory
        WHERE  CaseActionDefinitionId = 66
        UNION ALL
        SELECT Description AS [Powód]
        FROM   CaseActionDefinition
               JOIN CaseActionHistory AS C
                 ON DefinitionId = C.CaseActionDefinitionId
        WHERE  DefinitionId BETWEEN 70 AND 78) AS x
       INNER JOIN CaseDetails AS C
               ON x.CaseDetailId = C.CaseDetaislId
       INNER JOIN CaseActionHistory AS CA
               ON C.CaseDetailsId = CA.CaseDetailId
WHERE  C.ClientId = '11'
GROUP  BY C.ClientCaseNumber

I've got such query. As return shows error of invalid columns "CaseDetailId, CaseDetailsId, Mail, Numer, Powód".

---

http://oi39.tinypic.com/2vwy44n.jpg

That's more or less how the results should look like.

ClientCaseNumber is taken from table CaseDetails All the sums are sums of code added to CaseActionHistory table. Notes/Info are in CaseActionHistory table Description is placed in CaseActionDefinition table.

Between tables there are such connections:

CaseDetails.CaseDetailId = CaseActionHistory.CaseDetailsId

CaseActionHistory.CaseActionDefinitionId = CaseActionDefinition.DefinitionId

Answer 1

Are you not missing the alias 'x' from these fields:

SELECT
C.ClientCaseNumber
,sum(case when CA.CaseActionDefinitionId in (28,29,30) then 1 else 0 end) as [Wezwania]
,sum(case when CA.CaseActionDefinitionId in (14,21) then 1 else 0 end) as [Kontakt]
,sum(case when CA.CaseActionDefinitionId = 32 then 1 else 0 end) as [SMS]
,sum(case when CA.CaseActionDefinitionId = 44 then 1 else 0 end) as [Zgon]
,sum(case when CA.CaseActionDefinitionId = 49 then 1 else 0 end) as [Areszt]
,sum(case when CA.CaseActionDefinitionId = 37 then 1 else 0 end) as [Odmowa]
,sum(case when CA.CaseActionDefinitionId = 39 then 1 else 0 end) as [Podważa]
,sum(case when CA.CaseActionDefinitionId = 99 then 1 else 0 end) as [Ugoda]
,x.[Adres]
,x.[Numer]
,x.[Mail]
,x.[Powód]

FROM
(select Notes as [Adres] from CaseActionHistory where CaseActionDefinitionId = 68
UNION ALL
select Info as [Numer] from CaseActionHistory where CaseActionDefinitionId in (54,55,56,58,59,60,61,62,63)
UNION ALL
select Notes as [Mail] from CaseActionHistory where CaseActionDefinitionId = 66
UNION ALL
select Description as [Powód] from CaseActionDefinition join CaseActionHistory as C on DefinitionId = C.CaseActionDefinitionId where DefinitionId between 70 and 78) 
AS x

inner join CaseDetails as C on x.CaseDetailId = C.CaseDetaislId
inner join CaseActionHistory as CA on C.CaseDetailsId = CA.CaseDetailId

where C.ClientId = '11'

GROUP by C.ClientCaseNumber

Answer 2

The UNION clause does not work like that.

This query:

select Notes as [Adres] from CaseActionHistory where ...
UNION ALL
select Info as [Numer] from CaseActionHistory where ...
UNION ALL
select Notes as [Mail] from CaseActionHistory where ...
UNION ALL
select Description as [Powód] from CaseActionDefinition join CaseActionHistory ...

will not populate a table with 4 columns. Instead it will be a table with one column, with all the values one after the other. The name of the column will be taken from the first SELECT, i.e.

• if the first query returns values 1 and 2,
• the second query returns values 3 and 4
• the third query returns values 5 and 6
• the fourth query returns values 7 and 8

you wont get:

Adres  | Numer | Mail | Powód
------------------------------
    1  |     3 |    5 |     7
    2  |     4 |    6 |     8

but you'll get:

Adres  
-------
    1  
    2  
    3
    4
    5
    6
    7
    8