Creating Subtotal With CTE and union

Question

Post reply

Creating Subtotal With CTE and union

luissantos

SSCertifiable

Points: 7391

February 5, 2020 at 12:51 pm

#3723256

Hello Communty,

I try to create for each employee, on line with Subtotals, but my query repeat teh subtotal line sum regarding the numbers os type of revenue or discount.

This is my query:

WITH CTE_REM
  AS
  ( SELECT prre.rem , prre.ere, pr.no, pr.nome, prre.cr FROM prre INNER JOIN pr ON prre.prstamp = pr.prstamp
    AND pr.data BETWEEN '20190101' AND '20190131') ,
 CTE_DSC
  AS
  (SELECT prde.des, prde.ede, pr.no, pr.nome , prde.cd FROM prde INNER JOIN pr ON prde.prstamp = pr.prstamp 
  AND pr.data BETWEEN '20190101' AND '20190131')


select nome, tipo, rem, desconto
from (
      SELECT r.nome [Nome],r.rem [Tipo], r.ere [Rem] ,0.00 [Desconto], r.cr [Cod] FROM CTE_REM r 
        UNION ALL
      SELECT  d.nome[Nome],d.des[Tipo], 0.00 [REM], d.ede [Desconto], d.cd FROM CTE_DSC d 
        UNION ALL 
      select rr.nome ,'Subtotal', SUM(rr.ere) [Rem], SUM(dd.ede) [Desconto] , 999 [Cod]
      from CTE_REM rr JOIN CTE_DSC dd ON rr.nome = dd.nome GROUP BY rr.nome
) as a

order by nome, cod asc

But the result is:

nometiporemdesconto
António Maria Gomes Gonçalves                          Ordenado Base2000,0000000,000000
António Maria Gomes Gonçalves                          Subsídio de Refeição12,8100000,000000
António Maria Gomes Gonçalves                          Imposto S/Rendimento0,000000470,000000
António Maria Gomes Gonçalves                          Imposto S/Rendimento0,00000045,000000
António Maria Gomes Gonçalves                          Segurança Social0,000000220,000000
António Maria Gomes Gonçalves                           »»»Subtotal     6038,430000       1470,000000
Beatriz Cristina Silva                                 Ordenado Base      1100,0000000,000000
Beatriz Cristina Silva                                 Subsídio de Férias47,5600000,000000
Beatriz Cristina Silva                                 Subsídio de Natal47,5600000,000000
Beatriz Cristina Silva                                 Subsídio de Refeição51,2400000,000000
Beatriz Cristina Silva                                 Imposto S/Rendimento0,000000159,000000
Beatriz Cristina Silva                                 Imposto S/Rendimento0,0000007,000000
Beatriz Cristina Silva                                 Imposto S/Rendimento0,0000006,000000
Beatriz Cristina Silva                                 Imposto S/Rendimento0,00000028,000000
Beatriz Cristina Silva                                 Imposto S/Rendimento0,0000001,000000
Beatriz Cristina Silva                                 Imposto S/Rendimento0,0000001,000000
Beatriz Cristina Silva                                 Segurança Social0,000000131,460000
Beatriz Cristina Silva                                  »»»Subtotal     8724,5200         1333,840000

Someone could give me what is wrong.

Thanks and best regards,

Luis

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 1

In your definition of 'a' you've included a line with the summed subtotal, did you intend to do that?

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

drew.allen SSC Guru Points: 76985 More actions · Answer 2

You're doing a partial CROSS JOIN. You either need to SUM your totals before joining or do a UNION before SUMming. You could also use GROUPING SETS. Since you failed to provide sample data, I can't provide you with test code.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

If you are trying to get subtotal lines in your results could GROUPING SETS help?

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017#group-by-grouping-sets--