CODEWITHSUNDEEP
sqlsql-server
Ivan
Ivan

Reputation: 17

Group by with Case and Subquery

I'm having problems when performing a grouping with the following SQL:

SELECT 
    CASE 
         WHEN  t = '11' then 'V' 
         WHEN (t = '20' OR t = '25') THEN 'S' 
    END as [ty],

    CONVERT(CHAR(3), DateEx, 101) + CONVERT(CHAR(4), DateEx, 120) as [Fecha], COUNT(*) as [Total],  
    SUM (case when  CodLastEstado in (3,4,5,6,7,10,11,12,13) then 1 else 0 end ) as [C1],
    SUM (case when  CodLastEstado in (4,5,6) then 1 else 0 end ) as [C2],
    (
    select COUNT(*)
    from (SELECT t.Line, t.VTO, t.SubTypo
          FROM (SELECT c.Line, CONVERT(CHAR(3), c.DateEx, 101) + CONVERT(CHAR(4), c.DateEx, 120) as [VTO],
                       ROW_NUMBER() OVER (PARTITION BY a.numid ORDER BY a.FechaAccion desc) as Fila, SubTypo
                FROM Acciones a, contratos c
                WHERE A.numid = c.numid
                      AND (c.CodLastEstado in (10,11) and a.IDCodTipoEstado in (4,5,6)) 
                      and c.Brand = 'mouse'             
               ) t
          where t.Fila = 1
         )as q   
    where q.VTO = CONVERT(CHAR(3), contratos.DateEx, 101) + CONVERT(CHAR(4), contratos.DateEx, 120)
    and q.SubTypo = contratos.SubTypo
    ) as [CHco],    
    SUM (case when  CodLastSubEstado in (56,46,36,3,13,24) then 1 else 0 end ) as [N],
 FROM contratos
WHERE Brand = 'MINI' AND DateEx between '01/11/2014' and '30/11/2014'
GROUP BY SubTypo,
CONVERT(CHAR(3), contratos.DateEx, 101) + CONVERT(CHAR(4), contratos.DateEx, 120)

The syntax is correct, showing the following:

ty||Total||C1||C2||CHco||N

S || 120 ||5 ||50||  3 ||0
S ||   1 ||0 ||0 ||  1 ||0
V ||   3 ||0 ||0 ||  0 ||0

I tried many things to be displayed as follows, but I have not got, any ideas?

ty||Total||C1||C2||CHco||N

S || 121 ||5 ||50||  4 ||0
V ||   3 ||0 ||0 ||  0 ||0

Upvotes: 0

Views: 283

Answers (2)

Richard Hansell
Richard Hansell

Reputation: 5403

...or you can use a common-table expression:

WITH UngroupedData AS (
--Your query
)
SELECT
    [ty],
    SUM([Total]) AS [Total],
    SUM([C1]) AS [C1],
    SUM([C2]) AS [C2],
    SUM([CHco]) AS [CHco],
    SUM([N]) AS [N]
FROM
    UngroupedData
GROUP BY
    [ty];

Upvotes: 1

Jonny
Jonny

Reputation: 2519

Might not be the fastest solution, but whenever you have a result that you want to re-aggregate you can put it with a subselect.

SELECT ty, 
  SUM(C1) as [C1], 
  SUM(C2) as [C2], 
  SUM(CHco) as [CHCo], 
  SUM(N) as [N]
FROM (
  // your original query
) as subselect
GROUP BY ty

Upvotes: 0

Related Questions