# need to calculate Group by date

• nrdroque

Old Hand

Points: 348

hi,

i'm creating a view and need some help.

i have the column "valorTotal" that i get the value PCPadrao*Quantidade but if Quantidade <0 then value equal to zero.

mi problem is that this is working line by line, but i need the acumulate from "artigo" by "Data"

on yellow what i need is ( -1672+2949+(-1672)+(-1332)= -1727)

is it possible

`SELECT [Artigo]      ,sum([Quantidade]) as [Quantidade]      ,sum(Case when [Quantidade] <0 then 0 else [Quantidade] end) as [qtdpositiva]	  ,sum([PCPadrao] *Case when [Quantidade] <0 then 0 else [Quantidade] end) as [vt]   	  ,avg([PCPadrao]) as PCPadrao   /*   ,[Data]*/      ,[Descricao]      ,[TipoArtigo]      ,[NometipoArtigo]  FROM [PRIKLC].[dbo].[Inc_vw_stkArmazem000]  where data >='2019-09-13 00:00:00' and data <='2019-09-13 23:59:59'  group by [artigo]      ,[Descricao]      ,[TipoArtigo]      ,[NometipoArtigo]`

• MVDBA (Mike Vessey)

SSC-Insane

Points: 21757

I think what you are describing is done by using "WITH ROLLUP"  in the group by or perhaps a grouping set

https://www.databasejournal.com/features/mssql/using-the-rollup-cube-and-grouping-sets-operators.html

for each of your dates it will give you the individual rows and a sub total for each date, you cacn also get a grand total in the bottom row

MVDBA

• terry999

SSCarpal Tunnel

Points: 4788

Pls ignore if I've misunderstood (the grid output doesn't seem to match the query)

`CAST(DATA as Date)`

into your group by clause solve it. Assuming DATA is datetime and you want to group by day?

• nrdroque

Old Hand

Points: 348

Yes i want to group by day and by artigo.